Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL code to check for records 365 prior to today

Status
Not open for further replies.

Kruzer

Programmer
Jun 16, 2000
117
US
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
 
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
 
do you have an alternative to using the DateADD() function?

Dano
What's your major malfunction
 
Why don't you want to use DATEADD()?

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

Code:
WHERE CAST(field1 AS DATETIME) >= DATEADD(d, -365, NOW())
 
Do you mean you want to strip time portion?
Code:
declare @BeginDate datetime

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

select * from myTable where DateFld <=@BeginDate

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

you need

Datefield >= sysdate-365

Ian
 
Wait... where was Oracle mentioned?

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



Just my 2¢

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

--Greg
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top