CODEWITHSUNDEEP
sqlsql-server
Ludovic Aubert
Ludovic Aubert

Reputation: 10528

sql server convert string representing datetime to date

I am trying to cast a string '2019-31-01 09:00:00' to a date. 

SELECT CAST('2019-31-01 09:00:00.000' AS date);

SQL Server returns an error message: failed conversion of the date and/or time from a character string.

Whereas

DECLARE @MyDate AS datetime = '2019-31-01 00:00:00';
SELECT CAST(@MyDate AS date)    
--2019-01-31

Works. Because of the Locale. Confusing ?

Upvotes: 1

Views: 113

Answers (2)

Anirudh Shankar
Anirudh Shankar

Reputation: 13

Date format to be in : yyyy-mm-dd:

SELECT CAST('2019-01-31 09:00:00.000' AS date);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Use a reasonable date/time format (YYYY-MM-DD):

SELECT CAST('2019-01-31 09:00:00.000' AS date);

Or what Microsoft recommends:

SELECT CAST('2019-01-31T09:00:00.000' AS date);

Note: You will lose the time component.

Upvotes: 1

Related Questions