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.
| Author |
Topic |
|
cstokes91
Yak Posting Veteran
72 Posts |
 Posted - 2013-03-21 : 12:18:03
|
Hey,I have a 3rd party database that I am converting to our datebase and am trying to find the easiest/best way to convert some data. The source data has the date in char(8) as yyyymmdd and a simple CAST(DATE AS DATETIME) AS OURDATE worked as needed.With their time field, it isn't working as smoothly... That is in char(6) and it is hhmmss. Should I just plug in a default date and then convert it to datetime? I have tried the following code but it is erroring out...cast((date+' '+time_rcvd) as datetime) as TRCVD |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2013-03-21 : 12:41:22
|
| You are probably going to have to substring it.CAST((Date + ' ' + SUBSTRING(time_rcvd,1,2) + ':' + SUBSTRING(time_rcvd,3,2) + ':' + SUBSTRING(time_rcvd,5,2)) AS DATETIME) |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-03-21 : 12:47:40
|
| If is somthing you need to do alot, I'd suggest either:1. Making a function to do the conversion2. Or create a table you can join to or do a lookup by the char(6) value. |
|
|
|
cstokes91
Yak Posting Veteran
72 Posts |
Posted - 2013-03-21 : 13:06:40
|
| Gotcha, thanks RickD... worked perfectly. @Lamprey No, it is just going to be a one time thing. I am pulling out all of the data out of a clients legacy system and putting it into our database so am fighting with some crappy database design haha. |
|
|
|
|
|
|
Convert hhmmss to datetime