select * from table where orderdate = getdate()-1

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 * from table where orderdate = getdate()-1

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-06-01 : 10:28:40

i have a query

select * from table where orderdate = getdate()-1

I get no result

when i do select * from table where orderdate = '5/31/2007'

i get 208 rows returned. is there a way to get it using getdate()-1
so that i can use it in a procedure. i know i am doing something wrong.

Ashley Rhodes

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-06-01 : 10:37:54

Try datediff instead of using -1

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-01 : 10:45:13

Select * from Table
Where dateadd(day, 0, datediff(day, 0, OrderDate)) =
dateadd(day, 0, datediff(day, 0, dateadd(day, -1, getdate())))

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-06-01 : 10:45:27

Also, this awesome article to explain datetime and how to use it.
http://www.devx.com/dbzone/Article/34594/

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-01 : 10:46:10

to make use of datecol's index, use

Where datecol>=dateadd(day,datediff(day,0,getdate()),-1)
and datecol<dateadd(day,datediff(day,0,getdate()),0)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-01 : 10:48:17

Also this one
http://www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-06-01 : 10:55:51

VERY nice article Madhi!

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-01 : 11:01:36

quote:
Originally posted by DonAtWork

VERY nice article Madhi!

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Yes it is

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources