Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
spohlso
Starting Member
3 Posts |
 Posted - 2006-12-22 : 14:19:41
|
| Hi all. Fairly new to all this, but I just came across a problem today with SQL 2000. I need to get a report on all data in a table for the previous 12 months. So when ever the report gets run, say it gets run on Decemebr 4th, I would get all data from the beginning of last december up til the end of this november.I tried saying WHERE dbo.Table.Date BETWEEN ((MONTH(GETDATE())-13) AND (MONTH(GETDATE())-1)) but that gave me everything between between December and November regardless of year.Anyone know a good way to word this? I can't just ask for everything older than GETDATE()-365 because it needs to be Start of Month to End of Month regardles of when it's run.Any help would be greatly appreciated. |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-22 : 14:34:18
|
| WHERE dbo.Table.Date >= cast(cast(month(dateadd(mm, -13, getdate())) as varchar(2)) + '-1-' + cast(year(dateadd(mm, -13, getdate())) as varchar(4)) as datetime)AND dbo.Table.Date < cast(cast(month(getdate()) as varchar(2)) + '-1-' + cast(year(getdate()) as varchar(4)) as datetime)This is simpler than BETWEEN because you don't have to make the end of the range 1 second before midnight on the last day, you can use greater than or equal to the beginning of the range and less than midnight the end of the range. |
 |
|
|
spohlso
Starting Member
3 Posts |
Posted - 2006-12-22 : 14:42:54
|
| Wow, that's awesome! Thanks! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-12-22 : 19:11:23
|
This will give the same result:where dbo.Table.Date >= dateadd(mm,datediff(mm,0,getdate())-13,0) and dbo.Table.Date < dateadd(mm,datediff(mm,0,getdate()),0) CODO ERGO SUM |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-12-22 : 22:55:38
|
quote:
Originally posted by snSQL
WHERE dbo.Table.Date >= cast(cast(month(dateadd(mm, -13, getdate())) as varchar(2)) + '-1-' + cast(year(dateadd(mm, -13, getdate())) as varchar(4)) as datetime)AND dbo.Table.Date < cast(cast(month(getdate()) as varchar(2)) + '-1-' + cast(year(getdate()) as varchar(4)) as datetime)This is simpler than BETWEEN because you don't have to make the end of the range 1 second before midnight on the last day, you can use greater than or equal to the beginning of the range and less than midnight the end of the range.
When you compare Dates CAST or CONVERT is not needed provided that datecolumn is of DATETIME datatypeMadhivananFailing to plan is Planning to fail |
|
|
|
spohlso
Starting Member
3 Posts |
Posted - 2006-12-27 : 09:28:56
|
| Thanks again. I like the simpler version. I feel much more comfortable using code I can actually interpret myself. |
|
|
|
|
|
|
|
|
Help with a date in WHERE clause