Date Validations in Datastage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 73
- Joined: Thu Mar 08, 2007 9:50 pm
- Location: Bangalore
Hi,
I have used the
But there is still one scenario that isn't covered. Once we had a job failure because the input date had the year part outside the SQL Server acceptable range of 1753 and 9999.
Is anyone aware of any inbuilt transform in Datastage to check whether the date is valid by checking if the date is btw 1 & 31, the month is btw 1 and 12 and the year is btw 1753 and 9999?
Thanks in advance.
I have used the
to check validity...If OConv(IConv(<inputDate>, <conversion code>), <conversion code>) <> inputDate then "invalid" else "valid".
But there is still one scenario that isn't covered. Once we had a job failure because the input date had the year part outside the SQL Server acceptable range of 1753 and 9999.
Is anyone aware of any inbuilt transform in Datastage to check whether the date is valid by checking if the date is btw 1 & 31, the month is btw 1 and 12 and the year is btw 1753 and 9999?
Thanks in advance.
-
- Participant
- Posts: 73
- Joined: Thu Mar 08, 2007 9:50 pm
- Location: Bangalore
Yes - they are already handled by the IConv function when handling dates, as is the year - just not with the restricted range you posted. Add another bit of logic to say that the date is invalid if the year portion of the date is outside that range as well as what you are already doing.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 73
- Joined: Thu Mar 08, 2007 9:50 pm
- Location: Bangalore
I was checking the HELP on Iconv function:
Remarks
The Status function returns the result of the conversion as follows:
0 The conversion was successful.
1 The string was invalid. An empty string was returned, unless string was a null value when null was returned.
2 The conversion was invalid.
3 Successful conversion but the input data may be invalid, for example, a nonexistent date, such as 31 September.
But when I tried using the date 31/09/07, it came in the destination as Ist October 2007
Remarks
The Status function returns the result of the conversion as follows:
0 The conversion was successful.
1 The string was invalid. An empty string was returned, unless string was a null value when null was returned.
2 The conversion was invalid.
3 Successful conversion but the input data may be invalid, for example, a nonexistent date, such as 31 September.
But when I tried using the date 31/09/07, it came in the destination as Ist October 2007
Rea Pullan Antony
As I mentioned before in this thread, you would need to write a routine in order to use the STATUS() value for an ICONV() or OCONV() call.
Which format did you use on "31/09/07"?
Which format did you use on "31/09/07"?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- 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
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
This is what you should be using if you are checking for dd/mm/yy format date:
Oconv(Iconv(Input.Date,"D/DMY[2,2,2]"), "D/DMY[2,2,2]") = Input.Date
Check out the date format difference/ [/code]
Oconv(Iconv(Input.Date,"D/DMY[2,2,2]"), "D/DMY[2,2,2]") = Input.Date
Check out the date format difference/ [/code]
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>
using the format
Go into the Manager and create a new server routine. Put in one line into the program and feed it your input string. You will see that your format is wrong, it should be "D2/DMY[2,2,2]". Once you have this function running so that it produces an integer number (the internal format), add another line to your test routine. and see what you get.
on "31/09/07" cannot result in an output of "Ist October 2007".OCONV(ICONV(DSLink9.Field003,"D2-DMY[2,2,2]"),"D2-YMD[4,2,2]"):" 00:00:00.000"
Go into the Manager and create a new server routine. Put in one line into the program
Code: Select all
Ans=ICONV(Arg1,"D2-YMD[4,2,2]")
Code: Select all
Ans = OCNOV(Ans,"D/DMY[2,2,2]")
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 73
- Joined: Thu Mar 08, 2007 9:50 pm
- Location: Bangalore
ArndW wrote:...You will see that your format is wrong, it should be "D2/DMY[2,2,2]"...
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 73
- Joined: Thu Mar 08, 2007 9:50 pm
- Location: Bangalore
Can someone advice me on how Iconv function can be used to accept different date formats at a time. What i mean to say is that, my job accepts 1-Jun-06 and 21/1/07 and 2/7/2007. But it stumps upon encountering 2006-jun-04.
Is it possible to use Iconv function to handle different types of date formats simultaneously?
Is it possible to use Iconv function to handle different types of date formats simultaneously?
When you specify a simple "D4" conversion code the routine will make a best guess as to the format, but it is very poor practice as the computer has no idea if "2007-04-08" means August 4 or April 8; thus you will certainly get invalid conversions.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>