Date Validations in Datastage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 73
- Joined: Thu Mar 08, 2007 9:50 pm
- Location: Bangalore
Date Validations in Datastage
Hi,
I'm loading data from a sequential file into a table. There is one Date column which has to be validated before loading.
I need to check if the value present in that column in the .csv file is a valid date.
Are anyone aware of any inbuilt validations present in datastage? Or else, can someone please advice me on how to validate this in the Transformer stage ( so that the invalid rows can be dropped).
I'm loading data from a sequential file into a table. There is one Date column which has to be validated before loading.
I need to check if the value present in that column in the .csv file is a valid date.
Are anyone aware of any inbuilt validations present in datastage? Or else, can someone please advice me on how to validate this in the Transformer stage ( so that the invalid rows can be dropped).
Rea Pullan Antony
There is no built in function to check the validity of dates in Server jobs (parallel job have the IsValid function).
But you can use the Oconv/Iconv functions for validating the incoming date.
One way to go about this would be -
This first converts the incoming date to the DataStage internal format, and then reconverts it to the external format. If the incoming date was valid then the reconverted external date value would equal the incoming date value.
Aneesh
But you can use the Oconv/Iconv functions for validating the incoming date.
One way to go about this would be -
Code: Select all
If OConv(IConv(<inputDate>, <conversion code>), <conversion code>) <> inputDate then "invalid" else "valid".
Aneesh
I need to check if the value present in that column in the .csv file is a valid date.
Are anyone aware of any inbuilt validations present in datastage?
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
Add this in your transformer constraint - Oconv(Oconv(Iconv(Input.date,"DYMD"),"DYMD"),"MCN")= Input.date - This filters out invalid dates (will check for the length 8 also)
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
Unfortunately the ICONV() and OCONV() functions will return the original string, so the result of the 2 functions on an invalid string will be the original string - just as if it were a valid date. The correct way to do this is to use a ICONV() function and check the value of STATUS(); but as that cannot be done without resorting to a user-written routine the simple method of a single ICONV() and comparing that to a string should be sufficient, although it will return a valid conversion for some dates that might not be valid, such as in leap years.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
I think below checking should work
Code:
Oconv(Oconv(Iconv(Input.Date,"D-YMD[4,2,2]"), "D-YMD[4,2,2]"),"MCN") = Input.Date
For example:
Quote:
For date --> 20070704
Iconv(Input.DT1,"D-YMD[4,2,2]") --> 14430
Oconv(Iconv(Input.DT1,"D-YMD[4,2,2]"), "D-YMD[4,2,2]") --> 2007-07-04
Oconv(Oconv(Iconv(Input.DT1,"D-YMD[4,2,2]"), "D-YMD[4,2,2]"),"MCN") --> 20070704
For below set of input records:
NULL
20070704
2000704
2007070
20070000
20077704
20070744
00070704
00000704
Output will be only :
20070704
00070704
Which is right I believe.
.
Code:
Oconv(Oconv(Iconv(Input.Date,"D-YMD[4,2,2]"), "D-YMD[4,2,2]"),"MCN") = Input.Date
For example:
Quote:
For date --> 20070704
Iconv(Input.DT1,"D-YMD[4,2,2]") --> 14430
Oconv(Iconv(Input.DT1,"D-YMD[4,2,2]"), "D-YMD[4,2,2]") --> 2007-07-04
Oconv(Oconv(Iconv(Input.DT1,"D-YMD[4,2,2]"), "D-YMD[4,2,2]"),"MCN") --> 20070704
For below set of input records:
NULL
20070704
2000704
2007070
20070000
20077704
20070744
00070704
00000704
Output will be only :
20070704
00070704
Which is right I believe.
.
Last edited by JoshGeorge on Wed Jul 04, 2007 5:00 am, edited 2 times in total.
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
Joshy - the output is right, but the overhead of 3 I/Oconv call is to much, just a simple "Iconv(Input.Date,"D-YMD[4,2,2]")=Input.Date" is sufficient.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
ArndW - Iconv(Input.Date,"D-YMD[4,2,2]") will be Julian date and won't be equal to Input.Date. You won't get any output records.
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
If ICONV() of a string is equal to the string then you have an invalid date.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
ICONV() of a string will not be equal to the orginal string. Did you mean to say something else?
ArndW wrote:If ICONV() of a string is equal to the string then you have an invalid date. ...
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
ICONV('THIS IS A BAD DATE','D4/E') is equal to 'THIS IS A BAD DATE'. Therefore just 1 ICONV() call is necessary to determine that you have an invalid date format, it is not necessary to issue 3 calls to do this.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
Inline you can see:
For example:
For example:
I have tested it with just Iconv() and have posted the result in my previous post. No output records.For date --> 20070704
Iconv(Input.Date,"D-YMD[4,2,2]") --> 14430
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
-
- Premium Member
- Posts: 50
- Joined: Tue Jan 02, 2007 1:40 am
Code: Select all
If Iconv(Input.Date,"D-YMD[4,2,2]") = Input.Date Then "Invalid Date" Else "Valid Date"
-
- Participant
- Posts: 73
- Joined: Thu Mar 08, 2007 9:50 pm
- Location: Bangalore
-
- Participant
- Posts: 73
- Joined: Thu Mar 08, 2007 9:50 pm
- Location: Bangalore