Date conversion

syam · Post by **syam** » Fri Dec 01, 2006 12:01 am

Hi All,

Please help how to convert string to date in PX

Ex. 20061122 to 11/22/2006

thanks in advance

chulett · Post by **chulett** » Fri Dec 01, 2006 12:03 am

Oddly enough, the StringToDate function.

ajith · Post by **ajith** » Fri Dec 01, 2006 12:08 am

chulett wrote:Oddly enough, the StringToDate function.

That hits the bull's eye

chandra · Post by **chandra** » Fri Dec 01, 2006 1:15 pm

U can do with concatinate and substring !

ray.wurlod · Post by **ray.wurlod** » Fri Dec 01, 2006 3:01 pm

Wrong! Substring and concatenate does not produce a date, it produces a string.

Stop thinking like a server job developer!

chandra · Post by **chandra** » Mon Dec 04, 2006 1:07 pm

Thanks !!!

ds_developer · Post by **ds_developer** » Tue Dec 05, 2006 9:41 am

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

ArndW · Post by **ArndW** » Tue Dec 05, 2006 10:12 am

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.

ds_developer · Post by **ds_developer** » Tue Dec 05, 2006 11:44 am

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