Cast as timestamp

General
Enthusiast

Cast as timestamp

We are receiving the following data from a source system in a varchar format

Sep 11 2011  5:17:05:000AM

Oct  9 2011  4:52:00:000AM

We can't simply use cast(...as timestamp format 'MMMBDDBYYYYBHH:MI:SS.S(F)T')  because in some cases the DD causes a problem because the day is only one digit and other times the HH causes a problem because there is only one digit.  I wrote the following SQL to parse out the entire date, and it works well.  However, when we run this against a large table, we get a "3710:  Insufficient memory..." error.  Any suggestion would be much appreciated!

select month_name||

               case when characters(day_of_month)=1

                           then ' 0'||day_of_month||' '

                           else ' '||day_of_month||' '

                end ||

                year_num||

               case when characters(hour_num)=1

                           then ' 0'||hour_num||':'

                           else ' '||hour_num||':'

                end  ||

               case when characters(minute_num)=1

                           then '0'||minute_num||':'

                           else minute_num||':'

                end   ||

               case when characters(sec_num)=1

                           then '0'||sec_num||'.'

                           else sec_num||'.'

                end   ||

               case when characters(sub_sec_num)=1

                           then '00'||sub_sec_num

                           when characters(sub_sec_num)=2

                           then '0'||sub_sec_num

                           else sub_sec_num

                end||

                tz as full_timestamp,

                cast(trim(full_timestamp) as timestamp format 'MMMBDDBYYYYBHH:MI:SS.S(F)T')                                                                

from (               

select dw_rec_txn_dtm as ts,

                substr(ts,1,position(' ' in ts)-1) as month_name,

                trim(substr(ts,position(' ' in ts)+1,characters(ts))) as day_year_time,

                trim(substr(day_year_time,1,position(' ' in day_year_time)-1)) as day_of_month,

                trim(substr(day_year_time,position(' ' in day_year_time)+1,characters(day_year_time))) as year_time,

                trim(substr(year_time,1,position(' ' in year_time)-1)) as year_num,

                trim(substr(year_time,position(' ' in year_time)+1,characters(year_time))) as hour_minute,

                trim(substr(hour_minute,1,position(':' in hour_minute)-1)) as hour_num,

                trim(substr(hour_minute,position(':' in hour_minute)+1,characters(hour_minute))) as minute_second,

                trim(substr(minute_second,1,position(':' in minute_second)-1)) as minute_num,

                trim(substr(minute_second,position(':' in minute_second)+1,characters(minute_second))) as second_num,

                trim(substr(second_num,1,position(':' in second_num)-1)) as sec_num,

                trim(substr(second_num,position(':' in second_num)+1,characters(second_num))) as sub_second_num,

                trim(substr(sub_second_num,1,position(case when position('A' in sub_second_num)>0 then 'A' else 'P' END in sub_second_num)-1)) as sub_sec_num,

                trim(substr(sub_second_num,position(case when position('A' in sub_second_num)>0 then 'A' else 'P' END in sub_second_num),characters(sub_second_num))) as tz from

                DQ_ACQ.CL_7056_WKL_MHD_FIN) as date_parse

2 REPLIES
Senior Apprentice

Re: Cast as timestamp

As there are only a few different combinations, i used to do it with LIKE:

CASE
WHEN ts LIKE '____ _______ %'
THEN SUBSTRING(ts FROM 1 FOR 4) || '0' || SUBSTRING(ts FROM 6 FOR 7) || '0' || SUBSTRING(ts FROM 14 FOR 7)
WHEN ts LIKE '____ %'
THEN SUBSTRING(ts FROM 1 FOR 4) || '0' || SUBSTRING(ts FROM 6 FOR 15)
WHEN ts LIKE '____________ %'
THEN SUBSTRING(ts FROM 1 FOR 12) || '0' || SUBSTRING(ts FROM 14 FOR 7)
ELSE SUBSTRING(ts FROM 1 FOR 20)
END || '.' || SUBSTRING(ts FROM 22) (TIMESTAMP, FORMAT 'MMMBDDBYYYYBHH:MI:SS.S(3)T')

This works when there's always a blank instead of a zero (as your data suggested), otherwise you have to adjust the LIKEs and SUBSTRINGs.

Dieter

Enthusiast

Re: Cast as timestamp

That did the trick.  Thanks!