Date comparison

nelc · Post by **nelc** » Tue Jun 17, 2003 9:30 pm

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(

vmcburney · Post by **vmcburney** » Tue Jun 17, 2003 11:35 pm

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

ray.wurlod · Post by **ray.wurlod** » Wed Jun 18, 2003 12:26 am

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

nelc · Post by **nelc** » Wed Jun 18, 2003 2:13 am

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. [:)]

ray.wurlod · Post by **ray.wurlod** » Wed Jun 18, 2003 5:50 pm

Can I ask you to start a new thread on this (it's not really date comparison), and be a bit more specific about what the new design looks like?
It certainly can be done, but the answer will vary depending on exactly what you want the target to look like. In particular, what are the key columns?

nelc · Post by **nelc** » Wed Jun 18, 2003 8:03 pm

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. [:)]

ray.wurlod · Post by **ray.wurlod** » Thu Jun 19, 2003 5:34 am

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.