x

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?

more ▼

asked Dec 17 '09 at 02:19 PM in Default

mlawton gravatar image

mlawton
42 5 6 6

(comments are locked)
10|1200 characters needed characters left

1 answer: sort oldest

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.

more ▼

answered Dec 17 '09 at 03:29 PM

Jim Orten gravatar image

Jim Orten
646 8 8 10

Thanks so much for your help!!!
Dec 17 '09 at 04:23 PM mlawton
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1936
x91

asked: Dec 17 '09 at 02:19 PM

Seen: 2118 times

Last Updated: Dec 17 '09 at 03:43 PM