Warning for conversion from String to date

[devidotcom]

Hi All,

I am checking if the incoming VarChar(10) field is a valid Date or not.

Its a not nullable column. I could have spaces and '0000-00-00' I found some useful post in the which reduced the following warning but I yet receive some of them.

Warning received is
APT_CombinedOperatorController,1: Conversion error calling conversion routine date_from_string data may have been lost [transform/tfmop_functions.C:132]

The input data is of the format yyyy-mm-dd

Below is the check I am doing in the stage variable which holds a value of 0 or 1.

svStageVar=
If Trim(Lnk_Read_PHONE.PHONE_EFF_DATE)='' OR
Trim(Lnk_Read_PHONE.PHONE_EFF_DATE)='0000-00-00'
Then 0 Else If IsValid("Date",StringToDate(Trim(Lnk_Read_PHONE.PHONE_EFF_DATE,'%yyyy-%mm-%dd'))) Then 0 Else 1

the stage variable svStageVar has a value of 0 or 1.

Thanks in advance

[ArndW]

There is something wrong with your derivation. The TRIM() function should only have one argument in your example, you have misplaced a close-parenthesis.

You could also add code to check if "DCOUNT(Lnk_Read_PHONE.PHONE_EFF_DATE,'-')#2 THEN 0"

[devidotcom]

Hi Changed the code and had the trim function with proper closing brackets I yet get the warning....

[sendmkpk]

Hi AndrW,

Why do u say
"DCOUNT(Lnk_Read_PHONE.PHONE_EFF_DATE,'-')#2 THEN 0"

It should be
"DCOUNT(Lnk_Read_PHONE.PHONE_EFF_DATE,'-')<>3 THEN 0"

Correct me if I am wrong

[ArndW]

sendmkpk - yes, you are wrong. How many "-" characters does a correctly formatted yyyy-mm-dd string have?

[devidotcom]

I checked the output of
DCOUNT(Lnk_Read_PHONE.PHONE_EFF_DATE,'-') it says 3 for a correct date.

What does #2 mean here

[ArndW]

sendmkpk/devidotcom - both of you are correct, I was wrong. Even though I had written DCOUNT I was thinking in terms of the COUNT function. COUNT("yyyy-mm-dd","-") is 2 and DCOUNT("yyyy-mm-dd","-") is 3. The "#2" part means "<>2". It makes sense to use the COUNT function in this place instead of DCOUNT, though; so the preferred check is

Code: Select all

COUNT(Lnk_Read_PHONE.PHONE_EFF_DATE,'-')#2 THEN 0

[devidotcom]

Thanks AndrW.
I used the below condition
svCustEffDate=
If Trim(Lnk_Trn_Columns.CUID_ID_EFF_DATE)='' OR Trim(Lnk_Trn_Columns.CUID_ID_EFF_DATE)='0000-00-00' OR Trim(Lnk_Trn_Columns.CUID_ID_EFF_DATE)='1911-11-11' Or Dcount(Lnk_Trn_Columns.CUID_ID_EFF_DATE,'-')<>3 Then '0000-00-00' Else StringToDate(Lnk_Trn_Columns.CUID_ID_EFF_DATE)

svCustCloseDate=
If Trim(Lnk_Trn_Columns.CUST_CLOSE_DATE)='' OR Trim(Lnk_Trn_Columns.CUST_CLOSE_DATE)='0000-00-00' Or Dcount(Lnk_Trn_Columns.CUST_CLOSE_DATE,'-')<>3 Then '0000-00-00' Else StringToDate(Lnk_Trn_Columns.CUST_CLOSE_DATE)

If (svCustEffDate > svCustCloseDate) Then 1 Else 0

I am later comparing the dates...
I yet get the warning

[ArndW]

This is a case for using Stage Variables. You don't need to TRIM() each input column 3 times. Do it once in a Stage Variable and use that value. A valid date would have a LEN of 10 and have 2 '-' characters in addition. What about using the IsValid() function - have you tried that?

[devidotcom]

Yes i did as your suggested it did not work

:(

[ArndW]

"did not work" doesn't help much in analyzing the problem. What happened? Did the machine crash? Was the output just 0000-00-00? Are you 100% certain that this date column is generated the error messages? Do you know which value of Date is causing this? You can narrow it down to the row - using IsValid and a condition in your transform.

[devidotcom]

Got the solution.
If you check the value I was specifing if the date was invalid was '0000-00-00' I changed that to '9999-12-31' It worked. Thanks you AndrW.