Query with date field

[Abhilasha]

Hi,

In the Informix client database stage, I am writing query as given below:

SELECT
col1, col2, col3, col4, batch_dt
FROM tab_nam WHERE batch_dt<Today-13 units month
and batch_dt >= #start_date#

But it is not selecting correct set of records. Job runs well and data is also selected, but " batch_dt >= #start_date# " condition is not getting checked.

If I try to give as
" batch_dt >= '#start_date#' " ,
it gives me error.

Please let me know the appropraite way to compare date fields with actual value.

Thanks,
Abhilasha

[DSguru2B]

Pass the date with surrounding quotes as a parameter. Dont apply the quotes in the query. See if that helps.
If that does not work then try to run the same query in your favourite sql tool (outside of datastage), once the query works, you will know exactly what you query should look like inside datastage to work.

[sachin1]

please check for the value of parameter #start_date#

[bucks]

You cant directly pass the string value for date comparision. please use date type conversion and then compare it with the batch_dt.

[Abhilasha]

Thanks...
Its working when i gave diff date formate.

[bucks]

Please mark it as resolved and what did you change in the query?