SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 get last year records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kt
Yak Posting Veteran

82 Posts

Posted - 03/05/2013 :  17:37:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/05/2013 :  18:49:21  Show Profile  Reply with Quote
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

Malaysia
961 Posts

Posted - 03/05/2013 :  20:47:42  Show Profile  Reply with Quote
quote:

select *
from [dbo].[company]
where year(date_added) >= DATEADD(year,-1,GETDATE())

liks this?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 03/05/2013 :  23:29:45  Show Profile  Reply with Quote
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

India
88 Posts

Posted - 03/06/2013 :  02:41:40  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 03/06/2013 :  03:54:35  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 03/06/2013 :  03:55:31  Show Profile  Reply with Quote
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

82 Posts

Posted - 03/06/2013 :  09:28:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/06/2013 :  10:00:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 03/06/2013 :  10:02:38  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000