Using Substring AND math on the same column
Posted on 2005-03-15
I am trying to do math on a column, then substring it so I can convert it to a time like value. The field is a number field that contains HHMM. I am subtracting another column from it that is in thousanths of a second. The math works alone. The substring works alone. Put both together and it fails.
Here is the math: select (ENDRUN*60)-(ELAPTIME/100))/60 from CMR_RUNINF
Here is the substring: substring(ENDRUN,1,2)
Here is both:
select JOBNAME, MEMNAME, NODEID, STARTRUN as "DATE", substr(((ENDRUN*60)-(ELAPTIME/100))/60),1,2) as "START TIME", ENDRUN as "END TIME", ELAPTIME/100 as "Minutes" from CMR_RUNINF order by JOBNAME
Here is my error:
System.Data.OleDb.OleDbException: The data type int is invalid for the substring function,
Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.
What am I missing?
What is the Oracle equivelant (I need to run this against both DB types!)