Need help with converting 2

[dobe1]

Using a legacy database, I have pulled tables into sql server. There is a date field and a time field, both are decimal(8). I would like to somehow concatenate them into a datetime column. I can convert the date field easily enough.

The time field has been recorded in military time, not decimal time, so 1750 would be 5:50 pm and not 5:30 pm.
The recorded format for the date field is ex: 20050130
for jan 30, 2005, and the format for the time would be ex: 1750 or 810. I can easily enough add a zero to the three digit times in a character field for the sake of conversion.
ex: 810 becomes 0810.


Any help would be appreciate.

Dobe

 

[dbomrrsm]

one way

select dateadd(mi,left('1750',2)*60+ right('1750',2),cast('20050130' as datetime))

will need to do the adding of the leading zero where only 3 chars long.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[dobe1]

Thanks, would this work the same with an update, and field names in place of the literals?

Also, in the first parentheses, the is an "mi". I assume this is minute.

Thanks again,
I will give this a try now.

Dobe

 

[dbomrrsm]

yep field names instead of literalls

what dateadd does is take the time portion you want - in this case minutes (worked out from breaking down your time ie the hours times 60 plus the minutes) then adds these minutes to the datetime which is a flat time

select cast('20050130' as datetime)

dont forget though to do your adding of the leading zaro otherwise 830 will become 83*60 minutes + 30 when you want 8*60 + 30.

do the query as a select first to check the reults are correct before the update

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[dobe1]

Thank again dbomrrsm. I have just tested it, and it works great. I have spent hours trying to figure this one out.

Dobe

 

[dbomrrsm]

Glad to help.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]