Casting varchar as DateTime SQL Server 2005 with smallest unit of time
In SQL Server 2005, does it only calculate to milliseconds?
Looking for an explanation for below
DECLARE @dt VARCHAR(26)
SET @dt = '2013-07-24 12:20:42.005135'
--This works
SELECT @dt
--This works
SELECT CONVERT(VARCHAR(23),@dt)
--This fails
SELECT CAST(@dt AS DATETIME)
--Msg 241, Level 16, State 1, Line 7
--Conversion failed when converting datetime from character string.
Looking for an explanation for below
DECLARE @dt VARCHAR(26)
SET @dt = '2013-07-24 12:20:42.005135'
--This works
SELECT @dt
--This works
SELECT CONVERT(VARCHAR(23),@dt)
--This fails
SELECT CAST(@dt AS DATETIME)
--Msg 241, Level 16, State 1, Line 7
--Conversion failed when converting datetime from character string.
Oops, I'm sorry! Looks like you are trying to convert VARCHAR back to DATETIME. :)
Here you go .. VARCHAR to DATETIME
Here you go .. VARCHAR to DATETIME
SELECT CONVERT(DATETIME,CAST('2013-07-24 12:20:42.005135' AS VARCHAR(23)))
SELECT CONVERT(DATETIME,CAST(@dt AS VARCHAR(23)))ASKER
My question was does sql server only go to milliseconds?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
Open in new window
And if you would like your date in a different format, here's a reference for changing 101 (above) to a different date format.
http://www.sql-server-helper.com/tips/date-formats.aspx