Wierd behavior of StringToDate conversion depending

[highpoint]

Wierd behavior of StringToDate conversion depending on location in If statement.

Code: Select all

If IsNotNull(ORCL.ORDER_DATE)    and svOrderDate<>''   and StringToDate(svOrderDate,"%yyyy%mm%dd") < '2010-03-01'
Then 
	'ABC'

Else 'DEF'

In this case the job throws warning as follows:

Copy_of_Transformer_77,0: Conversion error calling conversion routine date_from_string data may have been lost

Code: Select all

If IsNotNull(ORCL.ORDER_DATE)  and StringToDate(svOrderDate,"%yyyy%mm%dd") < '2004-03-01' and svOrderDate<>''
Then 
'ABC'

Else 'DEF

In this case job fails.

Copy_of_Transformer_77,0: Conversion error calling conversion routine date_from_string data may have been lost
Copy_of_Transformer_77,0: Failure during execution of operator logic.



Please help me understand the same if statement with same conditions but in different sequence produces warning or failure.

[Kryt0n]

First question would have to be why are you converting a string to a date and then comparing it to a string?

Does svOrderDate include a time portion? Or maybe including dashes between fields?

[highpoint]

First question would have to be why are you converting a string to a date and then comparing it to a string?

Does svOrderDate include a time portion? Or maybe including dashes between fields?

In this am i not comparing Date with a date?

StringToDate(svOrderDate,"%yyyy%mm%dd") < '2010-03-01'


'2010-03-01' --> Is this not date?

[pandeesh]

'2010-03-01' --> Is this not date?

It's not a date.if you want,you can convert this also using StringtoDate().

[Kryt0n]

That would be a string representation of a date.

Really all you need to do is svOrderDate < '20100301' to achieve the same aim (where I'm assuming svOrderDate is a string).

Not sure if DataStage would attempt an implicit conversion of '2010-03-01', the warning/error message implies it is trying to do such. Either compare the strings or put a stringtodate around the constant and see if the warning remains

[qt_ky]

I mentioned on your other post that's very similar to this one, effectively to separate your check if the date string is empty, from the StringToDate() function(s) that operate on the potentially empty string. Try that also.

[highpoint]

If svOrderDate is char[8]. '20100301' is a char.
Then do i have to convert them to decimal or integer before comparing them svOrderDate < '20100301'


And say svShipDate is char[8] and svCancelDate is Char[8].
And if i have to compare svShipDate and svCancelDate then do i have to convert them first into decimal or integer before comparing them.

Say calculations like:
svShipDate > svCancelDate
or
svCancelDate < svShipDate


Appreciate your reply.

[Kryt0n]

Nope, as long as the dates (strings) are in the YYYYMMDD format, your result will be the same whether integer or character (character comparisons work at the character set interpretation of the character - the charcode - which is a number). The primary difference ultimately is a computer will be able to compare an integer quicker than it can compare the character representation of that integer. I've never tested if it is quicker to compare two integer strings or to convert to integer first and then compare so feel free to try both and see which works best (assuming you have a big enough set to work against)

[qt_ky]

String comparison will work in your case because of the exact format YYYYMMDD. If the format were DDMMYYYY or MMDDYYYY then string comparison would certainly not guarantee correct results.

If you need to load dates into columns where the data type is date, or if you want to take advantage of date functions like DaysSinceFromDate or WeekdayFromDate, etc., then you would first have to convert from the string data type to a date data type.