Date format Error

[Das]

Hi,

Hi ,
I have one issue with the date format.Please give suggestions.

I need to convert the date format coming in mm/dd/yyyy to dd/mm/yyyy to update a data fileld in an informix table where field datatype is date

Thanks,

[ArndW]

Code: Select all

OCONV(ICONV(In.DateStringColumn,'D4MDY'),'D4/DMY[2,2,4]')

Note that this does not check for valid dates. Or, as a string solution,

Code: Select all

In.DateStringColumn[4,3]:In.DateStringColumn[1,3]:In.DateStringColumn[7,4]

The latter is faster as it just manipulates string portions.

[Das]

Code: Select all

OCONV(ICONV(In.DateStringColumn,'D4MDY'),'D4/DMY[2,2,4]')

Note that this does not check for valid dates. Or, as a string solution,

Code: Select all

In.DateStringColumn	 ...[/quote]

Thanks.

In my case source and target are same.i am fetching some data and trying to update the same table with some logic.IN table the format is [b]dd/mm/yyyy[/b]..When i run this job in director a messege is coming as follows [b]Date could not be converted into month/day/year format[/b]

[ArndW]

I just re-read your post and saw that you are using INFORMIX. You need to use the internal date format, so just the ICONV() part to convert your date.

[Das]

I just re-read your post and saw that you are using INFORMIX. You need to use the internal date format, so just the ICONV() part to convert your date. ...

When i am viewing data from input stage->view data option it is showing integer values of dates.Can i use like following
ICONV(reg_link.reg_date,'D4MDY')

Please suggest..

[DSguru2B]

Do a select and load it to a flat file, just a few records. See if the date comes up as integers (internal format). If yes then no conversion is required, pass it as it is.

[Das]

Do a select and load it to a flat file, just a few records. See if the date comes up as integers (internal format). If yes then no conversion is required, pass it as it is.

Thank you

I have tried the same ,The date is coming in internal integer format.When i try to update the date field with this value,It is not getting updated.and giving the following message in director
ABC...Dt_update: [Informix][Informix ODBC Driver][Informix]Date could not be converted to month/day/year format

[ray.wurlod]

If the date really is coming in internal integer format (how did you verify this fact?), then you need to apply a conversion function. Use Oconv() if it's DataStage internal format, or one of the SDK functions if it's based on a different base date (for example 1970 or 1900).