DateTime calculation

[Jcarr38]

I'm displaying records and using a field called ratedOn which is a datetime attribute with a getdate() default value. To display records on a page, say i wanted to only show records that are about 7 days old and anything older than 7 days, it won't display. How could i use a calculation in my query...

So for this example below only show records from May31st till today:

ratedon: 2007-06-06 12:00:10.020

I'd appreciate any help.

 

[AlexCuse]

Have a look in SQL Server Help (aka Books On Line) at the dateadd function.

To get rid of time component you can do this:

select dateadd(day, datediff(day, 0, getdate()), 0)

This uses datediff to get the number of whole days between now and date 0 (1/1/1900). Then adds this to zero to get the date (without hours/min/seconds added).

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SQLDenis]

where DateColumn > getdate() -7

or better yet if you want from midnight

where DateColumn > dateadd(dd, datediff(dd, 0, getdate())-7, 0)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[gmmastros]

or equal to ?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Jcarr38]

Thank you i appreciate the help..

 

[SQLDenis]

yes you need >= instead of > you will miss the exact time at midnight otherwise

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[SQLBill]

Check out the FAQ section...there is a section on DATETIME Tips & Tricks. Pretty good reading in there.

-SQLBill

Posting advice: FAQ481-4875