CODEWITHSUNDEEP
sql-serverdatecastingformat
Benoît S
Benoît S

Reputation: 163

SQL Format date to YYY-MM-DD 00:00:00.000

I have multiple dates and I would like to set Hours, minutes, seconds and milliseconds to zero in the SELECT.

For instance : 2017-10-10 15:23:28.603 becomes 2017-10-10 00:00:00.000

So far I could set everything to zero expect Hours.

dateadd(hour, datepart(hour,getdate()),CAST(convert(varchar(20),getdate(),112) as datetime)) AS 'Date',

Gives : 2017-10-10 15:00:00.000, how to get rid of the hours too ?

Upvotes: 1

Views: 26828

Answers (1)

gbn
gbn

Reputation: 432271

Just cast to date and back

SELECT GETDATE(), CAST(CAST(GETDATE() AS date) AS datetime)

gives

2018-06-05 10:53:41.937    2018-06-05 00:00:00.000

Upvotes: 7

Related Questions