0

I am working in Oracle 10g XE. I am having Two databases inside by Oracle SQL Developer. I am executing this Query..

SELECT SCHEDULE_ID, START_DATE, END_DATE 
  FROM SCHEDULE
 WHERE C_ID IN (5781) 
   AND START_DATE >=TO_DATE ('1/29/2012','MM/DD/YYYY') 
   AND END_DATE <=TO_DATE ('4/14/2012','MM/DD/YYYY')

In First Database, the data returned correctly. I am executing the same Query in the Second Database, but it does not returns any values. But the data is available in table.

I had executed the query

SELECT dump(START_DATE), dump(END_DATE) FROM SCHEDULE WHERE C_ID=5026

I am getting the result as, 

DUMP(START_DATE ) -> Typ=12 Len=7: 100,112,2,7,1,1,1 
DUMP(END_DATE) -> Typ=12 Len=7: 100,112,2,13,1,1,1

and an important thing is, i should not modify the query..Because its working in all the other databases...

Improve this question
14
  • 4
    Date settings won't impact this. Are you sure that your data is the same? Commented Apr 24, 2012 at 10:33
  • yes.sure..i had checked. I am having the data in table.. Commented Apr 24, 2012 at 10:36
  • I had tried like this "....TO_DATE(START_DATE) >=TO_DATE ('1/29/2012','MM/DD/YYYY') " Its working.....But its not the proper solution.... Commented Apr 24, 2012 at 10:43
  • 1
    I note that the SELECT includes C_ID IN (5781) in the WHERE clause. Try executing the following: SELECT * FROM TABLE WHERE C_ID=5781. Does that row have a date in the appropriate range? Commented Apr 24, 2012 at 11:24
  • 1
    START_DATE and END_DATE are of type DATE, right ? Commented Apr 24, 2012 at 11:25

1 Answer 1

Reset to default
4

Your statement:

SELECT dump(START_DATE), dump(END_DATE) FROM SCHEDULE WHERE C_ID=5026

I am getting the result as, 

DUMP(START_DATE ) -> Typ=12 Len=7: 100,112,2,7,1,1,1 and DUMP(END_DATE) -> Typ=12 Len=7: 100,112,2,13,1,1,1

My Comment:

100,112,2,7,1,1,1

100,112,2,13,1,1,1

This results in YYYY-MM-DD

Startdate    0012-02-07 00:00:00
Enddate      0012-02-13 00:00:00

So you're about 2000 years off.

The format of the date datatype is

byte 1 - century (excess 100)  100 - 100 = 00
byte 2 - year (excess 100)  112 - 100 = 12
byte 3 - month = 2
byte 4 - day = 7
byte 5 - hour (excess 1) 1 - 1 = 0
byte 6 - minute (excess 1) 1 - 1 = 0
byte 7 - seconds (excess 1) 1 - 1 = 0
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

The dump function show the internal representation of the date in the database is 100,112,2,7,1,1,1. This corresponds with 0012-02-07. Jesus was about 12 years old on that date. Do a select to_char(startdate,'YYYY-MM-DD') from schedule; and you'll see. So probably your data is 'wrong', at least not what you expect is to be.
@RobinHood - Your dates are in the year 12, not the year 2012 (2000 years ago). If you want the SQL statement to work, you'll need to change the data so that the dates are correct. So, for example update schedule set start_date = add_months(start_date, 12*2000), end_date = add_months(end_date, 12*2000) where c_id = 5026 will update these two rows so that the years are correct. You may well have other rows where the year is incorrect, however, so you may want to do something like update schedule set start_date = add_months(start_date,12*2000) where start_date < date '1900-01-01'
Thats right..My data is wrong.. Thanks Justin and Robert for valuble comments. I had changed the data in the database and now its working correctly...

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.