All Forums Database
leokaplin1 11 posts Joined 05/07
17 May 2007
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!

nithyanandam 65 posts Joined 10/04
17 May 2007

One way is to convert them to char and then to date....select cast(cast(112207 as char(6)) as date format 'mmddyy')

leokaplin1 11 posts Joined 05/07
17 May 2007

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/2007andselect cast(cast(12207 as char(6)) as date format 'mmddyy')returns error... "Invalid Date"Any other ideas ;-)

nithyanandam 65 posts Joined 10/04
17 May 2007

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

leokaplin1 11 posts Joined 05/07
17 May 2007

I'm using Teradata SQL Assistant andfor select cast(cast(112207 as char(6)) as date format 'mmddyy');I get 1907-11-22.

nithyanandam 65 posts Joined 10/04
17 May 2007

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

Mitrich 5 posts Joined 05/07
17 May 2007

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

joedsilva 505 posts Joined 07/05
18 May 2007

This was the closest I could get :oSELECT 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 MYDTEFROM(SELECT 105007(INTEGER))TMP(MYDATE);

leokaplin1 11 posts Joined 05/07
18 May 2007

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

leokaplin1 11 posts Joined 05/07
18 May 2007

Cancel my last post... it does not workSELECT 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 MYDTEFROM(SELECT 100507(INTEGER))TMP(MYDATE);Returns 1/5/2007 should return 10/05/2007 (Date is MMDDYY... decimal format)

leokaplin1 11 posts Joined 05/07
18 May 2007

Looks like this works good.cast((100+(dd-dd/100*100))*10000+dd/10000+(dd/100-(dd/10000)*100)*100 as date)

joedsilva 505 posts Joined 07/05
18 May 2007

I guess I ran into trouble because in your original post you mentioned " could be 105007 which is 1/5/2007 "I thought the format to be funny :-) How about this one ?SELECT ADD_MONTHS(('112207' (DATE, FORMAT 'MMDDYY')), 1200) (FORMAT 'YYYY-MM-DD') ;

leokaplin1 11 posts Joined 05/07
18 May 2007

every solution posted here has not worked....SELECT cast((100+(53107-53107/100*100))*10000+53107/10000+(53107/100-(53107/10000)*100)*100 as date) returns INVALID DATE.Is everyone stumped?

Fred 1096 posts Joined 08/04
18 May 2007

Integer arithmetic is cheaper than string manipulation or FORMAT conversion:SELECT CAST((1000000+(IntDate MOD 100)*10000+IntDate/100) AS DATE)FROM (SELECT CAST(112207 AS INTEGER)) x(IntDate);Or if you need to allow for "Century Break" where two-digit years above some pre-set value (say 90) are 19yy while those less than that value are 20yy, replace the 1000000 with an appropriate CASE expression:CASE WHEN IntDate MOD 100 >= 90 THEN 0 ELSE 1000000 END

rgs 106 posts Joined 02/07
18 May 2007

I don't see how one can do this without more information. You are essentially saying you have two date formats if I read your post correctly. One decimal form is DDMMYYY and the other from is DDMMYYIn your example 105007 is of the from DDMMYYY (1/5/2007).What if the date is 101007 how does one tell whether this is 1/1/2007 or 10/10/07? You need to figure how to tell the difference. Once you know that then you can use a CASE to pick from two conversion algorithms of which some examples were already provided.-- Rolf

You must sign in to leave a comment.