Page 1 of 1
Date conversion
Posted: Fri Dec 01, 2006 12:01 am
by syam
Hi All,
Please help how to convert string to date in PX
Ex. 20061122 to 11/22/2006
thanks in advance
Posted: Fri Dec 01, 2006 12:03 am
by chulett
Posted: Fri Dec 01, 2006 12:08 am
by ajith
Posted: Fri Dec 01, 2006 1:15 pm
by chandra
U can do with concatinate and substring !
Posted: Fri Dec 01, 2006 3:01 pm
by ray.wurlod
Wrong! Substring and concatenate does not produce a date, it produces a string.
Stop thinking like a server job developer!
Posted: Mon Dec 04, 2006 1:07 pm
by chandra
Thanks !!!
Posted: Tue Dec 05, 2006 9:41 am
by ds_developer
I don't know about Date fields, but I know you can substring and concatenate a Timestamp field for Oracle. I assume it is a string as Ray says, then the Oracle Enterprise stage converts it as needed.
John
Posted: Tue Dec 05, 2006 10:12 am
by ArndW
John - this is a PX subject - so the typical server conversions won't work and you should explicitly convert datatypes and this is what Ray has pointed out.
Posted: Tue Dec 05, 2006 11:44 am
by ds_developer
You might have to explain this further - I understand this is a PX topic. This is what I am doing in a derivation:
Code: Select all
IF ValidDateOpen = '0' THEN SetNull() ELSE ValidDateOpen[1,4] : '-' : ValidDateOpen[5,2] : '-' : ValidDateOpen[7,2] : ' 00:00:00'
ValidDateOpen is a VarChar stage variable that verifies that the date is valid. The derivation is in a Timestamp field. Maybe the underlying data type is string as Ray points out, but the Oracle Enterprise stage eventually gets it to a date.
It may be this isn't the best way to do it, but it is working. Could it be a version issue? I'm on 7.5.1.A Enterprise Edition.
John