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
how to convert mm/dd/yyyy hh:mm:ss into CCYYMMDD
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 224
- Joined: Tue Sep 24, 2002 7:32 am
- Location: Denver, CO USA
thanks, but still one question
[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
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
[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
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
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 137
- Joined: Sun Jan 07, 2007 11:17 pm
- Location: VISAKHAPATNAM
Re: thanks, but still one question
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
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:hi johnds_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
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
Shivaranjani
-
- Participant
- Posts: 137
- Joined: Sun Jan 07, 2007 11:17 pm
- Location: VISAKHAPATNAM
Re: thanks, but still one question
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]
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
Shivaranjani