data format conversion

dsscholar · Post by **dsscholar** » Mon Sep 19, 2011 8:55 pm

Hi all,

I have a varchar column coming the source(ds) which needs to be converted to date (target oracle's default date format).

Format eg : 11 jun 2011 11 : 30

This needs to be converted to oracle's standard date format. I tried in transformer using stringtodate(inputcolumn,'%yyyy-%mm-%dd'). I gives minimum characters in the required year place something like that. I understand that the format is wrong. Please help in this conversion..

Thanks in advance.

jwiles · Post by **jwiles** » Mon Sep 19, 2011 10:06 pm

Will you be loading an Oracle table using DataStage? If so, tackle the task of converting the string into a Date datatype within DataStage first (well, do that anyway

). StringToDate() function in the parallel transformer would be an appropriate starting point.

If you're loading the table with a DataStage job and the date is stored as an Oracle Date datatype, DataStage will handle the conversion from DS Date (or Timestamp if you prefer) to Oracle Date automatically and you don't need to worry about formatting it for Oracle (dates and databases have been discussed many times here).

Regards,

chulett · Post by **chulett** » Mon Sep 19, 2011 10:15 pm

Make sure you understand how StringToDate works. A date is a date, so you need to describe what format the incoming string is in, not what you think the date needs to look like.

ray.wurlod · Post by **ray.wurlod** » Mon Sep 19, 2011 11:12 pm

Perhaps StringToTimestamp() should be the function you are using. The StringToDate() function has day granularity.

dsscholar · Post by **dsscholar** » Tue Sep 20, 2011 1:14 am

As mentioned, i tried with StringToDate(UStringToString(lnk_scAuditKey.timestamp),'%dd %mon %yyyy'). Input format is 30 jul 2011 11:30. I tried with StringToTimestamp also. in the format of '%dd %mmm %yyyy %hh : %mm : %ss'. But that also not working.

Error
: Data string '30 Jun 2011 ' does not match format '%dd %mon %yyyy': an integer was expected to match tag %m.

SURA · Post by **SURA** » Tue Sep 20, 2011 1:38 am

Use as Ray said. Hence you have time in the date, you should go for timestamp too.

DS User

ray.wurlod · Post by **ray.wurlod** » Tue Sep 20, 2011 1:40 am

%mon is not a valid date format token. All of the valid tokens may be found in the documentation (manuals or Information Center).