yesterday - 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

SQL Server 2000 Forums

Transact-SQL (2000)

yesterday

Author

Topic

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-10-15 : 05:00:06

hi,
how can i get the date of yesterdaY??
i tried this on my queries but it will be problem at the end of month i think.
select * from xxx where datepart(day,date) = datepart(day,getdate()) -1 and datepart(month,date) = datepart(month,getdate()) and datepart(year,date) = datepart(year,getdate())

well, if the yesterday is 31th of the month it wil lbe problem
when today is christmas it will be problem from the datepart(year,date) function.
what to do?

MS BLESS US

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-10-15 : 05:37:39

In case your datetime has a time part, you do it like this:

SELECT * FROM xxx
WHERE date >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0) AND date < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

and in case you don't, like this:

SELECT * FROM xxx
WHERE date = DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0)

both also has the benefit that an index on 'date' can be used instead of a complete table scan (way faster).

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-10-15 : 06:02:50

select dateadd(day,-1,getdate())

i think this solves too =)

MS BLESS US

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-10-15 : 14:00:53

Yes and no, it does give you the date of yesterday, but will contain the time part too, which in most cases is undesired, in your case too.

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

Kristen
Test

22859 Posts

Posted - 2006-10-15 : 14:18:39

mk.vignesh
Starting Member

3 Posts

Posted - 2006-10-17 : 01:45:28

U CAN TRY OUT
SELECT GETDATE()-1
IT GIVES EXACTLY THIS TIME YESTERDAY

Nobody is Perfect in this world,I am Nobody

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-17 : 10:35:17

www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

mfemenel
Professor Frink

1421 Posts

Posted - 2006-10-17 : 12:02:22

Does no one use the day function anymore? select day(getdate()-1)

Mike
"oh, that monkey is going to pay"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-18 : 09:48:42

quote:
Originally posted by mfemenel

Does no one use the day function anymore? select day(getdate()-1)

Mike
"oh, that monkey is going to pay"

It wont make use of index if that column has it
Read the article I posted

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-18 : 10:08:38

quote:
Originally posted by mfemenel

Does no one use the day function anymore? select day(getdate()-1)

It will be hard to do any searches with only day portion of a date.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources