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
Sys Date Comparison
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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").
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").
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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 )
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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