Geek asks and answers

Given the date, I get the first and last date of this month using classic asp

I have a date field in a database that stores values ​​in the format mm / dd / yyy. Using this, how can I get the first and last day of a given date. I use this following code to display records from a database

<%
sql="select * from order_details where emp_name='"&session("emp")&"' order by 1,2,3,4,5,6,7"
        rs.open sql,con,1,2
        do while not rs.eof
        session("wk")=weekdayname(weekday((rs("date"))))
    %>
<tr>

    <td align="center"><%=rs("emp_name")%></td>
    <td align="center"><%=rs("food_had")%></td>
    <td align="center"><%=rs("quantity")%></td>
    <td align="center"><%=session("wk")%></td>
    <td align="center"><%=day(rs("date"))&"/"&month(rs("date"))&"/"&year(rs("date"))%></td>
    <td align="center"><%=rs("grand_total")%></td>

</tr>
<%
        rs.movenext
        loop
        rs.close
%>

session ("wk") gives me the name of the day of the week.

Now that I have the code, I edited the request so that I can use firstdayhemhemthth and lastdatofthemonth to display the entries, but this is not what I used

<%
sql="select * from order_details where emp_name='"&session("emp")&"' and date BETWEEN '"&session("firstDayOfMonth")&"' AND '"&session("firstDayOfMonth")&"'"
        rs.open sql,con,1,2
        do while not rs.eof
        session("m")=monthname(month((rs("date"))))
        session("firstDayOfMonth") = rs("date") + 1 - day(rs("date"))
        session("lastDayOfMonth") = dateadd("m", 1, session("firstDayOfMonth") ) - 1
    %>   
<tr>   

    <td align="center"><%=rs("emp_name")%></td>   
    <td align="center"><%=rs("food_had")%></td>   
    <td align="center"><%=rs("quantity")%></td>   
    <td align="center"><%=session("wk")%></td>   
    <td align="center"><%=day(rs("date"))&"/"&month(rs("date"))&"/"&year(rs("date"))%></td>   
    <td align="center"><%=rs("grand_total")%></td> 
    <td align="center"><%=session("firstDayOfMonth")%></td>   
    <td align="center"><%=session("lastDayOfMonth")%></td> 


</tr>
<%
        rs.movenext
        loop
        rs.close
    %>

" BETWEEN '' & session (" firstDayOfMonth ") &" 'AND' "& session (" firstDayOfMonth ") &" '" ... , firstdate lastdate... - ..

+3
1

firstDayOfMonth, rs ( "date" ).

, , 1 dateadd() :

        firstDayOfMonth = rs("date") + 1 - day(rs("date"))
        lastDayOfMonth = dateadd("m", 1, firstDayOfMonth ) - 1

:

<%   
sql="select * from order_details where emp_name='"&session("emp")&"' order by 1,2,3,4,5,6,7"   
        rs.open sql,con,1,2   
        do while not rs.eof   
        session("wk")=weekdayname(weekday((rs("date"))))   
        firstDayOfMonth = rs("date") + 1 - day(rs("date"))
        lastDayOfMonth = dateadd("m", 1, firstDayOfMonth ) - 1
    %>   
<tr>   

    <td align="center"><%=rs("emp_name")%></td>   
    <td align="center"><%=rs("food_had")%></td>   
    <td align="center"><%=rs("quantity")%></td>   
    <td align="center"><%=session("wk")%></td>   
    <td align="center"><%=day(rs("date"))&"/"&month(rs("date"))&"/"&year(rs("date"))%></td>   
    <td align="center"><%=rs("grand_total")%></td> 
    <td align="center"><%=fdate(firstDayOfMonth) ></td>   
    <td align="center"><%=fdate(lastDayOfMonth) ></td> 

</tr>   
<%   
        rs.movenext   
        loop   
        rs.close   
%>

Edit: , sql, WHERE date >= firstDayOfMonth AND date <= lastDayOfMonth :

 sql="select * from order_details where emp_name='" & session("emp") & "' " & _ 
     "WHERE date >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())) + 1 - DAY(DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))) " &_
     "AND date <= DATEADD(MONTH, 1, DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())) + 1 - DAY(DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())))) - 1 " &_
     "order by 1,2,3,4,5,6,7"

( , - , )

+3

More articles:


All Articles