sysdate usage in where clause 1

[jdobyns]

I want to be able to run a query everyday beginning at 6am this morning to 6am yesterday morning. The query will always be run after 6am manually via Excel. We are running a 9i Oracle database. I have been unable to get the syntax correct. Here is a description of the table.

Name Null? Type
------------------------------- -------- ----
TAG_REF_NO NOT NULL NUMBER(5)
IN_OUT NOT NULL VARCHAR2(1)
C_DT NOT NULL DATE
TRANSACTION_NO NOT NULL NUMBER(12)
GROSS_WT NOT NULL NUMBER(11,3)
FRONT_AXLE NOT NULL NUMBER(11,3)
DRIVE_AXLE NOT NULL NUMBER(11,3)
TANDEM_AXLE NOT NULL NUMBER(11,3)

example:

select * from table_name
where c_dt > = sysdate (06:00:00) -1

I know that is not correct but that is what I am trying to accomplish. Thanks in advance!

Update: I have gotten this far but with an inconsistant data type error:

1 select tag_ref_no, in_out, to_char(c_dt,'mm/dd/yyyy hh24:mi'), transaction_no
2 from api_Scale
3 where c_dt between to_char(trunc(sysdate) +6/24, 'MM/DD/YYYY HH24:MI')
4* and to_char(trunc(sysdate) +6/24, 'MM/DD/YYYY HH24:MI') -1
SQL> /
where c_dt between to_char(trunc(sysdate) +6/24, 'MM/DD/YYYY HH24:MI')
*
ERROR at line 3:
ORA-00932: inconsistent datatypes

 

[SantaMufasa]

JDobyns,

My suggestion to you is, "When it comes to DATE expressions, don't try to overmanage Oracle"...Oracle's DATE processing algorithms are much, much stronger than other vendors' DATE-(mis)handling routines.

Your code should work with these simplifications:

select tag_ref_no, in_out, to_char(c_dt,'mm/dd/yyyy hh24:mi'), transaction_no
from api_Scale
where c_dt between trunc(sysdate) +(6/24) and (trunc(sysdate) +(6/24))-1
/

Let us know your findings/results.

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 or risk. The cost will be your freedoms and your liberty.”

 

[jdobyns]

Thank you so much for the quick answer! The only problem I have now is the query does not return any rows. I checked the table and there is date in there from 6am this morning to 6am yesterday morning. Is there something else I am not doing correctly?

 

[jdobyns]

I can not figure out how to edit the above post, I apologize. I made the following changes:

select tag_ref_no, in_out, to_char(c_dt,'mm/dd/yyyy hh24:mi'), transaction_no
from api_Scale
where c_dt between trunc(sysdate) +(6/24) -1 and (trunc(sysdate) +(6/24))

and I got all the records from 8:50 am to 8:50 today. What am I doing wrong to indicate that time instead of 6am to 6am?

 

[SantaMufasa]

JDobyns,

That is a bit strange...Is there a possible Timezone issue with your data? When you run the following SQL code, are your results the same as mine?:

select to_char(trunc(sysdate) +(6/24) -1,'yyyy-mm-dd hh24:mi:ss') Yesterday_Morning
      ,to_char(trunc(sysdate) +(6/24),'yyyy-mm-dd hh24:mi:ss') This_Morning
  from dual;

YESTERDAY_MORNING   THIS_MORNING
------------------- -------------------
2009-02-19 06:00:00 2009-02-20 06:00:00

1 row selected.

Let us know.

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 or risk. The cost will be your freedoms and your liberty.”

 

[jdobyns]

SQL> select to_char(trunc(sysdate) +(6/24) -1,'yyyy-mm-dd hh24:mi:ss') Yesterday_Morning
  2        ,to_char(trunc(sysdate) +(6/24),'yyyy-mm-dd hh24:mi:ss') This_Morning
  3    from dual;

YESTERDAY_MORNING   THIS_MORNING
------------------- -------------------
2009-02-19 06:00:00 2009-02-20 06:00:00

I got the same output as you did. I found out something in testing, I can not go backpast 9am, however I go go forward with +10/24 etc.

 

[SantaMufasa]

I can not go backpast 9am, however I go go forward with +10/24 etc.

That's positively bizarre !


Would you please post code that exhibits the code-behavior anomaly?

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 or risk. The cost will be your freedoms and your liberty.”

 

[jdobyns]

select tag_ref_no, in_out, to_char(c_dt,'mm/dd/yyyy hh:mi:ss'), transaction_no
from api_Scale
where c_dt between trunc(sysdate) +(6/24) -1 and (trunc(sysdate) +(6/24))

 

[SantaMufasa]

