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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 get last year records

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 forward
but 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.
Go to Top of Page

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?
Go to Top of Page

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);
Go to Top of Page

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
Go to Top of Page

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 same

James query will translate to date value of

2012-01-01 and start from it

if you want it to start from current date last year then it should be

date_added >= DATEADD(yy,-1,DATEADD(dd, DATEDIFF(dd, 0, GETDATE()),0))


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-06 : 03:55:31
also see


http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-06 : 10:00:56
Check if this works for you

SELECT Columns FROM YourTable
WHERE 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))

Cheers
MIK
Go to Top of Page

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)

OR

2. 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)

OR

2. 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)
Go to Top of Page
   

- Advertisement -