Select a date minus whatever todays date is? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Select a date minus whatever todays date is?

Author

Topic

Topaz
Posting Yak Master

199 Posts

Posted - 2008-02-15 : 05:47:42

At the moment i have a query that is dependent on a date which is 42 days before whatever the date may be today.

The statement in my query I have to use is:

MailDate <= '2008-01-05'

I am wondering if i could make that statement automatically take off 42 days from todays date?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-15 : 05:50:08

SELECT * FROM Table WHERE MailDate < DATEADD(DAY, -42, GETDATE())

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-15 : 05:51:40

SELECT * FROM Table WHERE MailDate < DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101')

This calculates the entire day. My previous response calculated 42 days back to the millisecond.

E 12°55'05.25"
N 56°04'39.16"

Topaz
Posting Yak Master

199 Posts

Posted - 2008-02-15 : 05:53:31

quote:
Originally posted by Peso

SELECT * FROM Table WHERE MailDate < DATEADD(DAY, -42, GETDATE())

E 12°55'05.25"
N 56°04'39.16"

Many thanks!!!

Topaz
Posting Yak Master

199 Posts

Posted - 2008-02-15 : 05:54:26

quote:
Originally posted by Peso

SELECT * FROM Table WHERE MailDate < DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101')

This calculates the entire day. My previous response calculated 42 days back to the millisecond.

E 12°55'05.25"
N 56°04'39.16"

Is this anything i need to change? '19000101'

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-15 : 06:00:46

You can use 0 as well there instead of '19000101'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

schizmark
Starting Member

5 Posts

Posted - 2008-02-17 : 15:50:53

Maybe I'm just more new here than everyone else, but I was just working on something similar in a tutorial last week. It was actually something else, but I had the same question about subtracting days. I ended up with this:
declare @DateHired as datetime,
@CurrentDate as datetime,
@GoodNews as varchar(30),
@BadNews as varchar(30)
set @DateHired = '2007/11/05'
set @CurrentDate = getdate()
set @GoodNews = 'You qualify for a raise'
set @BadNews = 'Maybe next month'
if @DateHired < (@CurrentDate - 90)
select @DateHired as Hired, @CurrentDate as Today, @GoodNews as Result
else
select @DateHired as Hired, @CurrentDate as Today, @BadNews as Result
go

Not fancy, but it worked. If I put the time in there, I even got varying answers based on the minute the query was run.

Schiz

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-17 : 16:15:29

SELECT @DateHired AS Hired,
CASE WHEN @DateHired < @CurrentDate - 90 THEN 'You qualify for a raise' ELSE 'Maybe next month' END AS Result

E 12°55'05.25"
N 56°04'39.16"

schizmark
Starting Member

5 Posts

Posted - 2008-02-17 : 16:18:36

I like that. I haven't seen CASE WHEN yet. Later lesson.

If you haven't tried, you've already failed.
Schiz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources