Month to Date Query update

Question

question

mlawton asked Dec 17, '09

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-2005 datetime

10 |1200

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

Answer 1 · 2009-12-17T15:29:35Z

Jim Orten answered Dec 17, '09

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

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

question