Convert to MM/DD/YYYY HH:MI:SS AM/PM format in Server job
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 153
- Joined: Thu May 11, 2006 1:52 am
- Location: Bangalore
Convert to MM/DD/YYYY HH:MI:SS AM/PM format in Server job
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.
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.
-
- Participant
- Posts: 153
- Joined: Thu May 11, 2006 1:52 am
- Location: Bangalore
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?
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?
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 153
- Joined: Thu May 11, 2006 1:52 am
- Location: Bangalore
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)
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)
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 153
- Joined: Thu May 11, 2006 1:52 am
- Location: Bangalore
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
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
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 153
- Joined: Thu May 11, 2006 1:52 am
- Location: Bangalore
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
From what I recall, I believe there's a connection option that makes it care, to report sql errors back up the foodchain.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers