select from now and back 6 months
-
sound horrible but thats what I need
I need to select data where I have txdate as a date field to do a 6 month sales report.
Cannot see how to select just the 6 months of data.
So eg I want from now + MAR2005 + FEB 2005 + .....TO OCT 2004.
THE REST OF MYSCRIPTS DEAL OK WITH THE MONTHS PROVIDING I DO NOT LAND UP WITH JAN2004 AND JAN 2005 (AS EG) AS IT IS THEN ADDED TOGETHER.
AT THE MOMENT MY SCRIPT JUST EXCTRACTS THE YEAR FROM txdate for 2005 and works well for 2nd half of year June to Jan
Wrote this in Sept last year which is why it worked then.
Thanks
-
SELECT
...
WHERE
DATECOLUMN >= DATEADD(m,-6,DATEDIFF(d,0,GETDATE()))
goes always 6 month from the current date back in time.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
thanks I will try it and report back.
-
I believe it would technically need to be a between statement to ensure that there are no future dates included in the query.
ie. BETWEEN DATEADD(m,-6,DATEDIFF(d,0,GETDATE())) AND GETDATE()
if you want todays date too you'd need to add a day.
ie. BETWEEN DATEADD(m,-6,DATEDIFF(d,0,getdate())) AND DATEADD(d,1,DATEDIFF(d,0,getdate()))
-
Good point. I was implying that there are no future dates.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks guys but did not work.
The package I am using (accounting system) uses DBISAM from elevate software and as a newbie discovering the difference in SQL syntax between different versions. On their forum I got the answer
where txdate>=current_date-182
So could the following not work in MS SQL
where txdate >= getdate() -182 or do you have to use the dateadd
not too concerned with it pulling back a day extra as the rest of the script will ignore it any how so value of days can be anything between 182 and 364
Thanks
Larry
-
Sury, you can use GETDATE()-182 like this
SELECT getdate() - 182
------------------------------------------------------
2004-10-13 11:30:05.233
(1 row(s) affected)
Since SQL Server's base date unit is a day, this will work. However, note that 6 months not necessarily always will have 182 days!
And yes, you can use DATEADD. Like this
SELECT DATEADD(d,-182,DATEDIFF(d,0,GETDATE()))
------------------------------------------------------
2004-10-13 00:00:00.000
(1 row(s) affected)
I've intentionally incorporated the DATEDIFF function to get rid of the time portion, if there is one unequal midnight. Note the difference between both statements. But as I 've said, you're on a far saver side, when you use DATEADD along with the month parameter in SQL Server. Consider this:
DECLARE @prove DATETIME
SET @prove = '20041231'
SELECT @prove - 182 --wrong
, DATEADD(d,-182,DATEDIFF(d,0,@prove)) --wrong
, DATEADD(m,-6,DATEDIFF(d,0,@prove)) --correct, I think
-------------------------- ------------------------- ------------------------
2004-07-02 00:00:00.000 2004-07-02 00:00:00.000 2004-06-30 00:00:00.000
(1 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply