Dates In Decimal Format

Database
Enthusiast

Dates In Decimal Format

How Do i Convert decimal dates to date format.

the decimal is not in format YYMMDD...

its in DDMMYY, it could be 105007 which is 1/5/2007
or it could be 112207 which is 11/22/07.

Please Help!
14 REPLIES
Enthusiast

Re: Dates In Decimal Format

One way is to convert them to char and then to date....

select cast(cast(112207 as char(6)) as date format 'mmddyy')
Enthusiast

Re: Dates In Decimal Format

Does not work...

select cast(cast(112207 as char(6)) as date format 'mmddyy')

return 1907-11-22 which is wrong, should be 11/22/2007

and

select cast(cast(12207 as char(6)) as date format 'mmddyy')

returns error... "Invalid Date"

Any other ideas ;-)
Enthusiast

Re: Dates In Decimal Format

This is what I got in bteq:

BTEQ -- Enter your DBC/SQL request or BTEQ command:
select cast(cast(112207 as char(6)) as date format 'mmddyy');
select cast(cast(112207 as char(6)) as date format 'mmddyy');

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

112207
------
112207
Enthusiast

Re: Dates In Decimal Format

I'm using Teradata SQL Assistant and

for select cast(cast(112207 as char(6)) as date format 'mmddyy');

I get 1907-11-22.
Enthusiast

Re: Dates In Decimal Format

Ok. I get 11/22/2007 for the same query in my SQL Assistant.
Enthusiast

Re: Dates In Decimal Format

Try this
cast((100+(dd-dd/100*100))*10000+dd/10000+(dd/100-(dd/10000)*100)*100 as date)

where dd is your decimal date
Enthusiast

Re: Dates In Decimal Format

This was the closest I could get :o

SELECT MYDATE

,((CASE WHEN (MYDATE / 10000 (NAMED DAYOFDATE1) ) MOD 10 (NAMED DAYOFDATE2) = 0 THEN DAYOFDATE1/10 ELSE DAYOFDATE1 END (FORMAT '99') ) ||
(CASE WHEN (MYDATE MOD 10000 / 100 (NAMED MOOFDATE1) ) MOD 10 (NAMED MOOFDATE2) = 0 THEN MOOFDATE1/10 ELSE MOOFDATE1 END (FORMAT '99')) || '20' ||
(MYDATE MOD 100 (FORMAT '99'))) (DATE, FORMAT 'MMDDYYYY') (FORMAT 'YYYY-MM-DD') AS MYDTE

FROM
(
SELECT 105007(INTEGER)
)TMP(MYDATE)

;

Enthusiast

Re: Dates In Decimal Format

This works perfect...

but If I have this query with a bunch of fields, wont this be very expensive to run?

Enthusiast

Re: Dates In Decimal Format

Cancel my last post...

it does not work

SELECT MYDATE

,((CASE WHEN (MYDATE / 10000 (NAMED DAYOFDATE1) ) MOD 10 (NAMED DAYOFDATE2) = 0 THEN DAYOFDATE1/10 ELSE DAYOFDATE1 END (FORMAT '99') ) ||
(CASE WHEN (MYDATE MOD 10000 / 100 (NAMED MOOFDATE1) ) MOD 10 (NAMED MOOFDATE2) = 0 THEN MOOFDATE1/10 ELSE MOOFDATE1 END (FORMAT '99')) || '20' ||
(MYDATE MOD 100 (FORMAT '99'))) (DATE, FORMAT 'MMDDYYYY') (FORMAT 'YYYY-MM-DD') AS MYDTE

FROM
(
SELECT 100507(INTEGER)
)TMP(MYDATE)

;

Returns 1/5/2007 should return 10/05/2007 (Date is MMDDYY... decimal format)