Casting char to datetime - SQL Server Forums

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.

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

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 :-)

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

slihp
Yak Posting Veteran

61 Posts

Posted - 2013-04-09 : 05:29:14

yep visakh you just beat me, thanks.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources