CODEWITHSUNDEEP
t-sqldatetimevarchar
slandau
slandau

Reputation: 24052

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'

Upvotes: 2

Views: 13246

Answers (2)

Widor
Widor

Reputation: 13265

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)

Upvotes: 5

Hank Freeman
Hank Freeman

Reputation: 1212

Try this one !

select  CONVERT(VARCHAR(10), GETDATE(), 112) 
+ right('0'+cast(datepart(hh, GETDATE()) as varchar(2)),2)
+ right('0'+cast(datepart(mi, GETDATE()) as varchar(2)),2)
+ right('0'+cast(datepart(ss, GETDATE()) as varchar(2)),2) as 'DateTime_STR2'

Upvotes: 0

Related Questions