Current position:  Home > Default > Operation on a varchar colunm

Operation on a varchar colunm

Time:November 30
Advertisement
Hi,
I would like to know if it's possible to do some mathematical operations on a nvarchar2 colunm ? My delay colunm is typed as nvarchar2(5) and we store delay such as : hh:mm.
Do you have any tips or function which can allow me to extract all delay which are greater than 00:30 or for example less than 00:30 ?
SELECT
  DISTINCT RES."ID" AS "id"
FROM
   RES
WHERE
  RES.SITE_ID = 3
  AND   (
    trunc(RES."RECEIPT", 'DD') >= TO_DATE('27-02-2012', 'DD-MM-YYYY') AND
    trunc(RES."RECEIPT", 'DD') <= TO_DATE('28-02-2012', 'DD-MM-YYYY') AND
    RES."DELAY" <= '00:30'
ORDER BY
  1Thanks.
Regards,
Jarod
Advertisement
Why not use an interval column?
SQL> create table tester
  2  (duration interval day to second);
Table created.
SQL>
SQL> insert into tester values(to_dsinterval('00 01:00:00'));
1 row created.
SQL> insert into tester values(to_dsinterval('00 01:20:00'));
1 row created.
SQL> insert into tester values(to_dsinterval('00 00:20:15'));
1 row created.
SQL> insert into tester values(to_dsinterval('00 00:01:00'));
1 row created.
SQL> insert into tester values(to_dsinterval('00 04:00:00'));
1 row created.
SQL>
SQL> select *
  2  from tester
  3  where duration < to_dsinterval('00 00:30:00');
DURATION
+00 00:20:15.000000
+00 00:01:00.000000
SQL>

How to convert Date in varchar(50) format MM/DD/YYYY HH:MM into YYYY-MM-DD format using MS SQLServer 2008 R2 ?

2015-10-11

Hi, I am getting the error "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value." when converting date in format Date in varchar(50) format MM/DD/YYYY HH:MM into YYYY-MM-DD format using MS SQLServer 20

Oracle Table Storage Parameters - a nice reading

-0001-11-30

bold Gony's reading excercise for 07/09/2009 bold - The below is from the web source http://www.praetoriate.com/t_%20tuning_storage_parameters.htm. Very good material.The notes refers to figures and diagrams which cannot be seen below. But the text b

Boolean operators in the WHERE clause

-0001-11-30

I'd like to create an operator which would allow me to use the following syntax in the where clause: select 1 from dual where iplike('172.16.0.128', '*.*.*.*'); I've created a function (iplike_bool_fn) to implement my desired check, and attempted to

Fault cases identification using Sql query

-0001-11-30

Hi , i have data in below format.using below data to extract the fault cases. operator machine fromdate todate 1491 QC03 2014-09-02 02:51:00.000 2014-09-02 06:10:00.000 1427 QC03 2014-09-02 06:11:00.000 2014-09-02 07:17:00.000 1491 QC03 2014-09-02 11

Error when deliverySemantics set to OnceAndOnlyOnce

-0001-11-30

Hi, When i set deliverySemantics = "OnceAndOnlyOnce" ,i get <Oct 16, 2002 11:24:35 AM EDT> <Error> <B2B> <000000> <<Repository> ERROR: In SQ LExecutor.doUpdate, a SQL Exception was caught while executing a SQL St

SCCM 2007 LastLogonTimeStamp

-0001-11-30

I need help getting the LastLogonTimeStamp to convert to a date. This is my query  SELECT     dbo.v_R_System.Name0, dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],  v_R_System.LastLogonTimeStamp0 AS [LastLogonTimeStamp],                    

The data types varchar and varchar are incompatible in the subtract operator

-0001-11-30

Hi ; I want to fetch values  6,903,00  format ,my query is ; SELECT CONVERT(varchar(50), CONVERT(money,SUM(DEBIT)),1) AS DEBIT ,CONVERT(varchar(50), CONVERT(money,SUM(CREDIT)),1),AS CREDIT CONVERT(varchar(50), CONVERT(money,SUM(DEBIT)),1) - CONVERT(v

Between operator for VARCHAR values

-0001-11-30

Hi there, I am trying to compare one varchar value between two varchar values which is not working. for e.g. in my table there are two columns. FROMpcode(column1) L6P 0A1 L6A 0A1 TOpcode(column2) L7E 5S1 L6A 4R1 the value I want to compare for betwee

Data Federator XI 3.0 using DB2 VARCHAR FOR BIT DATA Column?

2015-10-11

We have a column in a DB2 database that is defined as VARCHAR(16) FOR  BIT DATA. We are using the suggested IBM JDBC driver, db2jcc.jar, against a DB2  OS/390 8.1.5 version database. The Datasource column displays a data type of NULL, indicating the

Index as varchar or numeric

2015-10-11

Hi: I'm using a field that contains only digits (0..9) and will use this field as a primary key. Does it make any difference if the field is a varchar or a numeric? I do not plan on performing any mathematical operations on the field's value, so it w

SSRS 2005 report: Cannot bulk load Operating system error code 5(Access is denied.)

2015-10-11

I built a SSRS 2005 report, which calls a stored proc on SQL Server 2005. The proc contains following code: CREATE TABLE #promo (promo VARCHAR(1000)) BULK INSERT #promo FROM '\\aseposretail\c$\nz\promo_names.txt' WITH --FIELDTERMINATOR = '', ROWTERMI

Is there a way to dynamically calculate values for the IN operator?

2015-10-11

I am looking to see if there is a way to dynamically calculate the criteria to include inside an IN operator in the WHERE clause. For example when I try to run the following I get an Oracle error message 'OR!-01722: invalid number'. The script below

From varchar to date format mm/dd/yyyy

2015-10-11

Hi everyone, I am pretty sure this question has been asked before, but I've been going through the blog for quite a while and can't seem to find anything! Here is my problem. I am trying to compare a field in my database with a certain date, but the

Error while invoking database stored procedure while mapping to varchar,int

2015-10-11

Hi I am using Oracle SOA 11g 11.1.1.4 In that i am calling db procedure.The procedure is taking input parameters int and varchar and nillable is true for both cases.Its a sybase data base. I am calling this db proc through db adater.Here I am mapping

Performance operations based on Column values in SQL server 2008

2015-10-11

Hi , I have a table which consist of following columns ID    Formula               Values                  DisplayValue 1                     a*b/100       100*12/100           null 2                     b*c/100        12*4/100               null I w

SCOM 2012 R2 installing on SQL 2012 SP1 - Error Code: 0x80131904, Exception.Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

2015-10-11

Hi, I am getting an issue during a SCOM 2012 R2 installation while creating the SCOM DataWarehouse database. Setup seems to timeout during creating the datawarehouse database. I can see all database files created in windows explorer on the SQL server

Logs for 'Operating mode' change in mirroring

2015-10-11

Logs for 'Operating mode' change in mirroring Hi Everyone, Is there any log,that shows when the Operating mode of a database involved in mirroring(SQL SERVER 2008 R2) has been changed from asynchronous to synchrous(or vise versa). Regards, Aspet A.GT

Store procedure to take Back up operation of certain column values.

2015-10-11

Hi , Can anyone please tell me how to do this? I have three table 1:emp(empno,ename,deptno,sal) 2:emp_changes(empno,changetime(timestamp),operation(varchar2)) 3:emp_metadata(tablename(varchar),Maxtime(timestamp)) I want to create a trigger which will

IN operator in nested tables

2015-10-11

Hi, Does anyone can redo an IN operator in nested tables example? http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28371/adobjcol.htm#sthref396 My example returns no row... CREATE TYPE Pilote_elt_nt_type AS OBJECT (brevet VARCHAR(4), nomPil V