Date conversion in Server Jobs of Transformer

[antojj]

How to type cast date in datastage server jobs?

Prob Desc :
I am using sql server as my source connection and extracting date from one of a table.
The date format in sql server is "1/18/2006 11:14:37 AM". I have typecasted in the query of the ODBC stage as "convert(varchar(10),INVOICE_DATE, 101) as INVOICEDATE". After type cast,I am getting the date value as 01/18/2006.

I need to filter the records which satisfies the condition.

Code: Select all

ie INVOICEDATE {01/16/2006} >= the system date {current date}

[/color]

For this I used type cast in the constraint of transformer.

Code: Select all

Code :
OCONV(ICONV(InDate.INVOICE_DATE,"DE"),"D/E") >= OCONV(ICONV(Lookup_Date.Field001,"DE"),"D/E")

But, nothing works.
If I removed the constraint in the transformer , i am getting all the records from the table.

Can someone correct me if am making wrong here ?

NOTE:
a) 01/16/2006 ---> MM/DD/YYYY
b) OCONV(ICONV ---> Functions used for the conversion of date in internal format of datastage.

[ArndW]

Antonio,

could you use the mask "D4/MDY" instead of "DE" for the ICONV; I'm not at a PC with DS now so I can't look up the BASIC manual, but it is best to make sure that the conversion is as explicit as possible for the ICONV.

[raj_konig]

Hi antonio,

your SQl date format is MM/DD/YYYY HH:MI:SS. (Is wht stated by U)
you are trying to convert that format to DD/MM/YYYY using ICONV & OConv.

but the data stage default format is YYYY-MM-DD HH24: MI: SS

so can you compre these 2 dates?

reforamt your date and then compare. it shud work

rajesh