Using Previous Day as DateTime duration

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

Using Previous Day as DateTime duration

Author

Topic

wabaker95
Starting Member

12 Posts

Posted - 2008-09-19 : 12:43:35

Say I have a date/time column named Added and I want to get information from the previous day as a time duration.

How would I write my where clause to get the previous day from midnight til that minute before todays's midnight?

select * from Table
WHERE Added >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -1) AND

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-19 : 12:53:18

select * from Table
WHERE Added >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -1) AND
Added <= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

X002548
Not Just a Number

15586 Posts

Posted - 2008-09-19 : 12:54:05

I'm guessing it's a time duration for a "thing" (i.e. Primary Key)

Do you have any sample data and expected results?

What if the furst entry for a day is 12:01AM and the previous entry is 11:59PM...do you want to see 2 minutes?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2008-09-19 : 12:55:31

quote:
Originally posted by visakh16

select * from Table
WHERE Added >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -1) AND
Added <= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

You must work for the outsourcing firm my company hired...

What that does that do, except potentially get a boatlod of rows?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-19 : 12:58:21

quote:
Originally posted by visakh16

select * from Table
WHERE Added >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -1) AND
Added <= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

assuming your interpretation of the question is correct I think that should be:

...AND Added < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

Be One with the Optimizer
TG

wabaker95
Starting Member

12 Posts

Posted - 2008-09-19 : 12:59:37

I just need the results from Midnight to begin previous day until 11:59PM of previous day.
This is basically a report query to get new accounts added for each previous day.

Visakh if I used your code would it include 11:59PM because Midnight would start the current day.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-19 : 13:06:12

quote:
Originally posted by wabaker95

I just need the results from Midnight to begin previous day until 11:59PM of previous day.
This is basically a report query to get new accounts added for each previous day.

Visakh if I used your code would it include 11:59PM because Midnight would start the current day.

yup it will include. just make modification as TG suggested. My soln will include midnight current day also which is not you intend i guess. so modify as per TG's soln.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-19 : 13:07:04

quote:
Originally posted by X002548

quote:
Originally posted by visakh16

select * from Table
WHERE Added >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -1) AND
Added <= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

You must work for the outsourcing firm my company hired...

What that does that do, except potentially get a boatlod of rows?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

what did you mean by that?

wabaker95
Starting Member

12 Posts

Posted - 2008-09-19 : 15:16:29

Thanks everyone for their help it. The query does what I want it to.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources