Date Syntax - 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 Syntax

Author

Topic

Limuh
Yak Posting Veteran

94 Posts

Posted - 2009-02-26 : 01:05:08

hello i want to get the syntax in SQL 2005 to get the data of <= 365 days ago from today's date. Any help will be appreciated.Thanks

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-26 : 01:39:26

try any one of these
select * from urtable where datecolumn <= GETDATE()-365

select * from urtable where datecolumn <= DATEADD(YY,-1,GETDATE())

select * from urtable where dateadd(dd,datediff(dd,0,datecolumn),0) <= dateadd(dd,datediff(dd,0,DATEADD(YY,-1,GETDATE())),0)

Limuh
Yak Posting Veteran

94 Posts

Posted - 2009-03-01 : 23:52:29

Thank you! :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-02 : 00:07:53

For performance reasons, don't use the third one!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-02 : 00:12:07

quote:
Originally posted by Limuh

Thank you! :)

welcome

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-02 : 01:03:52

Also try this

SELECT * FROM urtable where datecol between dateadd(dd,datediff(dd,0,DATEADD(YY,-1,GETDATE())),0) and dateadd(dd,datediff(d,0,getdate()),0)

Limuh
Yak Posting Veteran

94 Posts

Posted - 2009-03-02 : 07:03:17

Thanks for the reply, but i have a question regarding displaying the date, i dont want to display that have a date of '1900-01-01 00:00:00' that is why i created this code:

CASE WHEN <DateColumn> = '1900-01-01 00:00:00' THEN ''
ELSE <DateCoulumn>
END

Is there any way to get rid of the time 00:00:00? like using getdate() syntax?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-02 : 07:21:35

select dateadd(dd,datediff(dd,0,getdate()),0)
see this links for conversion of dates in different formats
http://www.sql-server-helper.com/tips/date-formats.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80563

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-02 : 08:59:08

quote:
Originally posted by bklr

try any one of these
select * from urtable where datecolumn <= GETDATE()-365

select * from urtable where datecolumn <= DATEADD(YY,-1,GETDATE())

select * from urtable where dateadd(dd,datediff(dd,0,datecolumn),0) <= dateadd(dd,datediff(dd,0,DATEADD(YY,-1,GETDATE())),0)

just keep in mind that these will take timepart as well.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources