# question

## 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.

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?

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

·

Ran on 12/17:

```
SELECTdateadd(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:

```
SELECTdateadd(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.

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