Timestamp Issues

[Anonymous]

I am having real trouble retrieving data from an Oracle database based on a date parameter. The date I am trying to interrogate is actually a timestamp in the format DD-MM-YYYY hh:mm:ss, but no matter what I try I am either getting

• all the data, unfiltered
• errors pertaining to "inconsistent datatypes: expected DATE got NUMBER"
• errors pertaining to "literal does not match format string"

This is the current version of the code, and this returns all the data, unfiltered:

let vLatestQVDDate = Date#('01/01/2012','DD/YY/YYYY');

  MovementDates:

          LOAD MOVEMENT_DT;

          sql Select DISTINCT FX.MOVEMENT_DT

          FROM

            CDW.FCT_XCS_BREAKDOWN_TR_DY FX

      WHERE FX.MOVEMENT_DT > '$(vLatestQVDDate)'

      ORDER BY FX.MOVEMENT_DT;

Can anyone offer any advice? We've used the {ts....} function around the filter value in other apps, but I can't seem to get it to work here.

Thanks

Andrew

[MayilVahanan]

HI

I think u used wrongly..

let vLatestQVDDate = Date#('01/01/2012','DD/YY/YYYY');

Use like this

let vLatestQVDDate = Date#('01/01/2012','DD/MM/YYYY');

And verified the format for MOVEMENT_DT and vLatestQVDDate.

Hope that helps

[#3]

Hi,

try this:

let vLatestQVDDate = Date#('01/01/2012','DD/MM/YYYY');

  MovementDates:

          LOAD MOVEMENT_DT;

          sql Select DISTINCT FX.MOVEMENT_DT

          FROM

            CDW.FCT_XCS_BREAKDOWN_TR_DY FX

      WHERE CONVERT(VARCHAR(10),FX.MOVEMENT_DT,110) > '$(vLatestQVDDate)'

      ORDER BY CONVERT(VARCHAR(10),FX.MOVEMENT_DT,110);

Regards

[Anonymous]

Ah, sorry. That was just a typo on my part. Correctly assigning the value for vLatestQVDDate using Date# makes no difference, unfortunately.

This doesn't work either:

let vLatestQVDDate = Timestamp#('01/01/2012 00:00:00','DD-MM-YYYY hh:mm:ss');

(I get the "literal does not match format string" error)

[Anonymous]

Sorry, I'm getting a "missing expression" with that I'm afraid.

[#6]

means when u load ur script ?

Regards

[#7]

ahh, thats an Oracle database....i thot it was SQL Server

use

TO_DATE (TO_CHAR (FX.MOVEMENT_DT, 'DD/MM/YYYY'), 'DD/MM/YYYY')

instead of the Convert.

Regards

[Anonymous]

Thanks again, but sadly I'm still getting every available MOVEMENT_DT back with the data retrieval.