Convert to MM/DD/YYYY HH:MI:SS AM/PM format in Server job

sumesh.abraham · Post by **sumesh.abraham** » Fri Mar 25, 2011 8:04 am

Hi,

I have data coming in as 2010-02-19 16:00:00 and I want to convert it to format MM/DD/YYYY HH:MI:SS AM/PM.

Can someone suggest the Oconv and Iconv combination to achieve this.

chulett · Post by **chulett** » Fri Mar 25, 2011 9:04 am

What have you tried? If it helps, you'll need to split the date from the time, convert both separately and then stick them back together.

sumesh.abraham · Post by **sumesh.abraham** » Fri Mar 25, 2011 10:09 am

Thanks Craig.

I have tried the following.
datepartupdts=Field(parmUpdateTs,' ',1)
timepartupdts=Field(parmUpdateTs,' ',2)
parmSnapDateTime = Oconv(IConv(datepartupdts,'D-YMD[4,2,2]'),'D/MDY[2,2,4]') : ' ' : OConv(IConv(timepartupdts,"MTS"),"MTS")

When I print the values, they get printed in MM/DD/YYYY HH:MI:SS format as expected.

Then I am using to insert these values into a table using a DSExecute cmmand as

Cmd3 ='INSERT INTO curve_term_value': '(upd_ts,snap_date_time,term_id,value)':' values(':SQUOTE(parmUpdTs):',':SQUOTE(parmSnapDateTime):',':parmTermId:',':parmValue:')';

This creates date format issues with Oracle. Then I tried to_date in the statement
Cmd3 ='INSERT INTO curve_term_value': '(upd_ts,snap_date_time,term_id,value) values(':to_date(SQUOTE(parmUpdTs),'MM/DD/YYYY HH24:MI:SS'):',':to_date(SQUOTE(parmSnapDateTime),'MM/DD/YYYY HH24:MI:SS'):',':parmTermId:',':parmValue:')';

I get compilation error from Server Routine as
Array 'to_date' never dimensioned.

1 Errors detected, No Object Code Produced.

I am using the sql to_date function. How to dimension this?

chulett · Post by **chulett** » Fri Mar 25, 2011 10:23 am

If your target is Oracle, there's absolutely no need to 'convert' that date format, simply load it as is with the appropriate TO_DATE mask. However, you can't simply use DSExecute to perform a sql INSERT unless you're not showing us critical (but missing) pieces of the puzzle. You could use sqlplus for this... is that what's actually going on here?

sumesh.abraham · Post by **sumesh.abraham** » Fri Mar 25, 2011 10:30 am

The whole code snippet as below.
When I take the to_date from the insert, it does not do either . I added it because the same statement when executed from PL/SQL developer threw error with invalid month.
I am wondering why the to_date is required since I am already converting to date format with Oconv. Why is teeh warning fot to_date not dimensioned in the DSExecute? Many thanks.

datepartupdts=Field(parmUpdateTs,' ',1)
timepartupdts=Field(parmUpdateTs,' ',2)
parmSnapDateTime = Oconv(IConv(datepartupdts,'D-YMD[4,2,2]'),'D/MDY[2,2,4]') : ' ' : OConv(IConv(timepartupdts,"MTS"),"MTS")

Cmd1 = 'sqlplus -s ':parmDBUser:'/':parmDBPass:'@':parmDBName:' << EOF'
Cmd2='set heading off'
Cmd3 ='INSERT INTO curve_term_value': '(upd_ts,snap_date_time,term_id,value) values(':to_date(SQUOTE(parmUpdTs),'MM/DD/YYYY HH24:MI:SS'):',':to_date(SQUOTE(parmSnapDateTime),'MM/DD/YYYY HH24:MI:SS'):',':parmTermId:',':parmValue:')';
Cmd4 = 'EOF'
Cmd = Cmd1:Char(10):Cmd2:Char(10):Cmd3:Char(10):Cmd4
Call DSExecute('UNIX',Cmd,Output1,ReturnCode)

chulett · Post by **chulett** » Fri Mar 25, 2011 10:34 am

As noted, just load the date as is, you are wasting your time and energy doing an unneccesary conversion. Your problem is leaving the 'to_date' parts exposed so DataStage tries to interpret them and fails. Keep everything inside your strings as you build Cmd3 except for the parameter values you want substituted in.

sumesh.abraham · Post by **sumesh.abraham** » Fri Mar 25, 2011 11:10 am

I tried
Cmd3 ='INSERT INTO curve_term_value (upd_ts,snap_date_time,term_id,value) values(to_date(SQUOTE(parmUpdTs),'MM/DD/YYYY HH24:MI:SS',to_date(SQUOTE(parmSnapDateTime),'MM/DD/YYYY HH24:MI:SS'),parmTermId,parmValue)';

and still get warnings from routine as

WARNING: Variable 'MM' never assigned a value.
WARNING: Variable 'DD' never assigned a value.
WARNING: Variable 'YYYY' never assigned a value.
WARNING: Variable 'HH24' never assigned a value.
WARNING: Variable 'MI' never assigned a value.
WARNING: Variable 'SS' never assigned a value

chulett · Post by **chulett** » Fri Mar 25, 2011 11:20 am

Use double-quotes for your strings so the single quotes in the insert statement don't cause you grief. And again, make sure your parm variables are not inside your strings but are concatenated into them so they get a chance to resolve.

sumesh.abraham · Post by **sumesh.abraham** » Fri Mar 25, 2011 1:55 pm

Thanks Craig. That helped.
Now I am able to generate the same sql statement within the routine that when executed against a database inserts the record correctly.

Unfortunately, for some reason, the insert via DSExecute is still not happening. Job completed without any error, DSExecute returns 0 and no warnings.
The sql generated and the output from routine is as below.

INSERT INTO table1 (col1,col2,col3,col4) values(to_date('2011-03-21 10:20:00 AM','MM/DD/YYYY HH24:MI:SS'),to_date('2010-02-25 4:00:00 PM','MM/DD/YYYY HH24:MI:SS'),5270,1.09)
DSLogInfo called from :
Message to be logged is...
> Output is

ray.wurlod · Post by **ray.wurlod** » Fri Mar 25, 2011 2:41 pm

You're using a 12 hour clock in your value but at 24 hour clock (HH24) in your date format picture. That's almost certainly why it's not working. Your date picture does not match the string date format.

chulett · Post by **chulett** » Fri Mar 25, 2011 3:07 pm

Right... something that Oracle would have told you if you ran that same sql in something like Toad. And that's something else you're going to have to contend with - DSExecute returned a zero simply because it was able to run sqlplus, it doesn't care that your SQL failed.

From what I recall, I believe there's a connection option that makes it care, to report sql errors back up the foodchain.