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

x
?
Solved

Using Substring AND math on the same column

Posted on 2005-03-15
4
Medium Priority
?
420 Views
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!)
0
Comment
Question by:JRamos1200
  • 2
4 Comments
 
LVL 7

Expert Comment

by:fefo_33065
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,

Fefo

ps: Not familiar with Oracle
0
 
LVL 7

Accepted Solution

by:
fefo_33065 earned 1500 total points
ID: 13551269
OOps,

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


0
 
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.


Goodluck!
eNTRANCE2002 :-)
0
 

Author Comment

by:JRamos1200
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!!
0

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