yesterday date - 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 2005 Forums

Transact-SQL (2005)

yesterday date

Author

Topic

Muj9
Yak Posting Veteran

75 Posts

Posted - 2013-09-04 : 04:46:15

Hi,

I would like to get yesterday's date. i know i can use getdate()-1 to get the date for yesterday by if i run the query at 8am it will only select data from yesterday at 8am but i would like the time for yesterday to be 12:00am.

for example current date = 04/08/2013 09:44
getdate()-1 = 03/08/2013 09:44
i want = 03/08/2013 12:00am

can somebody help me out please. Thank you.

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-09-04 : 05:03:15

[code]
declare @datGetDate as datetime
set @datGetDate='04/08/2013 09:44'

select Dateadd(d,datediff(d,1,@datGetDate),0)

[/code]

--output
2013-04-07 00:00:00.000

replace @datGetDAte with the GetDate()

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-09-04 : 05:05:55

[code]
this is the initial date
'2013-04-08 09:44:00.000'
[/code]

in my exemple:
04 is the month
08 is the day

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

Muj9
Yak Posting Veteran

75 Posts

Posted - 2013-09-04 : 05:14:07

Thank you stepson. it has worked. really appriciate it.

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-09-04 : 13:38:08

with welcome

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-04 : 14:08:53

[code]DECLARE @OriginalDate DATETIME = '20130804 09:44',
@Yesterday DATETIME;

SET @Yesterday = DATEDIFF(DAY, 1, @OriginalDate);

SELECT @Yesterday;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources