Author |
Topic |
kt
Yak Posting Veteran
88 Posts |
Posted - 2013-03-05 : 17:37:05
|
Hi,I want to query returns me records from last year forwardbut the sysntax i have below seems not right because didn't get anything back. Pls advice?select *from [dbo].[company]where year(date_added) >= DATEADD(year,-1,GETDATE())thanks |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-03-05 : 18:49:21
|
Your predicate doesn't make sense. What do you want for output? The ENTIRE previous year? The entire previous year and currect year? Or all there rows starting from one year prior to the current date?If the last one, then try removing the YEAR function from the date_added column and you should get the rows you are after. |
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2013-03-05 : 20:47:42
|
quote: select *from [dbo].[company]where year(date_added) >= DATEADD(year,-1,GETDATE())
liks this? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-05 : 23:29:45
|
Or perhaps this?SELECT *FROM [dbo].[company]WHERE dateadded >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0); |
|
|
mmkrishna1919
Yak Posting Veteran
95 Posts |
Posted - 2013-03-06 : 02:41:40
|
quote: Originally posted by waterduck
quote: select *from [dbo].[company]where year(date_added) >= DATEADD(year,-1,GETDATE())
liks this?
Hi waterduck,let's consider getdate() value while executing query is :"2013-03-06 02:33:33.223"Then the expression "DATEADD(year,-1,GETDATE())" will return the value as "2012-03-06 02:33:33.223"Then the result set will missed out few records..having date earlier than this expression date even those records belongs to last year date.I think "James K" query will return all records last year forward and it is an saragable where clause..Thanks..M.MURALI kRISHNA |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-06 : 03:54:35
|
quote: Originally posted by mmkrishna1919
quote: Originally posted by waterduck
quote: select *from [dbo].[company]where year(date_added) >= DATEADD(year,-1,GETDATE())
liks this?
Hi waterduck,let's consider getdate() value while executing query is :"2013-03-06 02:33:33.223"Then the expression "DATEADD(year,-1,GETDATE())" will return the value as "2012-03-06 02:33:33.223"Then the result set will missed out few records..having date earlier than this expression date even those records belongs to last year date.I think "James K" query will return all records last year forward and it is an saragable where clause..Thanks..M.MURALI kRISHNA
there are not the sameJames query will translate to date value of2012-01-01 and start from itif you want it to start from current date last year then it should bedate_added >= DATEADD(yy,-1,DATEADD(dd, DATEDIFF(dd, 0, GETDATE()),0))------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
kt
Yak Posting Veteran
88 Posts |
Posted - 2013-03-06 : 09:28:30
|
thank for all your reponsed. the data in table is 2012-12-14 00:00:00.000, 2011-10-10 00:00:00.000, 2010-05-12 00:00:00.000, i want to do the query return only last year record 2012-12-14 00:00:00.000, thanks |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-03-06 : 10:00:56
|
Check if this works for youSELECT Columns FROM YourTableWHERE Convert(datetime,Date_Added) Between DateAdd(year,-1,DateAdd(Year,DATEDIFF(YEAR,0,getdate()),0)) AND Dateadd(second,-1,DateAdd(Year,DATEDIFF(YEAR,0,getdate()),0))CheersMIK |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-06 : 10:02:38
|
quote: Originally posted by kt thank for all your reponsed. the data in table is 2012-12-14 00:00:00.000, 2011-10-10 00:00:00.000, 2010-05-12 00:00:00.000, i want to do the query return only last year record 2012-12-14 00:00:00.000, thanks
If you just want that one record, probably any of the queries that various people posted would work. Rather than looking the data that happens to be in the table at the moment and writing a query to get that data correctly, you should ask yourself what the general case should be and then write the query to work correctly in that general case.We were all trying to figure out what that general case would be - we don't know the answer, only you know. 1. Did you want to get all the rows as long as the date is within one year of the current date and time? (Later than 2012-03-06 09:59:42.380 as of now)OR2. Did you want to get all the rows as long as the date is within one year of the current date, not considering time (Later than 2012-03-06 or 2013-03-07)OR2. Did you want to get all the rows as long as the date is later than or equal to first day of the previous year? (Jan 1 2012 as of now) |
|
|
|