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 |
|
qman
Constraint Violating Yak Guru
442 Posts |
 Posted - 2010-03-23 : 10:21:00
|
| I am trying to compare a date vs another date, simple enough...The only difficulty is that I need to use a parameter/variable for the YEAR.Hope this makes some sense, see below:declare @year as datetime@year = 2010select * from historywhere begindate >= '10' + '/' + '01' + @year - 1ANDbegindate <= '9' + '/' + '30' + @year |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-23 : 10:27:48
|
| [code]declare @year as intset @year = 2010select * from historywhere begindate >= DATEADD(mm,9,DATEADD(yy,@year - 1901,0))ANDbegindate <= DATEADD(mm,9,DATEADD(yy,@year - 1900,0))-1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
qman
Constraint Violating Yak Guru
442 Posts |
Posted - 2010-03-23 : 13:22:10
|
| Visakh16, once again thanks...!This works perfect, but....What exactly is DATEADD(mm,9,DATEADD(yy,@year - 1901,0)) really doing? Just trying to be sure I understand. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-23 : 13:32:47
|
quote:
Originally posted by qman
Visakh16, once again thanks...!This works perfect, but....What exactly is DATEADD(mm,9,DATEADD(yy,@year - 1901,0)) really doing? Just trying to be sure I understand.
1900 is base year. what @year - 1900 does is to convert value inside @year to integer equivalent. you need to start from previous year so @year - 1900-1 or @year - 1901. it takes you to first date of prev year (1 Jan of @year-1). then adding 9 months to it takes you to beginning of Oct ( 1 Oct of @year-1) which is your start condition and for end condition i follow same logic but subtracts only 1900 so you get 01 Oct of @year itself -1 gives last day of @year sept (09/30)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
qman
Constraint Violating Yak Guru
442 Posts |
Posted - 2010-03-23 : 13:42:37
|
| Thanks for the explanation, excellent! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-23 : 13:43:49
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Date Compare using Year parameter/variable