SQL Date/Time Comparison

[Alan0568]

Hi Folks,

I'm trying to get a count from a table where the values in a [DATE] column are more recent than a specified lapsed time. Here is the code so far which doesn't appear to work given the Sysdate and the values in the table.
----
select count(*) as StatusResult from table1
where to_char(date1, 'DD.MM.YYYY:HH24:MI:SS') >
(select to_char(sysdate - .5, 'DD.MM.YYYY:HH24:MI:SS')from dual)
----

In this case 6 hours but I'll need to adjust. Any help would be appreciated.

Cheers

Al

 

[SantaMufasa]

...Here is the code so far which doesn't appear to work...

So, Alan, would you like us to guess what the results of your query are? <grin>


So that we can better help you diagnose this problem, it would be helpful to see values that are in your table that should have been part of the result set, but are not.

Also, there is no (business/logical) need (as far as I can tell) for you to convert either your already-DATE columns, date1 and SYSDATE from DATE expressions into character expressions to accomplish the comparison you are doing. Neither must you access "...SYSDATE from DUAL" to obtain the current DATE/TIME. You should get intended results by simply coding

select count(*) as StatusResult from table1
where date1 > sysdate - .5;

Let us know if this code adjustment gives you results you wanted...The new code is certainly more efficient by not doing extraneous Date-to-Char conversions and not accessing the DUAL table.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[Alan0568]

Fair point Mufasa. The results were always 0. I did get the query to work after changing the second part of the comparison to ...
----
>
(select to_char(sysdate - INTERVAL '6' HOUR , 'DD.MM.YYYY:HH24:MI:SS')from dual)
----

However having read you post this morning you are quite correct and you sql
----
select count(*) as StatusResult from table1
where date1 > sysdate - .5;
----
works fine and is obviously more efficient. I now understand that variants of to_char(date,format) are basically just the presentation layer as the underlying columns data type obviously contains the full date time element anyway.

Many thanks for your assistance.

A

 

[Dagon]

What you were doing previously made no sense. Converting the dates to character strings in the format DD.MM.YYYY:HH24:MI:SS would render the comparison meaningless. It would compare for example:

23.03.2003:18:07:08

with

21.12.2008:15:13:02

and decide that the first one was the more recent date because its leading part ('23') when considered as a string is higher than '21'.