Using Substring AND math on the same column

Posted on 2005-03-15
Medium Priority
Last Modified: 2008-02-26
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!)
Question by:JRamos1200
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Expert Comment

ID: 13551227
Convert it to string first,

select JOBNAME, MEMNAME, NODEID, STARTRUN as "DATE", Convert(char,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

I hope this helps,


ps: Not familiar with Oracle

Accepted Solution

fefo_33065 earned 1500 total points
ID: 13551269

The other way around:

JOBNAME, MEMNAME, NODEID, STARTRUN as "DATE", substr(Convert(char,((ENDRUN*60)-(ELAPTIME/100))/60)),1,2) as "START TIME", ENDRUN as "END TIME", ELAPTIME/100 as "Minutes" from CMR_RUNINF order by JOBNAME

LVL 14

Expert Comment

by:Renante Entera
ID: 13552687
Hi JRamos1200!

By the way, could you double-check your posted code ???  I think there's a problem on this part :
   substr(((ENDRUN*60)-(ELAPTIME/100))/60),1,2) as "START TIME",

It should be : substr(((ENDRUN*60)-(ELAPTIME/100)/60),1,2) as "START TIME",

Remove extra ")" before "/60".  Just correct me if I'm wrong.

And to answer your question, you could also do it using CAST() function.

Here is the code :

select JOBNAME, MEMNAME, NODEID, STARTRUN as "DATE", substr(cast(((ENDRUN*60)-(ELAPTIME/100)/60) as varchar(10)),1,2) as "START TIME", ENDRUN as "END TIME", ELAPTIME/100 as "Minutes" from CMR_RUNINF order by JOBNAME

Hope this helps you.  Just try it.

eNTRANCE2002 :-)

Author Comment

ID: 13647105
That did got the results I expected, Thanks!  Now I just need to re-think my logic as I was doing bad date math!!

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question