From: Zabach, Elke (elke.zabachsap.com)
Date: Wed Apr 14 2004 - 01:11:50 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Alan Penny wrote:
>
> I'm trying to write a SQL statement that needs
> some data type casting/conversion. I can't
> seem to find the documentation to do this.
>
> The following expression:
>
> rec_entry_date > SUBTIME(TIME, '00:15:00')
>
> Gives the error:
>
> Integrity constraint violation;-8006 POS(61) Data types must be
> compatible.
>
> In Oracle you would do something like:
>
> rec_entry_date > CAST(SUBTIME(TIME,
> '00:15:00') AS DATE)
>
> But that does not work.
>
> I don't want to switch on the "use Oracle syntax" option,
> there must be a way to express this natively.

Sorry, but what do you want to do?
You want to ask if a date
(rec_entry_date is of Oracle-datatype date == internal datatype timestamp
or the internal datatype date?)
is greater than a time-value (with no date-part in) ?

If rec_entry_date is of internal datatype timestamp, it includes a time-part,
which can be extracted using the function time -->
time (rec_entry_date) > SUBTIME (Time, '00:15:00),
or the other way round (if the date-part has to be taken into account)
rec_entry_date > timestamp (date, subtime(time, '00:15:00)).

In case rec_entry_date is of internal datatype date I am happy that MaxDB
is not able to compare date(without time-part) and time-values (without date-part),
but produces the correct error -8006.

Elke
SAP Labs Berlin

>
> --------------------------------------------------------------
> -------------
> Alan Penny
> Software Analyst
> Clinical Reference Laboratory, Inc.
> email: pennyacrlcorp.com
> voice: +01 913-693-2536
> fax: +01 913-492-1622
> web: www.crlcorp.com
>
>
> DISCLAIMER: The information in this message is confidential and may be
> legally privileged. It is intended solely for the addressee.
> Access to this
> message by anyone else is unauthorized. If you are not the intended
> recipient, any disclosure, copying, or distribution of the
> message, or any
> action or omission taken by you in reliance on it, is
> prohibited and may be
> unlawful. Please immediately contact the sender if you have
> received this
> message in error. Thank you.
>
>
>
> --
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe:
> http://lists.mysql.com/maxdb?unsub=elke.zabachsap.com
>

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]