Page 1 of 2
cyymmdd to timestamp
Posted: Fri Mar 04, 2005 9:14 am
by 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
Posted: Fri Mar 04, 2005 9:35 am
by Sainath.Srinivasan
How do you declare and assign all fields in the source into your temporary variable?
Posted: Fri Mar 04, 2005 10:28 am
by srikie
Sainath.Srinivasan wrote: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]
Posted: Fri Mar 04, 2005 11:03 am
by 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.
Posted: Fri Mar 04, 2005 11:09 am
by srikie
Is removing milli-seconds necessary. I tried replacing '-' with ' '.
Thanks
srikie
Posted: Fri Mar 04, 2005 11:13 am
by 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
Posted: Fri Mar 04, 2005 11:16 am
by srikie
Mike wrote: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'
Posted: Fri Mar 04, 2005 11:24 am
by 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.
![Sad :(](./images/smilies/icon_sad.gif)
Posted: Fri Mar 04, 2005 11:25 am
by Mike
DataStage standard timestamp format is "CCYY-MM-DD HH:MM:SS.NNNNNN". I assume the time delimiters probably matter.
Mike
Posted: Fri Mar 04, 2005 11:26 am
by 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
Posted: Fri Mar 04, 2005 11:27 am
by srikie
Mike wrote: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.
Posted: Fri Mar 04, 2005 11:28 am
by srikie
Mike wrote: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.
Posted: Fri Mar 04, 2005 11:29 am
by Mike
That's the DB2 format for a timestamp. Look at the CAST function documentation in your mainframe job developer's guide.
Mike
Posted: Fri Mar 04, 2005 11:31 am
by srikie
Mike wrote: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.
Posted: Fri Mar 04, 2005 11:33 am
by 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