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?