Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Casting char to datetime

Author  Topic 

slihp
Yak Posting Veteran

61 Posts

Posted - 2013-04-08 : 16:50:30
Im sure im being really stupid, but what is happening here

declare @Time varchar(11)
set @Time ='02:05.645'

select @Time, CAST(@Time AS DATETIME)

im wanting to convert the string 02:05.645 to a time, but was expecting the milliseconds to stay as .645, but if you run it they come through as .647

i it a units issue/conversion? and how would i pull the .645 through

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-04-08 : 17:46:51
Datetime has a precision of 3 ms, it will round anything to the nearest 3, 7 or 10 millisecond value:
WITH n(n) AS (SELECT 0 UNION ALL SELECT n+1 FROM n WHERE n<100)
SELECT n, DATEADD(ms, n, 0) FROM n
If you need to preserve those milliseconds you'll need to use datetime2 or time datatypes, if you're using SQL 2008 or higher. Otherwise you'll need a separate column for milliseconds.
Go to Top of Page

slihp
Yak Posting Veteran

61 Posts

Posted - 2013-04-08 : 18:41:59
oh thats great thanks,never knew that. every days a school day:0
Go to Top of Page

slihp
Yak Posting Veteran

61 Posts

Posted - 2013-04-09 : 05:20:39
OK tried this

SET DATEFORMAT DMY

declare @Time varchar(11)
set @Time ='02:05.645'

select @Time, CAST(@Time AS DATETIME),CAST(@Time AS DATETIME2)


and getting a "Conversion failed when converting date and/or time from character string." on the datetime2 cast. using sql 2008 R2

*edit* was missing the hours on the time so '00:02:05.645' now cast :-)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-09 : 05:24:14
quote:
Originally posted by slihp

OK tried this

SET DATEFORMAT DMY

declare @Time varchar(11)
set @Time ='02:05.645'

select @Time, CAST(@Time AS DATETIME),CAST(@Time AS DATETIME2)


and getting a "Conversion failed when converting date and/or time from character string." on the datetime2 cast. using sql 2008 R2


see
http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

it should be

declare @Time varchar(11)
set @Time ='00:02:05.645'

select @Time, CAST(@Time AS DATETIME),CAST(@Time AS DATETIME2)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

slihp
Yak Posting Veteran

61 Posts

Posted - 2013-04-09 : 05:29:14
yep visakh you just beat me, thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-09 : 05:35:13
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -