Given a datehow do I get the first and the last date of that month using classic asp

Question

I have a date field in the database that store values in format mm/dd/yyy. Using this how can I get the first and the last day of a given date. I'm using this following code to display records from 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 weekday name.

Now That I have the code I edited the query so that i can use firstdayofthemonth and lastdatofthemonth to display records but its not working this is 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
    %>

if I remove "and date BETWEEN '"&session("firstDayOfMonth")&"' AND '"&session("firstDayOfMonth")&"'" from the query shows the first and the last date...but I want to display records using firstdate and lastdate ...am I misssing something in my query on top..

nvuono · Answer 1 · 2012-05-18T08:12:12.950

You can calculate the firstDayOfMonth pretty easily by subtracting the day number from the rs("date") variable.

Then to get the last day of the month you add 1 month with the dateadd() function and follow up by subtracting a day:

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

I've set it up in your code as an example below:

<%   
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: If you want to filter for the current month's data you can change up your sql statement to add a WHERE date >= firstDayOfMonth AND date <= lastDayOfMonth clause like the following:

 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"

(I'm sure somebody could rewrite that query to not be an absolute mess)

how can I display records using firstDayOfMonth and lastDayOfMonth — Leroy Mikenzi, May 18 '12 at 07:54
If you want to use a messy sql query you can check my updated answer — nvuono, May 18 '12 at 08:13
can't i use this as a part of my query to display records Pls check my Question I have edited .. — Leroy Mikenzi, May 18 '12 at 09:19

Given a datehow do I get the first and the last date of that month using classic asp

1 Answers1