SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Casting char to datetime
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

slihp
Yak Posting Veteran

55 Posts

Posted - 04/08/2013 :  16:50:30  Show Profile  Reply with Quote
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

USA
15636 Posts

Posted - 04/08/2013 :  17:46:51  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

55 Posts

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

slihp
Yak Posting Veteran

55 Posts

Posted - 04/09/2013 :  05:20:39  Show Profile  Reply with Quote
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 :-)

Edited by - slihp on 04/09/2013 05:27:35
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/09/2013 :  05:24:14  Show Profile  Reply with Quote
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

Edited by - visakh16 on 04/09/2013 05:25:26
Go to Top of Page

slihp
Yak Posting Veteran

55 Posts

Posted - 04/09/2013 :  05:29:14  Show Profile  Reply with Quote
yep visakh you just beat me, thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/09/2013 :  05:35:13  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000