Sys Date Comparison

hemant · Post by **hemant** » Thu Jun 24, 2004 1:59 am

Hi!

I want to have 4 months records in my file.
So i am comparing the RecordDate which i am passing as a parameter with sys date but it seems not working .

Is any other option to do the same.

-Hemant

ray.wurlod · Post by **ray.wurlod** » Thu Jun 24, 2004 2:27 am

You probably have a mismatched format. The only time when strings containing dates can be reliably compared is when they comply with ISO 8601 (for example YYYY-MM-DD).

What is the source (text file, database table)?
What type is the parameter?

Date type parameters are converted into DataStage internal format, so to perform the comparison you will need to convert your source dates into internal format too, using the Iconv function. For example, if your source dates are in MM/DD/YY format, use Iconv(TheDate, "DMDY"). If your source dates are in YYYY-MM-DD format, use Iconv(TheDate, "DYMD").

hemant · Post by **hemant** » Thu Jun 24, 2004 2:50 am

When i do Iconv(RecordDate,"D-YMD[4,2,2]") its showing me a internal no that is been generated but if i do same for I.e Iconv(LkUp.LAST_INCDR_DATE,"D-YMD[4,2,2]")it doesn't shows me any no.

what i want in result :

If Iconv(RecordDate,"D-YMD[4,2,2]") - Iconv(LkUp.LAST_INCDR_DATE,"D-YMD[4,2,2]")=120
then LAST_INCDR_DATE (show me the date in my file)
else @null(those > 4 months )
---------------------------------------------------
imp:i have timestamp in my LAST_INCDR_DATE but in that case also
(LkUp.LAST_INCDR_DATE[1,10],"D-YMD[4,2,2]") doesn't work.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 24, 2004 3:02 am

What happens if you try

Code: Select all

If RecordDate  - Iconv(LkUp.LAST_INCDR_DATE,"D-YMD[4,2,2]") <= 120 
then LAST_INCDR_DATE (show me the date in my file) 
else @null(those > 4 months )

hemant · Post by **hemant** » Thu Jun 24, 2004 3:10 am

Ray,Its showing me blank colums.

hemant

hemant · Post by **hemant** » Thu Jun 24, 2004 3:34 am

Ray
This should give me some number but again its showing me NULL
Iconv(RecordDate,"D-YMD[4,2,2]") - Iconv(LkUp.LAST_INCDR_DATE[1,10],"D-YMD[4,2,2]")

-Hemant

parashu · Post by **parashu** » Thu Jun 24, 2004 6:41 am

hemant wrote:Ray
This should give me some number but again its showing me NULL
Iconv(RecordDate,"D-YMD[4,2,2]") - Iconv(LkUp.LAST_INCDR_DATE[1,10],"D-YMD[4,2,2]")

-Hemant

Hemant,

Check out if the Date field is null in some rows, if so try using the condition Not Null and convert the date using Iconv to a number. You might get the result.

Parashu

ketfos · Post by **ketfos** » Thu Jun 24, 2004 9:38 am

Hemant,
What values you have in the following fields for one record in table/file

RecordDate
LAST_INCDR_DATE

Then check the result of the following functions

Iconv(RecordDate,"D-YMD[4,2,2]")

Iconv(LAST_INCDR_DATE[1,10],"D-YMD[4,2,2]")

This debugging might help you.

Ketfos