date/time formats

Question

date/time formats

db2l

Ten Centuries

Points: 1386
More actions
November 16, 2003 at 10:28 pm

#160968

Hi,
I hope someone can help me with an SQL query!
I have 2 columns in a table; one column contains date data, the other
column contains time data, however they are both defined as decimal(12).
The format of the data is something like this:
DATE(DECIMAL 12) TIME(DECIMAL 12)
1031117 734
1031117 21601
1031117 161510
The date format is 1=year 2000+, 03=year, 11=month, 17=day.
The time format is the time after midnight, eg, 734 would be 7 minutes, 34 seconds,
21601 would be 2 hours, 16 minutes, 01 seconds, etc.
My question is, if I add a column to the table that I define as datetime,
can I use SQL to combine these two columns and insert into the new column?
tks a lot.

Viewing 5 posts - 1 through 4 (of 4 total)

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

philcart SSC-Forever Points: 47796 More actions · Answer 1

Weird format for the date. Why not just have 2003 for the year Anyhow, here's my bash at it.



SET NOCOUNT ON



CREATE TABLE #Date (

decDate decimal(12)

, decTime decimal(12)

)



INSERT INTO #Date VALUES (1031117, 734)

INSERT INTO #Date VALUES (1031117, 21601)

INSERT INTO #Date VALUES (1031117, 161510)



SELECT

CAST(

SUBSTRING(CAST(#Date.decDate as varchar(10)), 6, 2)

+ CASE SUBSTRING(CAST(#Date.decDate as varchar(10)), 4, 2)

WHEN '01' THEN ' Jan '

WHEN '02' THEN ' Feb '

WHEN '03' THEN ' Mar '

WHEN '04' THEN ' Apr '

WHEN '05' THEN ' May '

WHEN '06' THEN ' Jun '

WHEN '07' THEN ' Jul '

WHEN '08' THEN ' Aug '

WHEN '09' THEN ' Sep '

WHEN '10' THEN ' Oct '

WHEN '11' THEN ' Nov '

WHEN '12' THEN ' Dec '

END

+ CASE 

WHEN LEFT(CAST(#Date.decDate as varchar(10)), 1) = '1'

THEN '20' + SUBSTRING(CAST(#Date.decDate as varchar(10)), 2, 2)

END

+ CASE 

WHEN #Date.decTime > 0 THEN

CASE LEN(#Date.decTime)

WHEN 1 THEN ' 00:00:0' + CAST(#Date.decTime as varchar(10))

WHEN 2 THEN ' 00:00:' + CAST(#Date.decTime as varchar(10))

WHEN 3 THEN ' 00:0' + LEFT(CAST(#Date.decTime as varchar(10)), 1) 

+ ':' + RIGHT(CAST(#Date.decTime as varchar(10)), 2)

WHEN 4 THEN ' 00:' + LEFT(CAST(#Date.decTime as varchar(10)), 2) 

+ ':' + RIGHT(CAST(#Date.decTime as varchar(10)), 2)

WHEN 5 THEN ' 0' + LEFT(CAST(#Date.decTime as varchar(10)), 1) 

+ ':' + SUBSTRING(CAST(#Date.decTime as varchar(10)), 2, 2)

+ ':' + RIGHT(CAST(#Date.decTime as varchar(10)), 2)

WHEN 6 THEN ' ' + LEFT(CAST(#Date.decTime as varchar(10)), 2) 

+ ':' + SUBSTRING(CAST(#Date.decTime as varchar(10)), 3, 2)

+ ':' + RIGHT(CAST(#Date.decTime as varchar(10)), 2)

END

ELSE ' 00:00'

END

as datetime)



FROM #Date



DROP TABLE #Date

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

Jonathan SSC-Insane Points: 20427 More actions · Answer 2



SELECT CAST(CAST(LEFT([Date],3) + 1900 AS char(4)) + '-' + STUFF(RIGHT([Date],4),3,0,'-') + ' ' +

STUFF(REPLACE(REPLACE(STR([Time]/100,8,2),'.',':'),' ','0'),4,0,':') AS datetime)

FROM StrangeDates

--Jonathan

noeld SSC Guru Points: 96590 More actions · Answer 3

SELECT Cast(Cast([decDate]+ 19000000 as char(8)) + ' ' +

Stuff(Stuff(Right('000000'+ Cast([decTime]as varchar(6)),6),3,0,':'),6,0,':') as datetime)

from FunnyDates

* Noel

Jonathan SSC-Insane Points: 20427 More actions · Answer 4



SELECT CAST(LEFT([Date]+19000000,8) AS datetime)+STUFF(STUFF(REPLACE(STR([Time],6),' ',0),5,0,':'),3,0,':')

FROM StrangeDates

--Jonathan