CODEWITHSUNDEEP
sqlsql-server
Big_Dwarf90
Big_Dwarf90

Reputation: 77

Unexpected results with convert datetime

I was trying to get the date formatted like 01/01/2012 6:10:00 PM but I keep getting 2012-01-01 18:10:00.000, the Microsoft manual suggest that I use format 103 to my understanding. 103 = dd/mm/yyyy

this is what I have 

SELECT convert(datetime,'01-01-2012 6:10:00 PM',103)

what am I doing wrong here?

Upvotes: 0

Views: 64

Answers (1)

GarethD
GarethD

Reputation: 69759

When converting to a datetime, the style (in your case 103) indicates the format of the string being received, i.e. SELECT CONVERT(DATE, '01/02/2012', 103), will be interpreted as 1st February, rather than 2nd January. The output of this conversion will be of the type DATETIME, and dates have no implicit format, they can be represented by a string in different formats, but the datetime itself has none. So you first need to covert your string to a datetime using the style based on the input (105: dd-mm-yyyy or 110:mm-dd-yyyy ), then convert it back to a string using the style you want to output (103):

SELECT CONVERT(VARCHAR(10), CONVERT(DATETIME, '01-02-2012 6:10:00 PM', 105), 103),
        CONVERT(VARCHAR(10), CONVERT(DATETIME, '01-02-2012 6:10:00 PM', 110), 103)

If your input is a string in a constant format, is always valid, and you want to output a string, then you may find that simple string manipulation works best for you. e.g.

SELECT REPLACE(LEFT('01-02-2012 6:10:00 PM', 10), '-', '/')

Gives:

01/02/2012

This doesn't have the overhead of conversion so should be faster (I have not tested to see if this is significant), nor the potential errors from it, but this may not be what you want.

Upvotes: 2

Related Questions