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.
This criteria should satisfy your requirments:
where dt >= dateadd(month, datediff(month, 0, dateadd(day, datediff(day, 1, getdate()), 0)), 0)
and dt < dateadd(day, datediff(day, 0, getdate()), 0)
EDIT
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.
