Hello,
I have a date reading from a parameter file. Its format is CCYY-MM-DD.
What I wish to achieve is to subtract 1 from the parameter date? Mind that I am using Datastage OS/390.
I tried the following command but it did not return the expected result. The result returns me with the original date.
Below is my command used in the TRANSFORMATION stage: CAST(((CAST((CAST(JobParam.P_SNPSHT_DT AS DATE)) AS INT)) - 1) AS DATE)
The 3rd CAST is to convert the parameter date (in char format) to a date format. The 2nd CAST is to convert the date into integer. The 1st CAST is to convert the integer (after subtracting 1) to a date.
Cheers,
Paul.
How to subtract 1 day from a parameter date in the transform
Hello Paul,
I havent experimented with any date arithmetic in DS 390 yet, so I can only give you some ideas to try out (I dont currently have access to DS 390 to try these out myself).
1) Your nested casting has a significant logic flaw. If you subtract 1 from an integer that ends in 01, you get an integer that ends in 00 and cant be converted to a date (i.e. your logic wont work for a parameter date of the first day of a month).
2) Have you tryed to cast your parameter as a date and then see if DS 390 handles the date math (e.g. simply a transfrom like PARAMETER-DATE - 1)? I wouldnt be surprised if DS 390 doesnt yet support date arithmetic, but its worth a try to verify.
3) You could treat your parameter first as 3 separate numeric inputs and apply the date subtraction logic with IF-THEN-ELSE logic to handle first of month and first of year situations. Then apply the cast to date to the end result.
4) You could write an external routine to do date arithmetic.
If you provide more detail about how youre using your date parameter, I might be able to come up with an additional suggestion or two.
Regards,
Mike
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Wednesday, October 31, 2001 10:37 PM
To: datastage-users@oliver.com
Subject: How to subtract 1 day from a parameter date in the transform stage for OS/390
Hello,
I have a date reading from a parameter file. Its format is CCYY-MM-DD.
What I wish to achieve is to subtract 1 from the parameter date? Mind that I am using Datastage OS/390.
I tried the following command but it did not return the expected result. The result returns me with the original date.
Below is my command used in the TRANSFORMATION stage: CAST(((CAST((CAST(JobParam.P_SNPSHT_DT AS DATE)) AS INT)) - 1) AS DATE)
The 3rd CAST is to convert the parameter date (in char format) to a date format. The 2nd CAST is to convert the date into integer. The 1st CAST is to convert the integer (after subtracting 1) to a date.
Cheers,
Paul.
I havent experimented with any date arithmetic in DS 390 yet, so I can only give you some ideas to try out (I dont currently have access to DS 390 to try these out myself).
1) Your nested casting has a significant logic flaw. If you subtract 1 from an integer that ends in 01, you get an integer that ends in 00 and cant be converted to a date (i.e. your logic wont work for a parameter date of the first day of a month).
2) Have you tryed to cast your parameter as a date and then see if DS 390 handles the date math (e.g. simply a transfrom like PARAMETER-DATE - 1)? I wouldnt be surprised if DS 390 doesnt yet support date arithmetic, but its worth a try to verify.
3) You could treat your parameter first as 3 separate numeric inputs and apply the date subtraction logic with IF-THEN-ELSE logic to handle first of month and first of year situations. Then apply the cast to date to the end result.
4) You could write an external routine to do date arithmetic.
If you provide more detail about how youre using your date parameter, I might be able to come up with an additional suggestion or two.
Regards,
Mike
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Wednesday, October 31, 2001 10:37 PM
To: datastage-users@oliver.com
Subject: How to subtract 1 day from a parameter date in the transform stage for OS/390
Hello,
I have a date reading from a parameter file. Its format is CCYY-MM-DD.
What I wish to achieve is to subtract 1 from the parameter date? Mind that I am using Datastage OS/390.
I tried the following command but it did not return the expected result. The result returns me with the original date.
Below is my command used in the TRANSFORMATION stage: CAST(((CAST((CAST(JobParam.P_SNPSHT_DT AS DATE)) AS INT)) - 1) AS DATE)
The 3rd CAST is to convert the parameter date (in char format) to a date format. The 2nd CAST is to convert the date into integer. The 1st CAST is to convert the integer (after subtracting 1) to a date.
Cheers,
Paul.