Home » SQL & PL/SQL » SQL & PL/SQL » Date query
Date query [message #200279] Mon, 30 October 2006 03:40 Go to next message
rahulvasanth
Messages: 34
Registered: January 2006
Member
Hi
I have written the below query

SELECT * FROM ABC WHERE TRUNC(SYSDATE) BETWEEN TRUNC(FROM_DATE) AND TRUNC(END_DATE)

but the above query is selecting a row where FROM_DATE = '1/8/2006' and END_DATE = '9/30/2006' when the value of SYSDATE = '10/30/2006'. Please note that all values are in 'mm/dd/yyyy' format.

Why is this happening ? Am I missing anything in the query ?

Thanking in advance.

Re: Date query [message #200282 is a reply to message #200279] Mon, 30 October 2006 03:45 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Don't rely on (possible) implicit conversion between datatypes; it depends on NLS - always use TO_DATE function, for example
SELECT * FROM abc
WHERE date_column BETWEEN TO_DATE('01.08.2006', 'dd.mm.yyyy')
                      AND TO_DATE('30.09.2006', 'dd.mm.yyyy');


[EDIT]

By the way, your 'TRUNC(SYSDATE)' part of query will return all or nothing (depending on sysdate value). Is that really what you need? That's why I changed it into 'date_column'.

[Updated on: Mon, 30 October 2006 03:48]

Report message to a moderator

Re: Date query [message #200285 is a reply to message #200282] Mon, 30 October 2006 04:02 Go to previous messageGo to next message
rahulvasanth
Messages: 34
Registered: January 2006
Member
Thanks for the reply.

I tried what u have given, but it did not work, I got the following exception.

ORA-01858 : A non-numberic character was found where a numeric was expected

The columns are date type, so, will it help if I use "TO_DATE" function ? I m using the trunc function to truncate time.


Re: Date query [message #200287 is a reply to message #200285] Mon, 30 October 2006 04:11 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's a live example:
SQL> select count(*) from emp;

  COUNT(*)
----------
        14

SQL> SELECT ename, sal FROM EMP
  2  WHERE TRUNC(SYSDATE) BETWEEN TO_DATE('01.08.2006', 'dd.mm.yyyy')
  3                           AND TO_DATE('30.11.2006', 'dd.mm.yyyy');

ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
BURGLAR          1250
JONES            2975
TIGER            1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
ADAMS           99999
JAMES             950
FORD             3000
MILLER           1300

14 rows selected.
As you can see, we got all records when sysdate is between your 'start' and 'end' date.

Now, modify our 'end' date so that sysdate isn't longer between them:
SQL> l3
  3*                          AND TO_DATE('30.11.2006', 'dd.mm.yyyy')
SQL> c/30.11/30.09/
  3*                          AND TO_DATE('30.09.2006', 'dd.mm.yyyy')

SQL> l
  1  SELECT ename, sal FROM EMP
  2  WHERE TRUNC(SYSDATE) BETWEEN TO_DATE('01.08.2006', 'dd.mm.yyyy')
  3*                          AND TO_DATE('30.09.2006', 'dd.mm.yyyy')
SQL> /

no rows selected

SQL>
See?

As of ORA-01858 - you didn't correctly write the query. Post it here so that we can see it.
Re: Date query [message #200288 is a reply to message #200285] Mon, 30 October 2006 04:12 Go to previous messageGo to next message
thomaszhang
Messages: 3
Registered: October 2006
Junior Member
you needn't use TRUNC,TO_DATE is enough.
Re: Date query [message #200293 is a reply to message #200285] Mon, 30 October 2006 04:19 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Hmmm.. as it looks you want the records which are active today (trunc (sysdate)), so from_date in the table must be smaller or equal trunc(sysdate), and end_date must be bigger or equal sysdate.

In that case your first query looks about right.

can you do an
select
  to_char(sysdate,   'yyyy.mm.dd hh24:mi:ss),
  to_char(from_date, 'yyyy.mm.dd hh24:mi:ss),
  to_char(end_date,  'yyyy.mm.dd hh24:mi:ss)
FROM ABC 
 WHERE TRUNC(SYSDATE) BETWEEN TRUNC(FROM_DATE) AND TRUNC(END_DATE)


Maybe that will show something funny in the dates ?
Re: Date query [message #200297 is a reply to message #200293] Mon, 30 October 2006 04:50 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Can you try trunc with date explicitly?

TRUNC(<date>,'DD')


Please post your sql statements also.

By
Vamsi.
Re: Date query [message #200304 is a reply to message #200279] Mon, 30 October 2006 05:17 Go to previous messageGo to next message
ajaybabu.yaleti
Messages: 11
Registered: October 2006
Junior Member
Hi,

select * from tablname where to_char(dt,'dd-mon-yyyy') between
to_char(from_date,'dd-mon-yyyy') and to_char(end_date,'dd-mon-yyyy');


Regards,
Ajay
Re: Date query [message #200329 is a reply to message #200304] Mon, 30 October 2006 06:41 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
ajaybabu.yaleti wrote on Mon, 30 October 2006 05:17

select * from tablname where to_char(dt,'dd-mon-yyyy') between
to_char(from_date,'dd-mon-yyyy') and to_char(end_date,'dd-mon-yyyy');



This should error out.
Re: Date query [message #200349 is a reply to message #200279] Mon, 30 October 2006 07:47 Go to previous messageGo to next message
ajaybabu.yaleti
Messages: 11
Registered: October 2006
Junior Member
Hi,

This query was executed in oracle 9.0.1

select * from test30
where to_char(trans_dt,'dd-mon-yyyy')
between to_char(from_dt,'dd-mon-yyyy') and to_char(end_dt,'dd-mon-yyyy');

Regards,
Ajay.
Re: Date query [message #200351 is a reply to message #200349] Mon, 30 October 2006 08:11 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Ajay,
Seems ebrian's intention is to say it leads to a logical error.

Please have a look at the following. Comparing dates after changing them to characters will give wrong(I mean wrong with respective date not the characters) results.

select * from dual
where to_char(to_date('01-jan-2006'),'dd-mon-yyyy')
between to_char(to_date('01-feb-2006'),'dd-mon-yyyy') 
and to_char(to_date('02-feb-2006'),'dd-mon-yyyy');


In this case
01-feb-2006
01-jan-2006
02-feb-2006
is the order because character '2' comes next to '1' and character 'j' comes next to 'f'.

But rahulvasanth needs to give some more information to identify his issue.

By
Vamsi.
Re: Date query [message #200354 is a reply to message #200351] Mon, 30 October 2006 08:27 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
That's correct vamsi. Thanks for the clarification and for providing an example.
Previous Topic: Return a random value from a list of numbers minus a specified range
Next Topic: SQL Query Problem
Goto Forum:
  


Current Time: Sat Dec 03 05:50:10 CST 2016

Total time taken to generate the page: 0.11420 seconds