SQL code to check for records 365 prior to today

[Kruzer]

I would like to write some SQL to gather all of the records that meet the condition of todays date and everything prior to 365 days...

where field1 (DATE now <= 365 days)

can someone provide a better way of doing this in SQL?


Thanks!

Dano
What's your major malfunction

 

[gbaughma]

WHERE field1 >= DATEADD(d,-365,NOW())

(I think)

Notice the >=, meaning "Anything greater or equal to 365 days ago (through today, or potentially the future)"



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[Kruzer]

do you have an alternative to using the DateADD() function?

Dano
What's your major malfunction

 

[PGO01]

Why don't you want to use DATEADD()?

If it is because field1 is not a datetime column you could cast it.

WHERE CAST(field1 AS DATETIME) >= DATEADD(d, -365, NOW())

 

[markros]

Do you mean you want to strip time portion?

declare @BeginDate datetime

set @BeginDate = dateadd(day, - 365 + datediff(day,'19000101', getdate()),'19000101')

select * from myTable where DateFld <=@BeginDate

PluralSight Learning Library

 

[IanWaterman]

In the ANSI forum some one did suggest you post this in an Oracle forum.

you need

Datefield >= sysdate-365

Ian

 

[gbaughma]

Wait... where was Oracle mentioned?

Silly me... assuming that a SQL question posted in the Microsoft SQL Server Programming forum was about... <gasp> Microsoft.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com