Data formatting: GETDATE() - 1 day, with time 00:00:00.000

I need a function build that returns whatever yesterday was, with the timestamp of 00:00:00.000

i.e. Set @variable =  CONVERT(datetime, getdate(), 101), with time 00:00:00.000 instead of 15:25:55:042

TIA
Jim
LVL 66
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
select cast(convert(varchar(10), dateadd(d, -1, getdate()), 101) as datetime)
0
 
James MurrellConnect With a Mentor Product SpecialistCommented:
0
 
darkmooinkCommented:
CONVERT(datetime, month(getdate()) + day(getdate()) + year(getdate()), 101)
i think this might work
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
>select cast(convert(varchar(10), dateadd(d, -1, getdate()), 101) as datetime)
Worked.

>http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64759
This could come in handy.

>CONVERT(datetime, month(getdate()) + day(getdate()) + year(getdate()), 101)
returned 1905-07-27 00:00:00.000
0
 
darkmooinkCommented:
doh sorry
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.