question

mlawton avatar image
mlawton asked

Month to Date Query update

I asked the following question on 12/9/2009: I want to create a daily file that will have the previous day's data all the way back to the beginning of the month. For example: today's file will have data for 12/1/2009 through and including 12/8/2009. The columns are: OrderNo., OrderDate, Product, Status. However, on the 1st of the month, I want the file to include all data for the previous month. For example, the file on the 1st of January will include data for 12/1/2009 through 12/31/2009.

See: http://ask.sqlteam.com/questions/1252/month-to-date-query

Solution:

                    
where  dt >= dateadd(month, datediff(month, 0, dateadd(day, datediff(day, 1, getdate()), 0)), 0)                    
and    dt < dateadd(day, datediff(day, 0, getdate()), 0)

where dt is your date column. The first line will be the first of whatever month yesterday was in. The second line is today at 00:00:00.000. Since the operator is "<" (less than) that means all datetimes prior to today - or through yesterday.

Question: The first line works perfectly. Is there anyway to test the second line of code so that it will bring the entire previous month of data (December) before the 1st of January?

sql-server-2005datetime
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Jim Orten avatar image
Jim Orten answered

Ran on 12/17:

            
SELECT
dateadd(month, datediff(month, 0, dateadd(day, datediff(day, 1, getdate()), 0)), 0) as StartDate,
dateadd(day, datediff(day, 0, getdate()), 0) as EndDate

Result was:
StartDate = 2009-12-01 00:00:00.000
EndDate = 2009-12-17 00:00:00.000

Also ran:

            
SELECT
dateadd(month, datediff(month, 0, dateadd(day, datediff(day, 1, '1/1/2009'), 0)), 0) as StartDate,
dateadd(day, datediff(day, 0, '1/1/2009'), 0) as EndDate

Result was:
StartDate = 2008-12-01 00:00:00.000
EndDate = 2009-01-01 00:00:00.000

It looks to me like your where clause will work just fine for what you want to do.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.