cyymmdd to timestamp

[srikie]

Hi,
My vsam file has date of this

Code: Select all

10  CDB6ED-LAST-CHANGE-DT.                                
            15  CDB6ED-LAST-CHANGE-C            PIC X(01).          
            15  CDB6ED-LAST-CHANGE-MM           PIC X(02).        
            15  CDB6ED-LAST-CHANGE-DD           PIC X(02).       
            15  CDB6ED-LAST-CHANGE-YY           PIC X(02).    

which needs to cast to a timestamp attribute of db2 table.
When I do it using

Code: Select all

 CAST(LASTCHGDATE AS TIMESTAMP(6))

where LASTCHGDATE is a char stage variable which has the value of
'ccyy-mm-dd' based on the value of CDB6ED-LAST-CHANGE-C i.e. if
CDB6ED-LAST-CHANGE-C=0 then cc='19' else cc='20'.
but the problem is in the resultant flat file I only get all zeroes regardless of the original date(source date)
like 0000-00-00-00.00.00.000000.

Can some one help with this one.

Thanks
srikie

[Sainath.Srinivasan]

How do you declare and assign all fields in the source into your temporary variable?

[srikie]

How do you declare and assign all fields in the source into your temporary variable?

I dint get that part. But here is the stage variable derivation for lastchgdate:

Code: Select all

if   DSLink3.CDB6ED_LAST_CHANGE_C  = '0' then '19' ||   DSLink3.CDB6ED_LAST_CHANGE_YY   ||  '-' ||  DSLink3.CDB6ED_LAST_CHANGE_MM   ||  '-' ||  DSLink3.CDB6ED_LAST_CHANGE_DD   ||  '-00.00.00.000000'  else '20' ||   DSLink3.CDB6ED_LAST_CHANGE_YY   ||   '-' ||  DSLink3.CDB6ED_LAST_CHANGE_MM   ||  '-' ||  DSLink3.CDB6ED_LAST_CHANGE_DD   ||  '-00.00.00.000000' END

[/code]

[Sainath.Srinivasan]

2 things looking at it.

First, try using the CAST AS TIMESTAMP than TIMESTAMP(6).

Second, you are concatenating the DD and HH part with a '-' in middle but must be a ' ' in the middle.

i.e. it is defined as 'YYYY-MM-DD-HH:MI:SS.ssssss' but I believe it must be 'YYYY-MM-DD HH:MI:SS.ssssss'.

Also try removing the milli-seconds.

[srikie]

Is removing milli-seconds necessary. I tried replacing '-' with ' '.
Thanks
srikie

[Mike]

srike,

I think Sainath.Srinivasan has probably nailed your problem. The CAST function expects a DataStage format timestamp (i.e. without the dash between the date and time portions).

Mike

[srikie]

srike,

I think Sainath.Srinivasan has probably nailed your problem. The CAST function expects a DataStage format timestamp (i.e. without the dash between the date and time portions).

Mike

But the delimiters could be anythg right? I mean 'hh.mm.ss.ssssss' or should it only be 'hh:mm:ss.sssssss'

[srikie]

It still is giving the same zeros.
I dont know where look for? I mean atleast it should give the date if not the time.

[Mike]

DataStage standard timestamp format is "CCYY-MM-DD HH:MM:SS.NNNNNN". I assume the time delimiters probably matter.

Mike

[Mike]

srikie,

I don't have access to a mainframe to try some options, but this is what I would assume would be the way that CAST AS TIMESTAMP would work:

1) If character, I would expect that you would have to provide a 26-character string in standard DataStage timestamp format.
2) If date, I would expect the CAST to add 00:00:00.000000.

You could try something like:

Code: Select all

CAST (CAST (SomeDate AS DATE) AS TIMESTAMP)

Where SomeDate is a CHAR(10) with CCYY-MM-DD format.

Mike

[srikie]

DataStage standard timestamp format is "CCYY-MM-DD HH:MM:SS.NNNNNN". I assume the time delimiters probably matter.

Mike

No, when I get the current timestamp its displaying the timestamp in
'CCYY-MM-DD-HH.MM.SS.NNNNNN' format not in "CCYY-MM-DD HH:MM:SS.NNNNNN" , thats why I chose that way.
But even with this format its not working.

[srikie]

srikie,

I don't have access to a mainframe to try some options, but this is what I would assume would be the way that CAST AS TIMESTAMP would work:

1) If character, I would expect that you would have to provide a 26-character string in standard DataStage timestamp format.
2) If date, I would expect the CAST to add 00:00:00.000000.

You could try something like:

Code: Select all

CAST (CAST (SomeDate AS DATE) AS TIMESTAMP)

Where SomeDate is a CHAR(10) with CCYY-MM-DD format.

Mike

In MVS edition , You cant cast date as timestamp. That gives an error.

[Mike]

That's the DB2 format for a timestamp. Look at the CAST function documentation in your mainframe job developer's guide.

Mike

[srikie]

That's the DB2 format for a timestamp. Look at the CAST function documentation in your mainframe job developer's guide.

Mike

Yeah we are actually loading to db2load ready flat file.

[Mike]

What kind of error do you get. I was able to generate code to do the double cast. The generated code looked like it was moving zero to the various time components. I am not able to execute the job, but it looks like the generated code should run compile and run just fine.

Mike