how to convert mm/dd/yyyy hh:mm:ss into CCYYMMDD

[pattemk]

hi everyone

can anyone help me how to convert mm/dd/yyyy hh:mm:ss to CCYYMMDD

INPUT DATATYPE--> VARCHAR(24)
OUTPUT DATATYPE-->VARCHAR(8)

PLZ HELP

THANK YOU

[ds_developer]

You could try:

DATE[7,4]:DATE[1,2]:DATE[4,2]

The DATE I am referring to is the name of the date field coming into the transformer.

John

[pattemk]

[quote="ds_developer"]You could try:

DATE[7,4]:DATE[1,2]:DATE[4,2]

The DATE I am referring to is the name of the date field coming into the transformer.

John[/quote]

hi john

thanks for replyin but in my input dates are like 5/4/1998, 11/5/2001,5/21/1996,11/31/1994 and your formula works fine for 11/31/1994 how to do with other kinds

[pattemk]

[quote="ds_developer"]You could try:

DATE[7,4]:DATE[1,2]:DATE[4,2]

The DATE I am referring to is the name of the date field coming into the transformer.

John[/quote]

hi john

thanks for replyin but in my input dates are like 5/4/1998, 11/5/2001,5/21/1996,11/31/1994 and your formula works fine for 11/31/1994 how to do with other kinds

[ArndW]

Use the FIELD function to parse out your 3 string elements, i.e. FIELD(In.Date,'/',3) to get the year portion. Then format those strings to the correct length since you are getting variable length columns and finally concatenate them to the resulting format.

[G SHIVARANJANI]

Use this way

Str("0", 2-Len(trim(SRC_DATE)['/',1,1])) : trim(SRC_DATE)['/',1,1]:Str("0", 2-Len(trim(SRC_DATE)['/',2,1])) : trim(SRC_DATE)['/',2,1]:Str("0", 2-Len(trim(SRC_DATE)['/',3,1])) : trim(SRC_DATE)['/',3,1]


Here SRC_DATE is the date field from source

cheers

You could try:

DATE[7,4]:DATE[1,2]:DATE[4,2]

The DATE I am referring to is the name of the date field coming into the transformer.

John

hi john

thanks for replyin but in my input dates are like 5/4/1998, 11/5/2001,5/21/1996,11/31/1994 and your formula works fine for 11/31/1994 how to do with other kinds

[G SHIVARANJANI]

Sorry u need CCYYMMDD

So Use this Way
trim(SRC_DATE)['/',3,1]:Str("0", 2-Len(trim(SRC_DATE)['/',2,1])) : trim(SRC_DATE)['/',2,1]:Str("0", 2-Len(trim(SRC_DATE)['/',1,1])) : trim(SRC_DATE)['/',1,1]

Use this way

Str("0", 2-Len(trim(SRC_DATE)['/',1,1])) : trim(SRC_DATE)['/',1,1]:Str("0", 2-Len(trim(SRC_DATE)['/',2,1])) : trim(SRC_DATE)['/',2,1]:Str("0", 2-Len(trim(SRC_DATE)['/',3,1])) : trim(SRC_DATE)['/',3,1]


Here SRC_DATE is the date field from source