Trying to do a date comparison here. Applied the following code but works the other way round:
if (Iconv(Input.InputDate, "D/MDY[2,2,4]") >
Iconv("07/01/2001", "D/MDY[2,2,4]")) then "A" else "B"
The records with input date later than 1st July 2001 still gets loaded with "B". If i check input date to be smaller(
Date comparison
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
I suggest you put this code into a routine where you pass in Input.InputDate as an argument and get either A or B in return. This will let you trap errors and test your code in the routine editor.
The iconv function can return several status messages:
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.
The Server Developers handbook has some sample code for the Status function:
InputDate = Iconv(Arg1, "D/MDY[2,2,4]")
ConvStatus = Status()
Begin Case
Case ConvStatus = 0
* ...conversion succeeded
if (InputDate > Iconv("07/01/2001", "D/MDY[2,2,4]")) then Ans = "A" else Ans = "B"
Case ConvStatus = 1
* ...conversion failed - ExtDate not parsable as a date
...
I have tested your code in a routine and it works okay for VALID dates, therefore I think your problem is in the data. If for example you pass a null or zero length string in you will get B back in return, if you reverse the > to a < you will get A back. So perhaps your input field is blank.
Vincent McBurney
Data Integration Services
www.intramatix.com
The iconv function can return several status messages:
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.
The Server Developers handbook has some sample code for the Status function:
InputDate = Iconv(Arg1, "D/MDY[2,2,4]")
ConvStatus = Status()
Begin Case
Case ConvStatus = 0
* ...conversion succeeded
if (InputDate > Iconv("07/01/2001", "D/MDY[2,2,4]")) then Ans = "A" else Ans = "B"
Case ConvStatus = 1
* ...conversion failed - ExtDate not parsable as a date
...
I have tested your code in a routine and it works okay for VALID dates, therefore I think your problem is in the data. If for example you pass a null or zero length string in you will get B back in return, if you reverse the > to a < you will get A back. So perhaps your input field is blank.
Vincent McBurney
Data Integration Services
www.intramatix.com
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Good advice from Vincent. Using a Routine (transform function) also gives you the ability to check for NULL separately if you wish.
TheDate = Arg1
If IsNull(TheDate)
Then
Ans = @NULL
End
Else
TestDate = Iconv("07/01/2001", "DMDY")
CompDate = Iconv(TheDate, "DMDY")
* you don't need to specify separator on Iconv
If Status() = 0
Then
* we have a valid date
Ans = (If CompDate > TestDate Then "A" Else "B")
End
Else
Msg = "Input is not a valid date."
Call DSTransformError(Msg, "MyFunc")
Ans = "?"
End
End
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
TheDate = Arg1
If IsNull(TheDate)
Then
Ans = @NULL
End
Else
TestDate = Iconv("07/01/2001", "DMDY")
CompDate = Iconv(TheDate, "DMDY")
* you don't need to specify separator on Iconv
If Status() = 0
Then
* we have a valid date
Ans = (If CompDate > TestDate Then "A" Else "B")
End
Else
Msg = "Input is not a valid date."
Call DSTransformError(Msg, "MyFunc")
Ans = "?"
End
End
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
Hi both,
Thanks for the info. It is definitely because of the data like what Vincent mentioned. I am converting from a Access DB and the type is Date/Time, think that's why Iconv was not successful. In this case, how can I convert it into a string format?? I checked out the built-in routines such as DATE.TAG, couldn't figure out which one to use.
Another issue to ask is that of inserting multiple records into one source. Scenario is as follows:
Old data stores product type columns in boolean form. Column names are like 'HAIR','FACE'....
Whichever type the product can be classified under, that column will be marked with "-1", and a product and be for both 'HAIR' and 'FACE'. However, new database design stores it in a separate table with one record for each product type.
How can I check all the product type columns and insert each as a separate record into the new table?
Really appreciate any help rendered.
Thanks in advance again. [:)]
Thanks for the info. It is definitely because of the data like what Vincent mentioned. I am converting from a Access DB and the type is Date/Time, think that's why Iconv was not successful. In this case, how can I convert it into a string format?? I checked out the built-in routines such as DATE.TAG, couldn't figure out which one to use.
Another issue to ask is that of inserting multiple records into one source. Scenario is as follows:
Old data stores product type columns in boolean form. Column names are like 'HAIR','FACE'....
Whichever type the product can be classified under, that column will be marked with "-1", and a product and be for both 'HAIR' and 'FACE'. However, new database design stores it in a separate table with one record for each product type.
How can I check all the product type columns and insert each as a separate record into the new table?
Really appreciate any help rendered.
Thanks in advance again. [:)]
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hi Ray,
My apologies. Will post the second qn on a new thread. Can you kindly advise regarding date comparison query first?
With regards to the routine you advised me to write, I am converting from a Access DB and the type is Date/Time, think that's why Iconv was not successful.
In this case, how can I convert it into a string format??
Thanks again. [:)]
My apologies. Will post the second qn on a new thread. Can you kindly advise regarding date comparison query first?
With regards to the routine you advised me to write, I am converting from a Access DB and the type is Date/Time, think that's why Iconv was not successful.
In this case, how can I convert it into a string format??
Thanks again. [:)]
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Everything is a string in DataStage.
However, if the string you've returned from Access is in the form of a timestamp, you need to remove just the date portion, the leftmost 10 characters (which are in YYYY-MM-DD format).
For example, to get this date in DataStage internal format:
CompDate = Iconv(Left(InLink.TheTimeStamp, 10), "DYMD")
If you're still having problems use the Debugger or stage tracing (or an extra output link to a text file) to see just what DataStage is "seeing" from Access.
However, if the string you've returned from Access is in the form of a timestamp, you need to remove just the date portion, the leftmost 10 characters (which are in YYYY-MM-DD format).
For example, to get this date in DataStage internal format:
CompDate = Iconv(Left(InLink.TheTimeStamp, 10), "DYMD")
If you're still having problems use the Debugger or stage tracing (or an extra output link to a text file) to see just what DataStage is "seeing" from Access.