[sqr-users] RE: Inconsistent Data Types

["White, Denise" <DEWhite@vicr.com>]

To set the record straight, since there were several responses that you can't 
use trunc() on dates, the Oracle function trunc() can be used with a datetime 
field, in which case it will strip off the time part of the value (you can 
probably use it on a date as well, but there would be no difference).  This can 
be very useful when comparing such fields.  For example, if you are looking for 
dates <= 2/1/2003, and the field is datetime, this comparison will not pick up 
dates on 2/1/2003 itself, unless the time portion is missing or all zeroes.  If 
you use trunc(), it will pick up all dates on 2/1/2003, regardless of the time. 
 Using trunc() like this does not require a second argument (I assume it 
defaults to 0).

If Cal_Date is a datetime field, you may well want to use trunc() on it to 
compare it against the straight date you are inputting.  However, I don't think 
you want to use it on $Pay_End_Dt.  You are correct that a variable beginning 
with $ can be character or date, but SQR will not actually treat it as a date 
unless you first declare the variable to be one (look in reference for 
DECLARE-VARIABLE).  I find that using date variables in such comparisons is not 
completely reliable (in some versions of SQR, for example, the system will 
consider the last day of a month and the first day of the next month as equal, 
so you will get unpredictable results).  Since you are presumably entering the 
date in the format recognized by the database, you might get away with using it 
as a string.  You could test this in your query tool.  I find the most 
consistently reliable way of handling an input date in a WHERE clause is to 
treat it as a string (don't declare it as a date) and use the TO_DATE() 
function on it.  I have never had ANY problem doing it like that.  I would 
suggest the following:

where trunc(Cal_Date) <= to_date($Pay_End_Dt,'dd-mon-yy') 

HTH,

Denise M. White
Sr. Software Engineer
Vicor 
--__--__--

Message: 8
Subject: RE: [sqr-users] Inconsistent Data Types
Date: Thu, 31 Jul 2003 10:53:07 -0500
From: "Bob Helm" <Bob.Helm@mediware.com>
To: <sqr-users@sqrug.org>
Reply-To: sqr-users@sqrug.org

I think the problem is here:
trunc($Pay_End_Dt)

trunc is a numerical operator but you are passing it a sting or date variable.
Also, you may need a second argument in the trunc call, something like this:
trunc(#number, #trunc_position)

Not sure if you really need trunc or something else, maybe a dateadd???

-----Original Message-----
From: Chrisman, Dan: [mailto:Daniel.E.Chrisman@conocophillips.com]
Sent: Thursday, July 31, 2003 10:35 AM
To: sqr-users@sqrug.org
Subject: [sqr-users] Inconsistent Data Types


I get the following error when I test my program code.

SQR 5528 ORACLE OEXEC error -932 in cursor 1:
ORA-00932: inconsistent datatypes

error on Line 344:
(SQR 3735) could not execute SQL.

I am trying to execute the following code

! Lock the current pay period

begin-SQL
update TK_Calendar
  set Cal_Payroll_Code = {LOCKED}
where trunc(Cal_Date) <= trunc($Pay_End_Dt) 
AND Cal_Payroll_Code = {OPEN}
end-SQL

where Cal_Date is a Data field in the Table and Cal_Payroll_Code is CHAR (1) 
field.
$Pay_end_Dt is keyed in on an input statement as the date dd-mmm-yy. As I 
understand it $ can mean either a Date or character field.

Could someone tell me what I'm doing wrong?

Daniel E. Chrisman
Senior Analyst
Ponca City Refinery
ETN 442.5654
DDE 580.767.5654

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users