CODEWITHSUNDEEP
sqlsql-server
w0051977
w0051977

Reputation: 15807

Format a string as American

Please see the SQL below:

select cast('13/01/2015' as datetime)

The error is: 'The conversion of a varchar data type to a datetime data type resulted in an out-of-range value'. I know I can do this will resolve it:

select cast('01/13/2015' as datetime)

Is there a way of formatting a string of: '13/01/2015' as '01/13/2015'

Upvotes: 0

Views: 87

Answers (4)

TheCurt
TheCurt

Reputation: 26

You could try:

SELECT CONVERT(CHAR(10),CONVERT(DATETIME,'13/01/2015',103),101))

This will convert the string to a date time in the format of DD/MM/YYYY, and then it will convert it to the MM/DD/YYYY format you are looking for.

Edit Note: I noticed you wanted the resultant in a string.

Upvotes: 0

HKImpact
HKImpact

Reputation: 620

I would use CONVERT, but if you choose to use CAST then you might want to try this...

SET LANGUAGE british
SELECT CAST('13/01/2015' AS Datetime)

SET LANGUAGE us_english
SELECT CAST('01/13/2015' AS Datetime)

This still wont fix your formatting issue though. That's why I would use ..

CONVERT(DATETIME,'01/13/2015',101)

Upvotes: 0

Izzy
Izzy

Reputation: 6866

You can set the dateformat and do something like

set dateformat dmy

select cast('13/01/2015' as datetime)

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

Don't use cast, use Convert. this way you can choose the date format.

select convert(datetime, '13/01/2015', 103)

select convert(datetime, '01/13/2015', 101)

Upvotes: 1

Related Questions