Page 1 of 1
how to convert mm/dd/yyyy hh:mm:ss into CCYYMMDD
Posted: Tue Aug 07, 2007 2:22 pm
by 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
Posted: Tue Aug 07, 2007 2:41 pm
by 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
thanks, but still one question
Posted: Tue Aug 07, 2007 3:15 pm
by 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
thanks, but still one question
Posted: Tue Aug 07, 2007 3:23 pm
by 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
Posted: Tue Aug 07, 2007 3:43 pm
by 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.
Re: thanks, but still one question
Posted: Wed Aug 08, 2007 6:30 am
by 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
pattemk wrote:ds_developer wrote: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
Re: thanks, but still one question
Posted: Wed Aug 08, 2007 6:34 am
by 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]
G SHIVARANJANI wrote: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