date/time formats

Question

Post reply

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: 47794 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

Jonathan

SSC-Insane

Points: 20427

November 17, 2003 at 6:00 am

#482189



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

noeld

SSC Guru

Points: 96590

November 17, 2003 at 8:09 am

#482228

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

Jonathan

SSC-Insane

Points: 20427

November 17, 2003 at 3:20 pm

#482319



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

FROM StrangeDates

--Jonathan