I think this should bring different results:
To_date('06/02/2010 06:00:00','dd/mm/yyyy hh24:mi:ss') and
To_date('24/11/2006 06:00:00','dd/mm/yyyy hh24:mi:ss');
To_date('06/02/2006 06:00:00','dd/mm/yyyy hh24:mi:ss') and
To_date('24/11/2010 06:00:00','dd/mm/yyyy hh24:mi:ss');
I would use the second one.
>>> "Joel" <oracledba-ezmlmshield-x13044598.[Email address protected] 11/2/2006 9:07:04 AM >>>
Format picture is talking about the description of the date. Meaning
you are not using correct syntax. You cannot use "Between '06/02/2010
06:00:00' and '24/11/2006 06:00:00';"
You need the to_date, to_char on each side of the and like Stephen
demonstrates.
Joel Patterson
Database Administrator
joel.[Email address protected]
x72546
904 727-2546
-----Original Message-----
From: Weston Stephen
[mailto:oracledba-ezmlmshield-x30301948.[Email address protected]
Sent: Thursday, November 02, 2006 4:59 AM
To: LazyDBA Discussion
Subject: RE: sql question
The column will probably not be of the format '06/02/2010 06:00:00' but
is most likely to be of the format DATE.
The date is stored internally as a number representing the date and the
time portions. It's up to you when you want to use that date to convert
it into a different format.
So in your example you would want to put
select msg_datebegin
from msg
where msg_datebegin between
To_date('06/02/2010 06:00:00','dd/mm/yyyy hh24:mi:ss') and
To_date('24/11/2006 06:00:00','dd/mm/yyyy hh24:mi:ss');
I think if you specify the date just as a character string (without the
to_date) then you can only specify the date and not the time - although
I'm not sure how this is affected by the NLS_DATE_FORMAT. Try some
experiments!
Stephen Weston
-----Original Message-----
From: David NGUYEN
[mailto:oracledba-ezmlmshield-x44407666.[Email address protected]
Sent: 02 November 2006 08:38
To: LazyDBA Discussion
Subject: sql question
hello experts
please have a look of that:
select msg_datebegin from msg where msg_datebegin between
'06/02/2010 06:00:00' and '24/11/2006 06:00:00';
ORA-01830: date format picture ends before converting entire input
string
the msg_datebegin column is under format '06/02/2010 06:00:00'
the error message is like the first part of the format was converted
into
a valid date but not the second part.
anyone has a idea to correct this issue..?
thanks in advance
david..
(See attached file: C.htm)
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Oracle LazyDBA home page