CODEWITHSUNDEEP
sql-server-2008
Nick Kahn
Nick Kahn

Reputation: 20078

SQL Server Date Formats (YYYY-MM-DD HH:MI:SS.MMM)

Update: i am still getting this (4/14/2012 2:44:01 PM) result after trying this

 select convert(varchar(30), '4/14/2012 2:44:01 PM',121)

End Update

what format should i use in order to get this result set:

2012-04-14 14:44:01.683

i tried using this but does not show the micro/milli seconds

select convert(varchar(23), '4/14/2012 2:44:01 PM',120)

gets me this result:

4/14/2012 2:44:01 PM

Upvotes: 2

Views: 22956

Answers (3)

sanjay bhansali
sanjay bhansali

Reputation: 327

Convert your date string in datetime format , as below

select convert(varchar(23), convert(datetime,'4/14/2012 2:44:01 PM'),120)

Upvotes: 2

Abdul Ahad
Abdul Ahad

Reputation: 2221

select convert(varchar(30), CAST('4/14/2012 2:44:01 PM' As DATETIME),121)

Upvotes: 1

marc_s
marc_s

Reputation: 754348

You are very close - in the chapter on CAST and CONVERT on MSDN Books Online, you've missed the right answer by one line.... you can use style no. 121 (ODBC canonical (with milliseconds)) to get the result you're looking for: 

SELECT CONVERT(VARCHAR(30), GETDATE(), 121)

This gives me the output of:

2012-04-14 21:44:03.793

Update: based on your updated question - of course this won't work - you're converting a string (this: '4/14/2012 2:44:01 PM' is just a string - it's NOT a datetime!) to a string......

You need to first convert the string you have to a DATETIME and THEN convert it back to a string!

Try this:

SELECT CONVERT(VARCHAR(30), CAST('4/14/2012 2:44:01 PM' AS DATETIME), 121)

Now you should get:

2012-04-14 14:44:01.000

All zeroes for the milliseconds, obviously, since your original values didn't include any ....

Upvotes: 4

Related Questions