Search code examples
t-sqldatetimevarchar

Converting date/time in sql to string

Is there any way that I can convert the DateTime SQL value of this:

2011-10-11 00:00:00.000

to a string formatted exactly the same?

'2011-10-11 00:00:00.000'

I tried doing cast(fl_from_dt as varchar(50) but it converts it to something readable - ex. 'Oct 11 2011 12:00AM'

Solution

  • Are you using SqlServer?

    Take a look at the style parameter of CONVERT() here: http://msdn.microsoft.com/en-us/library/ms187928.aspx - in particular have a look at the ODBC canonical (with milliseconds) format.

    For short you should use style 121 in the CONVERT() command.

    Style: 121

    ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset

    yyyy-mm-dd hh:mi:ss.mmm(24h)