Conversion to DATETIME

Question

Post reply

Conversion to DATETIME

Green Armchair

Hall of Fame

Points: 3561
More actions
February 6, 2003 at 2:39 am

#79518

Hi
I have a decimal (20,6) field representing a date and time down to 6 decimal places of a second. Its come from DB2 where that accuracy exists. For example:
19991217120902.000000
represents 1999-12-17 12:09:02.000000
I want to have a computed column that displays this field as sql server datetime (ignoring the fractions of a second). Does anybody know a better way than:
convert(datetime,left(eftv_dt,4)+ '-' + substring(convert(varchar,eftv_dt),5,2) + '-' + substring(convert(varchar,eftv_dt),7,2) + ' ' + substring(convert(varchar,eftv_dt),9,2) + ':' + substring(convert(varchar,eftv_dt),11,2) + ':' + substring(convert(varchar,eftv_dt),13,2))
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

DavidT SSCommitted Points: 1727 More actions · Answer 1

Different way of doing the same thing. Slightly shorter code:

select stuff(stuff(stuff(stuff(stuff(stuff(@eftv_dt,5,0,'-'),8,0,'-'),11,0,' '),14,0,':'),17,0,':'),20,7,'')

paulhumphris Hall of Fame Points: 3496 More actions · Answer 2

This is not quite as short as DavidT's but it is an alternative way of approaching the issue.

select dateadd(hh, (convert(bigint, @eftv_dt) % 1000000) / 10000, dateadd(mi, (convert(bigint, @eftv_dt) % 10000) / 100, dateadd(ss, convert(bigint, @eftv_dt) % 100, convert(datetime, left(@eftv_dt, 8)))))

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 3

If shorter is better then try this:

declare @eftv_dt decimal(20,6)

set @eftv_dt= 19991217120902.000000

select cast(left(stuff(stuff(stuff(@eftv_dt,9,0,' '),12,0,':'),15,0,':'),17) as datetime)

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

paulhumphris Hall of Fame Points: 3496 More actions · Answer 4

Dont you just love it when someone takes two other peoples ideas and makes a better one from combining them.

Green Armchair Hall of Fame Points: 3561 More actions · Answer 5

Thanks for the replies.

I was looking for a more elegant solution than the one I had (and, of course, the one that performs the best!).

I will try them out

Thanks again

DavidT SSCommitted Points: 1727 More actions · Answer 6

Could create a function that uses this code, and refer to the function in the table's computed column. At least this keeps the code out of the table ddl code

Nicholas Wang Default port Points: 1463 More actions · Answer 7

There was an interesting article on "How to Search for Date and Time Values" written by Bryan Syverson.

http://www.sqlservercentral.com/columnists/bsyverson/sqldatetime.asp

May be that can help.