This still smells like timezone issues. By any chance, are the non-compliant rows ones that originated several timezones distant?

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 or risk. The cost will be your freedoms and your liberty.”

 

[jdobyns]

The server only serves information in EST since it was built, we do not have any offices that require any other Time Zones. I hope I am answering your question. I am curious because I can go forward but not backwards. If I put in any value 1/24,2/24,3/24 etc, I always get 8:50, if I go forward as in 10/24, 11/24, 12/24, the times show up correctly 10,11,12.

 

[SantaMufasa]

From a troubleshooting-assistance standpoint, without your data, I can be only a cheerleader for your rather than offer substantive help in isolating the problem.

In my 20 years using Oracle, I have never seen the behavior that you are seeing. I'd really like to identify what is happening to you.

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 or risk. The cost will be your freedoms and your liberty.”

 

[karluk]

Are you absolutely certain that your table actually contains any rows with c_dt between 6:00 and 8:50 yesterday? If there are simply no rows in the interval, that would explain why your query doesn't return anything before 8:50.

 

[jdobyns]

Here is the output from

select tag_ref_no, to_char(c_dt,'MM/DD/YYYY, hh:mm:ss') from api_Scale
where c_dt >= sysdate -2

 

[SantaMufasa]

JDobyns,

I seem to be receiving rational results, using your data and reasonable variants of your code:

SQL> col x heading "Number of rows from|your original Query" format a20
SQL> select to_char(count(*)) x
  2  from api_Scale
  3  where c_dt between (trunc(sysdate) +(6/24))-1 and trunc(sysdate) +(6/24)
  4  /

Number of rows from
your original Query
--------------------
107
SQL> 
SQL> col x heading "Earliest Date/Time|Later than|Yesterday @ 6am" format a20
SQL> select to_char(c_dt,'yyyy-mm-dd hh24.mi.ss') x
  2    from api_scale
  3   where c_dt =
  4  (select min(c_dt) from api_scale
  5    where c_dt >= trunc(sysdate) +(6/24) -1)
  6  /

Earliest Date/Time
Later than
Yesterday @ 6am
--------------------
2009-02-19 06.02.22
SQL> 
SQL> col x heading "Latest Date/Time|Earlier than|Yesterday @ 6am" format a20
SQL> select to_char(c_dt,'yyyy-mm-dd hh24.mi.ss') x
  2    from api_scale
  3   where c_dt =
  4  (select max(c_dt) from api_scale
  5    where c_dt < trunc(sysdate) +(6/24) -1)
  6  /

Latest Date/Time
Earlier than
Yesterday @ 6am
--------------------
2009-02-19 05.02.52
SQL> 
SQL> col x heading "Earliest Date/Time|Later than|Today @ 6am" format a20
SQL> select to_char(c_dt,'yyyy-mm-dd hh24.mi.ss') x
  2    from api_scale
  3   where c_dt =
  4  (select min(c_dt) from api_scale
  5    where c_dt >= trunc(sysdate) +(6/24))
  6  /

Earliest Date/Time
Later than
Today @ 6am
--------------------
2009-02-20 06.02.08
SQL> 
SQL> col x heading "Latest Date/Time|Earlier than|Today @ 6am" format a20
SQL> select to_char(c_dt,'yyyy-mm-dd hh24.mi.ss') x
  2    from api_scale
  3   where c_dt =
  4  (select max(c_dt) from api_scale
  5    where c_dt < trunc(sysdate) +(6/24))
  6  /

Latest Date/Time
Earlier than
Today @ 6am
--------------------
2009-02-20 05.02.37

What results do you receive using the code that I use, above?

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 or risk. The cost will be your freedoms and your liberty.”

 

[jdobyns]

I get the same results you do.

 

[SantaMufasa]

Then the next step is for you to compare the code that produces the results, above, to the code that you believe produces incorrect/anomalous results.

If you can post code that you believe produces bad results, then I'm happy to help troubleshoot.


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 or risk. The cost will be your freedoms and your liberty.”

 

[jdobyns]

Here is the code that will not go back past 8:50 am

select tag_ref_no, in_out, to_char(c_dt,'mm/dd/yyyy hh:mi:ss'), transaction_no
from api_Scale
where c_dt between trunc(sysdate) +(6/24) -1 and (trunc(sysdate) +(6/24))

 

[Turkbear]

Hi,
Just a thought, would another parens help the parser:

select tag_ref_no, in_out, to_char(c_dt,'mm/dd/yyyy hh:mi:ss'), transaction_no
from api_Scale
where c_dt between
((trunc(sysdate) + (6/24)) -1 ) and (trunc(sysdate) +(6/24))




To Paraphrase:"The Help you get is proportional to the Help you give.."