Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
  • 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

581 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