More fun with date math... - SQL Server Forums

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

More fun with date math...

Author

Topic

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-10-30 : 08:48:16

Sorry folks, I have not pestered in months (?) about date manipulation and on my last assignment on my last day they give me a report to build where I need to compare a user selected date to my Last_Edit_Date. I have that logic down but am struggling again to subtract # of days and then strip the time off. I need to return the same results regardless of what time today it is and what time the record was last edited on the number of days a user selects.

Example assumes user selects 1.

As always, thank you in advance...

Right now it is:
2009/10/30 2009-10-30 08:44:55.463

I want to subtract 1 day so I use:
DATEADD(day,-1,getdate()) AS Today_1
to get:
2009/10/30 2009-10-29 08:44:55.463

What would be the easiest way to instead return:
2009/10/30 2009-10-30 00:00:00.000 (or 12AM-Midnight)

John
It's a small world (but I wouldn't want to paint it)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-30 : 09:04:03

Start with this

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

Madhivanan

Failing to plan is Planning to fail

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-10-30 : 09:12:47

Thank you...

to go from
DATEADD(day,-1,getdate())

to
dateadd(day,datediff(day,1,getdate()),0),

I just have a tough time mentally doing the "let's start from Day#1" with:
datediff(day,1,getdate())

John
It's a small world (but I wouldn't want to paint it)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-30 : 09:17:53

So, if the input is 1 you want to get only yesterday's data or all data starting from yesterday?

Madhivanan

Failing to plan is Planning to fail

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-10-30 : 09:21:06

I will be prompting the user to select a number of days and then I will select records
where last_edit_date <= dateadd(day,datediff(day,@days,getdate()),0)

John
It's a small world (but I wouldn't want to paint it)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-30 : 09:23:23

It should be

where last_edit_date < dateadd(day,datediff(day,@days,getdate()),0)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-03 : 05:36:58

Did you get what you needed?

Madhivanan

Failing to plan is Planning to fail

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-11-03 : 06:25:19

Yes, thanks.

John
It's a small world (but I wouldn't want to paint it)