Converstion 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

General SQL Server Forums

New to SQL Server Programming

Converstion to DateTime

Author

Topic

gavakie
Posting Yak Master

221 Posts

Posted - 2013-06-03 : 18:00:59

I have two fields ive concatanated like this

,convert(varchar,date,101 ) + ' ' + cast(time as varchar(8))START_TIME

The date is a datetime fields. Time is a nvarchar field. I need them combined into a datetime field. How can I do this?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-03 : 18:24:23

Probably the easiest is this (assuming you want to throw out any time part that came along in the date field)

CAST(date AS DATE) + CAST(TIME AS DATETIME)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 23:42:15

in any case this would work. In case date field itself has a timepart other than 00:00, the passed time gets added to it

DATEADD(ss,DATEDIFF(ss,0,[time]),[date])

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

gavakie
Posting Yak Master

221 Posts

Posted - 2013-06-04 : 09:53:55

When I try to convert the TIME field which is nvarchar to datetime it gives me this error

Msg 242, Level 16, State 3, Line 1
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-04 : 12:20:39

quote:
Originally posted by gavakie

When I try to convert the TIME field which is nvarchar to datetime it gives me this error

Msg 242, Level 16, State 3, Line 1
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

That means you have some invalid data in your time column. Examine the data to see what it contains. If you are on SQL 2012, you can run this query to find the invalid rows:

SELECT * FROM YourTable where TRY_PARSE([time] AS TIME) IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources