[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1640
  • Last Modified:

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
0
Jim Horn
Asked:
Jim Horn
2 Solutions
 
chapmandewCommented:
select cast(convert(varchar(10), dateadd(d, -1, getdate()), 101) as datetime)
0
 
James MurrellProduct 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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now