How do I Date formatting

[Former Member]

I have tried this in the query transform and I get a Null value.

to_date('01/14/2011', 'yyyymmdd')

and this as a test

to_date(sysdate(), 'yyyymmdd')

but I still get 2011.08.05 09:34:47

How do I convert a date in SBO??????

[Former Member]

It comes in as a varchar in the template table and through the transform and then into the destination table as a varchar

[Former Member]

The column is a varchar 255, but I should be able to take a varchar convert to date format then back to varchar to satisfy the requirements in this tool. This is a template for Assets that was provided by SAP.

[Former Member]

Data Services can convert date, datetime and timestamp into a string representation and back again. I know because I've done it many times on many projects. The issue will be something minor in your code.

From the top - what datatype is your source column? If it's a date, the to_char can be used to create a string representation of that date. If it's a varchar, then it can be converted into a date using to_date, but we need to know exactly how the date is stored as a string in your character column.

Date to char, char to date, Data Services can do it all.

[Former Member]

One thing that can catch some people out is Data Services implicit data type conversion. When you validate your code do you get any warnings?

[Former Member]

Nope

[Former Member]

The issue is this value

'05/01/1988' goes in and comes out exactly the same.

It should come out as YYYYMMDD

[Former Member]

if

P2CAA02_FIXEDASSET_POSTINGINFO_MAP."*ASSET_CAPITALIZATION_DATE"

is a date column holding today, then

to_char(P2CAA02_FIXEDASSET_POSTINGINFO_MAP."*ASSET_CAPITALIZATION_DATE", 'yyyymmdd')

would return the date as string - '20110805'.

I would review your datatypes.

[Former Member]

It is in a Query Transform is assigned to a column varchar 255

[Former Member]

Sounds correct. Where do you see the "incorrect" result then? A table or file? Either way, all datatypes must be character based for this data after the to_char(). Run the job in debug and check the value in the column ditrectly after the conversion. What do you see?

[Former Member]

This is my code.

the example value of

P2CAA02_FIXEDASSET_POSTINGINFO_MAP."*ASSET_CAPITALIZATION_DATE" = 03/01/2011

to_char(to_date(P2CAA02_FIXEDASSET_POSTINGINFO_MAP."*ASSET_CAPITALIZATION_DATE", 'YYYYMMDD'),'YYYYMMDD')

I also tried.

to_char(P2CAA02_FIXEDASSET_POSTINGINFO_MAP."*ASSET_CAPITALIZATION_DATE", 'YYYYMMDD')

[Former Member]

What are you assigning the result to, a variable or a column? What is the datatype (it must be varchar).

[Former Member]

Hi,

If your source field is a varchar and the date is '03/01/2011' as in your example, then the format string in your to_date function:

to_date(P2CAA02_FIXEDASSET_POSTINGINFO_MAP."*ASSET_CAPITALIZATION_DATE", 'YYYYMMDD'),

is not correct because it needs to define the format of the input varchar date, so it knows how to convert to a date datatype, so it needs to be :

to_date(P2CAA02_FIXEDASSET_POSTINGINFO_MAP."*ASSET_CAPITALIZATION_DATE", 'MM/DD/YYYY),

(I'm assuming your example date is 1 March 2011, otherwise if it is 3 Jan 2011, then the format needs to be 'DD/MM/YYYY')

so your full mapping would be:

to_char(to_date(P2CAA02_FIXEDASSET_POSTINGINFO_MAP."*ASSET_CAPITALIZATION_DATE", 'MM/DD/YYYY'),'YYYYMMDD')

[Former Member]

Yes, sorry I forgot to mention that I did use the to_char function and still it did not convert.

[Former Member]

Can you post your code?

If I create a script with the following code

 print(to_char(sysdate(), 'yyyymmdd'));

the output is - 20110805

[Former Member]

Hi

If you require a text representation of a date, use the to_char() function.

M