Date - SQL Server Forums

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

Date

Author

Topic

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-07-15 : 11:48:48

I want to pull out all records that are greater than 30days.

I tried this? It returned 0 result.
usr_end_date > getdate() + 30

I also tried this. The data is not right.
usr_end_date > 30

I would appreciate your help.
Thanks,
Maachie

cvipin
Yak Posting Veteran

51 Posts

Posted - 2008-07-15 : 11:51:40

u might want to do this:

usr_end_date > dateadd(day, 30, GetDate())

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-15 : 11:55:50

usr_end_date > dateadd(dd,datediff(dd,0,getdate())+30,0)

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-07-15 : 12:43:31

this is very self explainatory
usr_end_date > dateadd(day, 30, GetDate())

if possible can you please explain how this line of code works?
usr_end_date > dateadd(dd,datediff(dd,0,getdate())+30,0)

both the above lines returns 0 records

does this mean that my line below is correct?
usr_end_date > getdate() + 30

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-15 : 13:25:29

quote:
Originally posted by Maachie

this is very self explainatory
usr_end_date > dateadd(day, 30, GetDate())

if possible can you please explain how this line of code works?
usr_end_date > dateadd(dd,datediff(dd,0,getdate())+30,0)

both the above lines returns 0 records

does this mean that my line below is correct?
usr_end_date > getdate() + 30

how is the data in your table? do you have enough data satisfying this condition in your table?
dateadd(dd,datediff(dd,0,getdate())+30,0) will return you the date which is 30 days from today after dropping the time part. This will ensure you get records even if its on 30th day after today with time less than current time i.e all records for the day.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 13:28:08

All queries above returns records that are dates TODAY + 30 days.
If you mean you want records that are 30 days old (from today), use

SELECT *
FROM Table1
WHERE usr_end_date >= dateadd(Day, datediff(day, '19000201', getdate()), '19000101')

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources