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 |
|
shakilhyd
Starting Member
16 Posts |
 Posted - 2008-08-03 : 01:44:15
|
| Hi,I am getting current year in the WHERE clause condition in the following script but I want to get year 2006 not 2007. How can I update the script so that I can get year 2006 instead of 2007. I need require editing in the following line of script(CONVERT(NUMERIC, DATEDIFF(YEAR, m.dob, GETDATE()), 101) / 5 = DATEDIFF(YEAR, m.dob, GETDATE()) / 5)Can you please help in this regard. Select * from member mWHERE (m.clientid = 16) AND (w.benelig = 1) AND (CONVERT(NUMERIC, DATEDIFF(YEAR, m.dob, GETDATE()), 101) / 5 = DATEDIFF(YEAR, m.dob, GETDATE()) / 5)Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-03 : 13:39:21
|
| Didnt understand where you're getting year part. what you're doing here is returning number of years passed from dob to today. if your question was to compare to 2006 instead of today use this.(CONVERT(NUMERIC, DATEDIFF(YEAR, m.dob, DATEADD(yy,DATEDIFF(yy,0,GETDATE())-3,0)), 101) / 5 = DATEDIFF(YEAR, m.dob, DATEADD(yy,DATEDIFF(yy,0,GETDATE())-3,0)) / 5) |
 |
|
|
shakilhyd
Starting Member
16 Posts |
Posted - 2008-08-03 : 16:00:42
|
Hi,Its wrong. I wanted to get year-2006 not 2008. In the following query I am getting year 2008 because I am using getdate() but I wanted to get year-2006 instead of 2008. How can I get. Please advise. Thanks.quote:
Originally posted by shakilhyd
Hi,I am getting current year in the WHERE clause condition in the following script but I want to get year 2006 not 2007. How can I update the script so that I can get year 2006 instead of 2007. I need require editing in the following line of script(CONVERT(NUMERIC, DATEDIFF(YEAR, m.dob, GETDATE()), 101) / 5 = DATEDIFF(YEAR, m.dob, GETDATE()) / 5)Can you please help in this regard. Select * from member mWHERE (m.clientid = 16) AND (w.benelig = 1) AND (CONVERT(NUMERIC, DATEDIFF(YEAR, m.dob, GETDATE()), 101) / 5 = DATEDIFF(YEAR, m.dob, GETDATE()) / 5)Thanks.
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
shakilhyd
Starting Member
16 Posts |
Posted - 2008-08-03 : 23:00:59
|
Hi,What will be updated script. Can you please update script.Thanks.quote:
Originally posted by tkizer
Then substract off 2 years using DATEADD.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-08-03 : 23:59:08
|
answered at http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3699966&SiteID=1SELECT * FROM Curben C WHERE m.optcode = 'Y' AND m.dob >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 2, 0) -- more AND equal to 2 years ago 1st of Jan : 2006-01-01AND m.dob < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0) -- less than 1 YEAR 1st of Jan : 2007-01-01
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|
|
Need help in Query