Friday, March 30, 2012

Relative Dates

I need to create an SQL Statement that pulls data selected by a datetime
field. i want to be able to select dates and times relative to the time that
the query is run.
Can these date manipulations be done in the SQL statement?
ThanksHave a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO
GetDate() will allow you to compare to the time the query was run.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||i am not sure how i would use thet for waht i want, basically to start with
i
need to get all records that were created TODAY. then go on to records
created within the last n days
"Paul Ibison" wrote:

> Have a look at DateDiff in BOL. Here is an example from SQL Server 2000 BO
L:
> USE pubs
> GO
> SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
> FROM titles
> GO
> GetDate() will allow you to compare to the time the query was run.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||Hi Mark,
something like this should do it:
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) = 0
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) <= n
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
Expanding on Paul's example, if you want to consider date and time:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, GETDATE())
To consider date only:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, DATEDIFF(day, 0, GETDATE())
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Shields" <MarkShields@.discussions.microsoft.com> wrote in message
news:2EE795F5-DA9D-4E51-A78B-57C929EDAE6B@.microsoft.com...[vbcol=seagreen]
>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
> "Paul Ibison" wrote:
>

No comments:

Post a Comment