How do you convert scientific time to standard

Hi All, I'm new to this forum, and appreciate any assistance with my issue.
I have a shell script that logs into an oracle DB and runs a sqlplus query. Everything works great except for the time I get. I'm new to shell so bare with me. What would be the code and where do I place it?

My results are this
myjob1
1.1915E+12
I need it to be seen as myjob1 hh:mm:ss: MM/DD/YY

Here is my code

 
#!/usr/bin/sh

sqlplus -S myusername/mypassword@MYDATABASE <<eof> myfile
       set heading off feedback off verify off
       select JOB.JOBNAME, JOBRUN.ENDTIME from JOB, JOBRUN where JOB.JOBID = JOBRUN.JOBID and JOB.JOBNAME in ('myjob1');
exit
 
EOF
�

Thanks for any help

Is not the solution, but, try this:

leo@lein:~$ echo 1.1915E+12|bc -l
13.19159
leo@lein:~$ 

Format more friendly.

Hi.

Try using to_char...

sqlplus -S myusername/mypassword@MYDATABASE <<eof> myfile
set heading off feedback off verify off
select JOB.JOBNAME, to_char( JOBRUN.ENDTIME, 'HH24:MI:SS MM/DD/YY' ) from JOB, JOBRUN where JOB.JOBID = JOBRUN.JOBID and JOB.JOBNAME in ('myjob1');

you could use the TO_CHAR function.

#!/usr/bin/sh

sqlplus -S myusername/mypassword@MYDATABASE <<eof> myfile
       set heading off feedback off verify off
       select JOB.JOBNAME, TO_CHAR(JOBRUN.ENDTIME, 'HH24:MI:SS MM/DD/YY') from JOB, JOBRUN where JOB.JOBID = JOBRUN.JOBID and JOB.JOBNAME in ('myjob1');
exit
 
EOF
�

alternatively, you can set the global date format in sqlplus like this:

alter session set NLS_DATE_FORMAT='<my_format>';

more info on time/date formatting here.

That almost got it, at least now I'm in epoc time
I didwhat you posted, and it gave me an error, so I removed the 'MM/DD/YY' from the query and that's how I got the epoc time. So almost there, I may be able to find some more info now. unless you guys have a quick code. Then I have to start working on grabbing the newest Time

Now it reads
myjob1
1193191665000
myjob1
1193278063000
myjob1
1193366454000

I don't believe the query is wrong (certainly not from two identical posts). Show us exactly what your query was, and the error.

You must've typed something incorrectly.
Have a look at this Oracle session to understand both techniques:

test@XE> 
test@XE> create table t (x number, y date);

Table created.

test@XE> 
test@XE> insert into t (x,y)
  2  select 1, to_date('12/31/2008 13:23:47','mm/dd/yyyy hh24:mi:ss') from dual union all
  3  select 2, to_date('2/4/2009 9:19:34','mm/dd/yyyy hh24:mi:ss') from dual union all
  4  select 3, to_date('5/24/2009 19:23:58','mm/dd/yyyy hh24:mi:ss') from dual;

3 rows created.

test@XE> commit;

Commit complete.

test@XE> 
test@XE> -- (1) using to_char function
test@XE> select y, to_char(y,'hh24:mi:ss mm/dd/yy') y_fmt from t;

Y           Y_FMT
------------------ -----------------
31-DEC-08       13:23:47 12/31/08
04-FEB-09       09:19:34 02/04/09
24-MAY-09       19:23:58 05/24/09

test@XE> 
test@XE> -- (2) using nls_date_format
test@XE> alter session set nls_date_format = 'hh24:mi:ss mm/dd/yy';

Session altered.

test@XE> 
test@XE> -- now you don't have to put that to_char around the date column
test@XE> select y from t;

Y
-----------------
13:23:47 12/31/08
09:19:34 02/04/09
19:23:58 05/24/09

test@XE> 
test@XE> 

tyler_durden

Gives me this error
ERROR at line 1:
ORA-01481: invalid number format model
When I use the code with date format, Here is my code that is getting errors

#!/usr/bin/sh

sqlplus -S username/password@MYDATABASE <<eof> myfile
set heading off feedback off verify off
select JOB.JOBNAME, to_char(JOBRUN.ENDTIME, 'HH24:MI:SS MM/DD/YY') from JOB, JOBRUN where JOB.JOBID = JOBRUN.JOBID and JOB.JOBNAME in ('myjob1');
exit

EOF

Can you describe the table JOBRUN - is the ENDTIME column a date type?

Jobrun table does hold run time columns and the endtime column holds the time the job end time.

what is the datatype of the endtime column?
the error indicates it most likely isn't date

This is date datatye
Here is a link to what the error code is about
ORA-01481: invalid number format model

I'm trying to reread SQL info to get the correct syntax

Not sure the last post was handled as it had a link, but here is the cause of the error code from a website

Cause: The user is attempting to either convert a number to a string via TO_CHAR or a string to a number via TO_NUMBER and has supplied an invalid number format model parameter.

The datatype is a TIME stamp (numbers), I'm rereading SQL data formatting now

---------- Post updated at 07:04 PM ---------- Previous update was at 06:56 PM ----------

correct my last post as the data is a time stamp (numeric)

the TO_CHAR function accepts both DATE and TIMESTAMP datatypes and shouldn't produce this error for those types.

what output does the following produce:

select data_type from user_tab_columns where column_name = 'JOBRUN.ENDTIME';

Ok here is what I have found, the column is date/time data, the EPOC time I receive is 1250909923000. I've found that this is was too many numbers, but when you remove the last 3 "0's" in the string, and compute it, it is the exact time that the job ended in our Prod environment, so the question now is how to you remove the last 3 digits cause I believe it will work. Any suggestions ?

Using bash parameter expansion:

TIME=1250909923000
# echo ${TIME:0:10}
1250909923

Right but I need to have this done within the sql statement , in the to_char. Thanks for the post

Hi.

As an example:

select to_char(1234567891000) from dual;
TO_CHAR(12345
-------------
1234567891000
 
 
select substr(to_char(1234567891000), 1, 10) from dual;
 
SUBSTR(TO_
----------
1234567891
 

try to amend the to_char function like this:

TO_CHAR(SUBSTR(JOBRUN.ENDTIME, 1, 10), 'HH24:MI:SS MM/DD/YY') from JOB