cast(convert(cast ... oh my/ is there a better way

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)

cast(convert(cast ... oh my/ is there a better way

Author

Topic

LaurieCox

158 Posts

Posted - 2010-12-30 : 13:56:03

(with a right thrown in for good measure) ...

I want to take the date part of a date time field and combine it with a varchar field that has a time in the form of hh:mm PM or hh.mm AM and turn it into a single date time.

Table Def and sample data:


create table #Dates_Convert(
       serv_start_date datetime,
       serv_start_time varchar(8)) 

insert into #Dates_Convert
SELECT '2010-06-23 00:00:00.000', '11:30 AM' UNION ALL
SELECT '2010-07-21 00:00:00.000', '03:00 PM' UNION ALL
SELECT '2010-10-11 00:00:00.000', '01:00 PM' UNION ALL
SELECT '2010-10-14 00:00:00.000', '03:15 PM' UNION ALL
SELECT '2010-11-30 00:00:00.000', '04:00 PM' UNION ALL
SELECT '2010-12-14 00:00:00.000', '03:45 PM' UNION ALL
SELECT '2010-12-29 00:00:00.000', '10:30 AM' UNION ALL
SELECT '2010-09-16 00:00:00.000', '08:45 AM' UNION ALL
SELECT '2010-09-23 00:00:00.000', '09:30 AM' UNION ALL
SELECT '2010-10-26 00:00:00.000', '02:05 PM'

So with a lot of trial and error; I came up with this:


select serv_start_date
      ,serv_start_time
--      ,cast(serv_start_date as varchar(10))
--      ,convert(varchar(10),serv_start_date,120) 
--      ,convert(varchar(30),cast(serv_start_time as datetime),120)
--      ,right(convert(varchar(19),cast(serv_start_time as datetime),120),9)
      ,cast(convert(varchar(10),serv_start_date,120) +
        right(convert(varchar(19),cast(serv_start_time 
              as datetime),120),9) as datetime)
from #dates_convert

Which works as it gives the result I want:


serv_start_date         serv_start_time results
----------------------- --------------- -----------------------
2010-06-23 00:00:00.000 11:30 AM        2010-06-23 11:30:00.000
2010-07-21 00:00:00.000 03:00 PM        2010-07-21 15:00:00.000
2010-10-11 00:00:00.000 01:00 PM        2010-10-11 13:00:00.000
2010-10-14 00:00:00.000 03:15 PM        2010-10-14 15:15:00.000
2010-11-30 00:00:00.000 04:00 PM        2010-11-30 16:00:00.000
2010-12-14 00:00:00.000 03:45 PM        2010-12-14 15:45:00.000
2010-12-29 00:00:00.000 10:30 AM        2010-12-29 10:30:00.000
2010-09-16 00:00:00.000 08:45 AM        2010-09-16 08:45:00.000
2010-09-23 00:00:00.000 09:30 AM        2010-09-23 09:30:00.000
2010-10-26 00:00:00.000 02:05 PM        2010-10-26 14:05:00.000

But is there a better way?

Laurie

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-12-30 : 14:11:03

This works OK for me using your test data.

select *,CombinedTime = serv_start_date+serv_start_time from #Dates_Convert

Results:

serv_start_date          serv_start_time  CombinedTime
-----------------------  ---------------  -----------------------
2010-06-23 00:00:00.000  11:30 AM         2010-06-23 11:30:00.000
2010-07-21 00:00:00.000  03:00 PM         2010-07-21 15:00:00.000
2010-10-11 00:00:00.000  01:00 PM         2010-10-11 13:00:00.000
2010-10-14 00:00:00.000  03:15 PM         2010-10-14 15:15:00.000
2010-11-30 00:00:00.000  04:00 PM         2010-11-30 16:00:00.000
2010-12-14 00:00:00.000  03:45 PM         2010-12-14 15:45:00.000
2010-12-29 00:00:00.000  10:30 AM         2010-12-29 10:30:00.000
2010-09-16 00:00:00.000  08:45 AM         2010-09-16 08:45:00.000
2010-09-23 00:00:00.000  09:30 AM         2010-09-23 09:30:00.000
2010-10-26 00:00:00.000  02:05 PM         2010-10-26 14:05:00.000

CODO ERGO SUM

LaurieCox

158 Posts

Posted - 2010-12-30 : 14:21:13

wow ... I didn't even think to try that. So you take the date and you take the time and you what? add them? ... who would have thought that would work? (duh)

...

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-30 : 14:28:02

Although concatinating/adding them together works, I really dislike those types of implicit converstions for several reasons.

LaurieCox

158 Posts

Posted - 2010-12-30 : 14:38:52

quote:
Originally posted by Lamprey

Although concatinating/adding them together works, I really dislike those types of implicit converstions for several reasons.

Hi Lamprey,

I am all for learning. Could you list some of the reasons?

Also does that mean that my original solution is good? Or is there a better (non implicit conversion) solution? I did not like my solution because it is hard to figure out what is going on. That is why I made this post to begin with.

Thanks,

Laurie

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-12-30 : 14:56:27

quote:
Originally posted by Lamprey

Although concatinating/adding them together works, I really dislike those types of implicit converstions for several reasons.

Then you could do it this way to make the conversion explicit:

select *,CombinedTime = serv_start_date+convert(datetime,serv_start_time) from #Dates_Convert

CODO ERGO SUM

LaurieCox

158 Posts

Posted - 2010-12-30 : 15:20:35

Ok, so the reason I never went down that path is because I went at it in steps and when I did this:

select convert(datetime,serv_start_time) from #Dates_Convert

I got this:


1900-01-01 11:30:00.000
1900-01-01 15:00:00.000
1900-01-01 13:00:00.000
1900-01-01 15:15:00.000
1900-01-01 16:00:00.000
1900-01-01 15:45:00.000
1900-01-01 10:30:00.000
1900-01-01 08:45:00.000
1900-01-01 09:30:00.000
1900-01-01 14:05:00.000

and I assumed that the 1900-01-01 would get in the way of adding the two dates ... and never even tried it. I figured the "+" was acting as a concatenation operator. I am now perusing the + (Add) (Transact-SQL) page from the BOL to learn how it works with dates.

Anyway thanks for the replies.

Laurie

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-30 : 15:25:11

quote:
Originally posted by AlgaeSea

<snip>
Hi Lamprey,

I am all for learning. Could you list some of the reasons?

<snip>

I'm of the opinion that implicit conversions are generally bad. A lot of people don't know about data type precedence and it can get people into trouble. Explicit conversion removes ambiguity and, possible, conversion issues (like converting a string to an int when you didn't intend that to happen).

But, how about a simple example:

SELECT '8.5' + 5

Bear with my contrived example, but if you saw that, would you know what I am expecting for output?? I surely wouldn’t have a clue. But, it produces a result without an error. Maybe I was expecting '8.55' or '13.5' or 13.5 or 13 or 14 or?

So, to me, implicit conversions should be avoided.

EDIT: Forgot to add, this is a semi-real example from an old co-worker that was upset with SQL not being able to read his mind about what he wanted. What he actually wanted was for SQL to convert the string to decimal and convert the int to decimal and add them together and then convert all that back to a string. I know, silly, but you can see how taking advantage of this "feature" can lead to issues.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources