in

ASP.NET Weblogs

This Blog

Syndication

Sponsors

Archives

Tien Chun Wang

Last date of the month. sql date process

I was requested to make a store procedure that is able to retrieve results from a log table based on the today's month, which has to start from the 1st day of the month to the last day of the month. After a no-brainer's action: Google it! some results were found and quite useful. but most of the version found on the internet does not cover the "time". (it has to return the first day and time , as well as last day and time such as 01/01/2008 12:00AM - 01/31/2008 11:59:PM)

So I took my time to write a simple query to get the last date and first date of the month. Post comments below if you have any better ideas or comments

    DECLARE @FirstDate datetime
    DECLARE @LastDate datetime
    DECLARE @Today datetime

    SELECT @Today = getDate()
    SET @FirstDate = CAST(CONVERT(varchar(2),MONTH(@Today)) + '/01/' + CONVERT(varchar(4),YEAR(@Today)) as DATETIME)
    SET @LastDate = DateAdd(second, -1, DateAdd(M,1,@FirstDate))
 

    -- @FirstDate returns 'Mar  1 2008 12:00AM'

    -- @LastDate returns 'Mar 31 2008 11:59PM'

 

Published Mar 03 2008, 02:46 PM by protienshow
Filed under: ,

Comments

 

Joe said:

You should really call SET DATEFORMAT first to ensure your dates are interpreted as mdy.

Here's a way that will work if your SQL Server DATEFORMAT isn't US mdy (note yyyymmdd is interpreted correctly independent of the DATEFORMAT setting :

SELECT @Today = dateadd(dd, datediff(dd,'19000101',GETDATE()), '19000101')

SELECT @FirstOfThisMonth = dateadd(dd, 1-DAY(@Today), @Today)

SELECT @FirstOfNextMonth = dateadd(mm, 1, @FirstOfThisMonth)

SELECT @LastOfThisMonth = dateadd(dd, -1, @FirstOfNextMonth)

March 3, 2008 3:23 PM
 

Joe said:

... and regarding your query you should query for

>= @FirstDayOfThisMonth AND < @FirstDayOfNextMonth.

(i.e. strictly less than midnight on the first of next month rather than <= 11:59PM on the last day of this month).

Surely you want rows from the last day of the month with a time of of 11:59:59 PM to be included in the current month.

March 3, 2008 3:28 PM
 

Abhishek, Bangalore said:

THANKS A LOT BUDDY!!

February 4, 2009 10:52 PM