Converting Date format from YYYY-MM-DD to YYYYMMDD

Christina · Post by **Christina** » Thu Nov 29, 2007 5:37 am

Hi,

My source is a Date datatype in YYYY-MM-DD format.
How can i convert it to Date(8) in YYYYMMDD format?

siva4joy · Post by **siva4joy** » Thu Nov 29, 2007 5:52 am

you can use convert function to remove '-'.
syntax for the function is Convert('-','', Input_Date_Column)
and you can use substring function ,and syntax is
Input_Date_Column[1,4]:Input_Date_Column[6,2]:Input_Date_Column[8,2]

Christina · Post by **Christina** » Thu Nov 29, 2007 6:16 am

Will this give the output as date dataype with length 8?

siva4joy · Post by **siva4joy** » Thu Nov 29, 2007 8:01 am

no the output will be string

Christina · Post by **Christina** » Thu Nov 29, 2007 8:13 am

I want the output to be in Date with length 8.

Please provide a solution....

chulett · Post by **chulett** » Thu Nov 29, 2007 8:40 am

StringToDate() with the appropriate format mask. Forget about the 'to YYYYMMDD' part, a date is a date.

DSguru2B · Post by **DSguru2B** » Thu Nov 29, 2007 8:49 am

No conversion is required. Just change the date output format in the extended properties of the field.

Christina · Post by **Christina** » Thu Nov 29, 2007 9:39 am

How to change the extended properties of the field?
I am new to DataStage PX.

I tried the following, but got some error.

StringToDate(Convert('-','',Date_Column),"%yyyy%mm%dd")

Error: Failure during execution of operator logic
Conversion error calling conversion routine date_from_string
data may have been lost

ZDC · Post by **ZDC** » Thu Nov 29, 2007 10:46 am

Hi there,
As DSGuru suggested ...
Just go to
format tab>type defaults and then go for the date ,
change the format string to %yyyy%mm%dd in the output tab,it will work for sequential file as i have done this in past.No need to worry about datatype.

Cheers,
ZDC

neena · Post by **neena** » Thu Nov 29, 2007 10:56 am

As shiva4joy said i tested converting yyyy-mm-dd to yyyymmdd using sequential file and in transformer i used convertion string , convert('-','', inputlink_columnname)and changed length properties of datecolumn in column tab in target to 8 and it worked fine. But i did in server job.

prasson_ibm · Post by **prasson_ibm** » Thu Nov 29, 2007 11:59 am

Christina wrote:How to change the extended properties of the field?
I am new to DataStage PX.

I tried the following, but got some error.

StringToDate(Convert('-','',Date_Column),"%yyyy%mm%dd")

Error: Failure during execution of operator logic
Conversion error calling conversion routine date_from_string
data may have been lost

hi, it can be simpaly done by Oconv(Iconv(INPUT_LINK,"D-YMD"),"YMD")............

DSguru2B · Post by **DSguru2B** » Thu Nov 29, 2007 1:14 pm

Guys, server solutions will not work in px. Follow ZDC's suggestion. He basically explained what I said earlier.

Christina · Post by **Christina** » Thu Nov 29, 2007 10:35 pm

Thanks a lot guys...

The solution given by DSguru2B and ZDC works...