November 30, 2010 at 5:32 am
Dear all,
I'm taking monthly reports with following line from excell :
......WHERE (sales_date>={ts '2010-11-01) 00:00:00'} and sales_date<{ts '2010-12-01 00:00:00'}).....
for every upcoming new month and new year I have to change related month & year figures manually (i.e. I have to change "11" to "12" and "12" to "01" AND second "2010" to "2011" . Is there any way to automatise this where statement in the query?
Thanks in advance....
November 30, 2010 at 5:42 am
sure, if you can assume that *today* decides that you want the first of the current month; so if you run the report, witht hat condition, the date range changes to selec the first of the current month, and also first day of the next month.
problems occur when someone wants last month's data.... would it work ok when you are calculating the date range based on todays date
/*--Results
(No column name)(No column name)
2010-11-01 00:00:00.0002010-12-01 00:00:00.000
*/
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0), DATEADD(mm, DATEDIFF(mm,0,getdate()) +1, 0)
/*--Results
(No column name)(No column name)
2010-12-01 00:00:00.0002011-01-01 00:00:00.000
*/
declare @date datetime
SET @date = '2010-12-11 12:43:00.000'
select DATEADD(mm, DATEDIFF(mm,0,@date), 0),
DATEADD(mm, DATEDIFF(mm,0,@date)+1, 0)
--and your WHERE statement
WHERE (sales_date >= DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
and sales_date < DATEADD(mm, DATEDIFF(mm,0,getdate()) +1, 0)
Lowell
November 30, 2010 at 5:56 am
It worked...Thanks once more Lovell for your help about my GETDATE() problems 😀
....Related report is only for Year To Date and Monthly figures....Ppl who wants last months reports is using another excell file...
November 30, 2010 at 6:07 am
glad i could help! i had to do a search to find the reference you were talking about, where we participated in a different getdate() post a couple of months ago or so.
glad things are working for you!
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply