Bearing in mind my other posts about dates and times I'd like to know if this is right and guaranteed to work.

select _hidealarm, cast(_hidealarm as date) from calls
where
_hidealarm is not null
and
_Hidealarm <= date'2008-09-15'

_HideAlarm is a TIMESTAMP and testing a value of 2008-09-15 12:00 AM is shown. I like this, but I expected to have to CAST _HideAlarm to a DATE.

Is this WAD or a bug?

Roy Lambert

Roy,

<< _HideAlarm is a TIMESTAMP and testing a value of 2008-09-15 12:00 AM is
shown. I like this, but I expected to have to CAST _HideAlarm to a DATE. >>

Yes, this is working correctly.  You can compare a date to a timestamp with
no problems, since the date type is a sub-set of the timestamp type without
missing any information required to make a proper comparison.  The time is
assumed to be 12:00 am midnight.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< You can compare a date to a timestamp with no problems, since the date
type is a sub-set of the timestamp type without missing any information
required to make a proper comparison.  The time is assumed to be 12:00 am
midnight. >>

Two datetime values are comparable only if both have the same primary
datetime field, hence comparing a date to a timestamp is not allowed in
standard SQL. See subclause 4.6.2 "Datetimes" (9075-2:2003).

Ole Willy Tuv

Tim

>Yes, this is working correctly.

Great so I'm safe to use this.

>You can compare a date to a timestamp with
>no problems, since the date type is a sub-set of the timestamp type without
>missing any information required to make a proper comparison. The time is
>assumed to be 12:00 am midnight.


Being my normal weird self I though I'd just try the same with a time comparison and that doesn't work.

Roy Lambert

Ole

>Two datetime values are comparable only if both have the same primary
>datetime field, hence comparing a date to a timestamp is not allowed in
>standard SQL. See subclause 4.6.2 "Datetimes" (9075-2:2003).

For us mortals does that mean that what ElevateDB is currently doing is against the standard?

Roy Lambert

Roy,

<< Being my normal weird self I though I'd just try the same with a time
comparison and that doesn't work. >>

No, that won't work because you're missing the date portion.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< For us mortals does that mean that what ElevateDB is currently doing is
against the standard? >>

Yes.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


Just doing some research I found this on http://download-uk.oracle.com


DATE Datatype

The DATE datatype stores date and time information. Although date and time information can be represented in both character and number datatypes, the DATE datatype has special associated properties. For each DATE value, Oracle stores the following information: century, year, month, date, hour, minute, and second.

....
....

Oracle automatically converts character values that are in the default date format into date values when they are used in date expressions.

If you specify a date value without a time component, then the default time is midnight. If you specify a date value without a date, then the default date is the first day of the current month.


No wonder I'm bloody confused

Roy Lambert

Tim


Thought so - ok what's going to happen? Stay the same? Go along with the standard?

Roy Lambert

Tim


I know - I just thought I'd see if it assumed a date - after all if it could pick on midnight why couldn't it pick on my birthday

Roy Lambert