By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,850 Members | 1,005 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,850 IT Pros & Developers. It's quick & easy.

Oracle SQL query by date

P: n/a
vnl
I'm trying to run a SQL query but can't find any records when trying to
select a certain date. Here's the sql:

SELECT field 1, field2, date_and_time,
FROM table1
WHERE date_and_time = '01-SEP-02'

I'm getting no results. The date_and_time field is formatted like this:

2002-SEP-02 00:01:04

When I run a range, the results show that records do occur on the single
date that I am looking for:

SELECT field 1, field2, date_and_time,
FROM table1
WHERE date_and_time >= '01-SEP-02' and date_and_time <= '01-DEC-02'

I'm wondering whether the problem may have something to do with the date
field containing both the date and time. Any suggestions?

Thanks.
Jul 19 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a

"vnl" <vn****@vnl999.invalid> wrote in message
news:Xn********************@216.196.97.131...
I'm trying to run a SQL query but can't find any records when trying to
select a certain date. Here's the sql:

SELECT field 1, field2, date_and_time,
FROM table1
WHERE date_and_time = '01-SEP-02'

I'm getting no results. The date_and_time field is formatted like this:

2002-SEP-02 00:01:04

When I run a range, the results show that records do occur on the single
date that I am looking for:

SELECT field 1, field2, date_and_time,
FROM table1
WHERE date_and_time >= '01-SEP-02' and date_and_time <= '01-DEC-02'

I'm wondering whether the problem may have something to do with the date
field containing both the date and time. Any suggestions?

Thanks.

You are making the mistake of comparing a string to a date. Compare a date
to a date. '01-SEP-02' is a string , date_and_time is a date. Do it like:
SELECT field 1, field2, date_and_time,
FROM table1
WHERE date_and_time >=to_date( '01-SEP-02','dd-mmm-yy') and date_and_time
<= to_date('01-DEC-02','dd-mmm-yy');

You should really use 4 didgit years unless you really mean the year 2. So
it should be:
SELECT field 1, field2, date_and_time,
FROM table1
WHERE date_and_time >=to_date( '01-SEP-2002','dd-mmm-yyyy') and
date_and_time <= to_date('01-DEC-2002','dd-mmm-yyyy');

Jim
Jul 19 '05 #2

P: n/a
vnl <vn****@vnl999.invalid> wrote in message news:<Xn********************@216.196.97.131>...
I'm trying to run a SQL query but can't find any records when trying to
select a certain date. Here's the sql:

SELECT field 1, field2, date_and_time,
FROM table1
WHERE date_and_time = '01-SEP-02'

I'm getting no results. The date_and_time field is formatted like this:

2002-SEP-02 00:01:04

And here lies the problem. You date is not equal to '01-SEP-02', it has
time component as well. You can either include time in your query
condition:

WHERE date_and_time = to_date('2002-SEP-02 00:01:04', 'YYYY-MON-DD HH24:MI:SS')

or use trunc function to truncate date before comparing it to the constant:

WHERE trunc(date_and_time) = '01-SEP-02'

When I run a range, the results show that records do occur on the single
date that I am looking for:

SELECT field 1, field2, date_and_time,
FROM table1
WHERE date_and_time >= '01-SEP-02' and date_and_time <= '01-DEC-02'

I'm wondering whether the problem may have something to do with the date
field containing both the date and time. Any suggestions?

Thanks.

Jul 19 '05 #3

P: n/a
vnl
af******@yahoo.com (Alex Filonov) wrote in
news:33**************************@posting.google.c om:
vnl <vn****@vnl999.invalid> wrote in message
news:<Xn********************@216.196.97.131>...
I'm trying to run a SQL query but can't find any records when trying
to select a certain date. Here's the sql:

SELECT field 1, field2, date_and_time,
FROM table1
WHERE date_and_time = '01-SEP-02'

I'm getting no results. The date_and_time field is formatted like
this:

2002-SEP-02 00:01:04


And here lies the problem. You date is not equal to '01-SEP-02', it
has time component as well. You can either include time in your query
condition:

WHERE date_and_time = to_date('2002-SEP-02 00:01:04', 'YYYY-MON-DD
HH24:MI:SS')

or use trunc function to truncate date before comparing it to the
constant:

WHERE trunc(date_and_time) = '01-SEP-02'

When I run a range, the results show that records do occur on the
single date that I am looking for:

SELECT field 1, field2, date_and_time,
FROM table1
WHERE date_and_time >= '01-SEP-02' and date_and_time <= '01-DEC-02'

I'm wondering whether the problem may have something to do with the
date field containing both the date and time. Any suggestions?

Thanks.


Thanks everyone. The field did turn out to be a "date" type field.

I was eventually able to get it to work by using the following format:

SELECT field1, field2, date_and_time,
FROM table1
WHERE TRUNC(date_and_time)=TO_DATE('31-dec-2002','dd-MON-yyyy')

What was weird was that I was getting different results in the
date_and_time field depending on whether I was running the SQL in Toad,
Oracle's SQL program (forgot name), and Crystal Reports SQL Designer. Two
showed both the date and time, the other showed just the date while
running the same SQL query. It got even worse as I tried to import the
data into Excel and Access which added further formatting decisions.

I'm still working on getting the SQL query to remove the time entirely so
that I will just have the date in that field.

Thanks.
Jul 19 '05 #4

P: n/a

"vnl" <vn****@vnl999.invalid> wrote in message
news:Xn********************@216.196.97.131...
| af******@yahoo.com (Alex Filonov) wrote in
| news:33**************************@posting.google.c om:
|
| > vnl <vn****@vnl999.invalid> wrote in message
| > news:<Xn********************@216.196.97.131>...
| >> I'm trying to run a SQL query but can't find any records when trying
| >> to select a certain date. Here's the sql:
| >>
| >> SELECT field 1, field2, date_and_time,
| >> FROM table1
| >> WHERE date_and_time = '01-SEP-02'
| >>
| >> I'm getting no results. The date_and_time field is formatted like
| >> this:
| >>
| >> 2002-SEP-02 00:01:04
| >>
| >
| > And here lies the problem. You date is not equal to '01-SEP-02', it
| > has time component as well. You can either include time in your query
| > condition:
| >
| > WHERE date_and_time = to_date('2002-SEP-02 00:01:04', 'YYYY-MON-DD
| > HH24:MI:SS')
| >
| > or use trunc function to truncate date before comparing it to the
| > constant:
| >
| > WHERE trunc(date_and_time) = '01-SEP-02'
| >
| >
| >> When I run a range, the results show that records do occur on the
| >> single date that I am looking for:
| >>
| >> SELECT field 1, field2, date_and_time,
| >> FROM table1
| >> WHERE date_and_time >= '01-SEP-02' and date_and_time <= '01-DEC-02'
| >>
| >> I'm wondering whether the problem may have something to do with the
| >> date field containing both the date and time. Any suggestions?
| >>
| >> Thanks.
|
| Thanks everyone. The field did turn out to be a "date" type field.
|
| I was eventually able to get it to work by using the following format:
|
| SELECT field1, field2, date_and_time,
| FROM table1
| WHERE TRUNC(date_and_time)=TO_DATE('31-dec-2002','dd-MON-yyyy')
|
| What was weird was that I was getting different results in the
| date_and_time field depending on whether I was running the SQL in Toad,
| Oracle's SQL program (forgot name), and Crystal Reports SQL Designer. Two
| showed both the date and time, the other showed just the date while
| running the same SQL query. It got even worse as I tried to import the
| data into Excel and Access which added further formatting decisions.
|
| I'm still working on getting the SQL query to remove the time entirely so
| that I will just have the date in that field.
|
| Thanks.
|
|

you're not really getting different results, the different tools are
displaying the results differently

oracle date columns are stared in an internal 7 byte binary format which is
not directly displayable, but always must be converted to a character format
by any tool that is attempting to display dates -- some tools, like TOAD,
choose their on date/time format for converting the data, others, like
SQL*Plus pick up the default format for the session, which is usually
DD-MON-RR

regarding working on removing the time entirely -- that's the better use of
the TRUNC function, in your select list. if you get in the habit of using
TRUNC in the WHERE clause, you may well end up writing poor some very poorly
performing code once you start working with production tables, since using
an expression on a column in the WHERE clause will prevent Oracle from using
any available index on that column, unless the index is a function-based
index (there are other considerations as to whether or not oracle will user
an index, but this is a typical performance error)

try rewriting the query so you don't have use TRUNC in the where clause --
this usually involves using a BETWEEN expression or a >= & < pair of
expressions; or, make sure you understand function based indexes

++ mcs
Jul 19 '05 #5

P: n/a
vnl
"Mark C. Stock" <mcstockX@Xenquery .com> wrote in
news:KK********************@comcast.com:

"vnl" <vn****@vnl999.invalid> wrote in message
news:Xn********************@216.196.97.131...
| af******@yahoo.com (Alex Filonov) wrote in
| news:33**************************@posting.google.c om:
|
| > vnl <vn****@vnl999.invalid> wrote in message
| > news:<Xn********************@216.196.97.131>...
| >> I'm trying to run a SQL query but can't find any records when
| >> trying to select a certain date. Here's the sql:
| >>
| >> SELECT field 1, field2, date_and_time,
| >> FROM table1
| >> WHERE date_and_time = '01-SEP-02'
| >>
| >> I'm getting no results. The date_and_time field is formatted like
| >> this:
| >>
| >> 2002-SEP-02 00:01:04
| >>
| >
| > And here lies the problem. You date is not equal to '01-SEP-02', it
| > has time component as well. You can either include time in your
| > query condition:
| >
| > WHERE date_and_time = to_date('2002-SEP-02 00:01:04', 'YYYY-MON-DD
| > HH24:MI:SS')
| >
| > or use trunc function to truncate date before comparing it to the
| > constant:
| >
| > WHERE trunc(date_and_time) = '01-SEP-02'
| >
| >
| >> When I run a range, the results show that records do occur on the
| >> single date that I am looking for:
| >>
| >> SELECT field 1, field2, date_and_time,
| >> FROM table1
| >> WHERE date_and_time >= '01-SEP-02' and date_and_time <=
| >> '01-DEC-02'
| >>
| >> I'm wondering whether the problem may have something to do with
| >> the date field containing both the date and time. Any suggestions?
| >>
| >> Thanks.
|
| Thanks everyone. The field did turn out to be a "date" type field.
|
| I was eventually able to get it to work by using the following
| format:
|
| SELECT field1, field2, date_and_time,
| FROM table1
| WHERE TRUNC(date_and_time)=TO_DATE('31-dec-2002','dd-MON-yyyy')
|
| What was weird was that I was getting different results in the
| date_and_time field depending on whether I was running the SQL in
| Toad, Oracle's SQL program (forgot name), and Crystal Reports SQL
| Designer. Two showed both the date and time, the other showed just
| the date while running the same SQL query. It got even worse as I
| tried to import the data into Excel and Access which added further
| formatting decisions.
|
| I'm still working on getting the SQL query to remove the time
| entirely so that I will just have the date in that field.
|
| Thanks.
|
|

you're not really getting different results, the different tools are
displaying the results differently

oracle date columns are stared in an internal 7 byte binary format
which is not directly displayable, but always must be converted to a
character format by any tool that is attempting to display dates --
some tools, like TOAD, choose their on date/time format for converting
the data, others, like SQL*Plus pick up the default format for the
session, which is usually DD-MON-RR

regarding working on removing the time entirely -- that's the better
use of the TRUNC function, in your select list. if you get in the
habit of using TRUNC in the WHERE clause, you may well end up writing
poor some very poorly performing code once you start working with
production tables, since using an expression on a column in the WHERE
clause will prevent Oracle from using any available index on that
column, unless the index is a function-based index (there are other
considerations as to whether or not oracle will user an index, but
this is a typical performance error)

try rewriting the query so you don't have use TRUNC in the where
clause -- this usually involves using a BETWEEN expression or a >= & <
pair of expressions; or, make sure you understand function based
indexes

++ mcs


Would this be the correct format?:

SELECT field1, field2, TRUNC(date_and_time),
FROM table1
WHERE date_and_time=TO_DATE('31-dec-2002','dd-MON-yyyy')
Thanks.
Jul 19 '05 #6

P: n/a

"vnl" <vn****@vnl999.invalid> wrote in message
news:Xn********************@216.196.97.131...
| "Mark C. Stock" <mcstockX@Xenquery .com> wrote in
| news:KK********************@comcast.com:
|
| >
| > "vnl" <vn****@vnl999.invalid> wrote in message
| > news:Xn********************@216.196.97.131...
| >| af******@yahoo.com (Alex Filonov) wrote in
| >| news:33**************************@posting.google.c om:
| >|
| >| > vnl <vn****@vnl999.invalid> wrote in message
| >| > news:<Xn********************@216.196.97.131>...
| >| >> I'm trying to run a SQL query but can't find any records when
| >| >> trying to select a certain date. Here's the sql:
| >| >>
| >| >> SELECT field 1, field2, date_and_time,
| >| >> FROM table1
| >| >> WHERE date_and_time = '01-SEP-02'
| >| >>
| >| >> I'm getting no results. The date_and_time field is formatted like
| >| >> this:
| >| >>
| >| >> 2002-SEP-02 00:01:04
| >| >>
| >| >
| >| > And here lies the problem. You date is not equal to '01-SEP-02', it
| >| > has time component as well. You can either include time in your
| >| > query condition:
| >| >
| >| > WHERE date_and_time = to_date('2002-SEP-02 00:01:04', 'YYYY-MON-DD
| >| > HH24:MI:SS')
| >| >
| >| > or use trunc function to truncate date before comparing it to the
| >| > constant:
| >| >
| >| > WHERE trunc(date_and_time) = '01-SEP-02'
| >| >
| >| >
| >| >> When I run a range, the results show that records do occur on the
| >| >> single date that I am looking for:
| >| >>
| >| >> SELECT field 1, field2, date_and_time,
| >| >> FROM table1
| >| >> WHERE date_and_time >= '01-SEP-02' and date_and_time <=
| >| >> '01-DEC-02'
| >| >>
| >| >> I'm wondering whether the problem may have something to do with
| >| >> the date field containing both the date and time. Any suggestions?
| >| >>
| >| >> Thanks.
| >|
| >| Thanks everyone. The field did turn out to be a "date" type field.
| >|
| >| I was eventually able to get it to work by using the following
| >| format:
| >|
| >| SELECT field1, field2, date_and_time,
| >| FROM table1
| >| WHERE TRUNC(date_and_time)=TO_DATE('31-dec-2002','dd-MON-yyyy')
| >|
| >| What was weird was that I was getting different results in the
| >| date_and_time field depending on whether I was running the SQL in
| >| Toad, Oracle's SQL program (forgot name), and Crystal Reports SQL
| >| Designer. Two showed both the date and time, the other showed just
| >| the date while running the same SQL query. It got even worse as I
| >| tried to import the data into Excel and Access which added further
| >| formatting decisions.
| >|
| >| I'm still working on getting the SQL query to remove the time
| >| entirely so that I will just have the date in that field.
| >|
| >| Thanks.
| >|
| >|
| >
| > you're not really getting different results, the different tools are
| > displaying the results differently
| >
| > oracle date columns are stared in an internal 7 byte binary format
| > which is not directly displayable, but always must be converted to a
| > character format by any tool that is attempting to display dates --
| > some tools, like TOAD, choose their on date/time format for converting
| > the data, others, like SQL*Plus pick up the default format for the
| > session, which is usually DD-MON-RR
| >
| > regarding working on removing the time entirely -- that's the better
| > use of the TRUNC function, in your select list. if you get in the
| > habit of using TRUNC in the WHERE clause, you may well end up writing
| > poor some very poorly performing code once you start working with
| > production tables, since using an expression on a column in the WHERE
| > clause will prevent Oracle from using any available index on that
| > column, unless the index is a function-based index (there are other
| > considerations as to whether or not oracle will user an index, but
| > this is a typical performance error)
| >
| > try rewriting the query so you don't have use TRUNC in the where
| > clause -- this usually involves using a BETWEEN expression or a >= & <
| > pair of expressions; or, make sure you understand function based
| > indexes
| >
| > ++ mcs
| >
|
| Would this be the correct format?:
|
| SELECT field1, field2, TRUNC(date_and_time),
| FROM table1
| WHERE date_and_time=TO_DATE('31-dec-2002','dd-MON-yyyy')
|
|
| Thanks.

in the select list, yes
but your where clause will only find rows for 12/31/02 that have no time
element stored in the date_and_time column
look at the between operator or look into using a '>=' along with a '<'
operator

++ mcs
Jul 19 '05 #7

P: n/a
vnl
"Mark C. Stock" <mcstockX@Xenquery .com> wrote in
news:Ob********************@comcast.com:

"vnl" <vn****@vnl999.invalid> wrote in message
news:Xn********************@216.196.97.131...
| "Mark C. Stock" <mcstockX@Xenquery .com> wrote in
| news:KK********************@comcast.com:
|
| >
| > "vnl" <vn****@vnl999.invalid> wrote in message
| > news:Xn********************@216.196.97.131...
| >| af******@yahoo.com (Alex Filonov) wrote in
| >| news:33**************************@posting.google.c om:
| >|
| >| > vnl <vn****@vnl999.invalid> wrote in message
| >| > news:<Xn********************@216.196.97.131>...
| >| >> I'm trying to run a SQL query but can't find any records when
| >| >> trying to select a certain date. Here's the sql:
| >| >>
| >| >> SELECT field 1, field2, date_and_time,
| >| >> FROM table1
| >| >> WHERE date_and_time = '01-SEP-02'
| >| >>
| >| >> I'm getting no results. The date_and_time field is formatted like| >| >> this:
| >| >>
| >| >> 2002-SEP-02 00:01:04
| >| >>
| >| >
| >| > And here lies the problem. You date is not equal to '01-SEP-02', it| >| > has time component as well. You can either include time in your
| >| > query condition:
| >| >
| >| > WHERE date_and_time = to_date('2002-SEP-02 00:01:04', 'YYYY-MON- DD| >| > HH24:MI:SS')
| >| >
| >| > or use trunc function to truncate date before comparing it to the
| >| > constant:
| >| >
| >| > WHERE trunc(date_and_time) = '01-SEP-02'
| >| >
| >| >
| >| >> When I run a range, the results show that records do occur on the| >| >> single date that I am looking for:
| >| >>
| >| >> SELECT field 1, field2, date_and_time,
| >| >> FROM table1
| >| >> WHERE date_and_time >= '01-SEP-02' and date_and_time <=
| >| >> '01-DEC-02'
| >| >>
| >| >> I'm wondering whether the problem may have something to do with
| >| >> the date field containing both the date and time. Any suggestions?| >| >>
| >| >> Thanks.
| >|
| >| Thanks everyone. The field did turn out to be a "date" type field.
| >|
| >| I was eventually able to get it to work by using the following
| >| format:
| >|
| >| SELECT field1, field2, date_and_time,
| >| FROM table1
| >| WHERE TRUNC(date_and_time)=TO_DATE('31-dec-2002','dd-MON-yyyy')
| >|
| >| What was weird was that I was getting different results in the
| >| date_and_time field depending on whether I was running the SQL in
| >| Toad, Oracle's SQL program (forgot name), and Crystal Reports SQL
| >| Designer. Two showed both the date and time, the other showed just
| >| the date while running the same SQL query. It got even worse as I
| >| tried to import the data into Excel and Access which added further
| >| formatting decisions.
| >|
| >| I'm still working on getting the SQL query to remove the time
| >| entirely so that I will just have the date in that field.
| >|
| >| Thanks.
| >|
| >|
| >
| > you're not really getting different results, the different tools are
| > displaying the results differently
| >
| > oracle date columns are stared in an internal 7 byte binary format
| > which is not directly displayable, but always must be converted to a
| > character format by any tool that is attempting to display dates --
| > some tools, like TOAD, choose their on date/time format for converting| > the data, others, like SQL*Plus pick up the default format for the
| > session, which is usually DD-MON-RR
| >
| > regarding working on removing the time entirely -- that's the better
| > use of the TRUNC function, in your select list. if you get in the
| > habit of using TRUNC in the WHERE clause, you may well end up writing| > poor some very poorly performing code once you start working with
| > production tables, since using an expression on a column in the WHERE| > clause will prevent Oracle from using any available index on that
| > column, unless the index is a function-based index (there are other
| > considerations as to whether or not oracle will user an index, but
| > this is a typical performance error)
| >
| > try rewriting the query so you don't have use TRUNC in the where
| > clause -- this usually involves using a BETWEEN expression or a >= & <| > pair of expressions; or, make sure you understand function based
| > indexes
| >
| > ++ mcs
| >
|
| Would this be the correct format?:
|
| SELECT field1, field2, TRUNC(date_and_time),
| FROM table1
| WHERE date_and_time=TO_DATE('31-dec-2002','dd-MON-yyyy')
|
|
| Thanks.

in the select list, yes
but your where clause will only find rows for 12/31/02 that have no time element stored in the date_and_time column
look at the between operator or look into using a '>=' along with a '<'
operator

++ mcs


This is the code that I was eventually able to get working. Any
suggestions about making it better?

SELECT field1, field2, TO_CHAR(date_and_time, 'DD-MON-YYYY') AS
date_entry
FROM table1
WHERE TRUNC(date_and_time)=TO_DATE('01-sep-2002', 'DD-MON-YYYY')
I tried using trunc in the select clause and was getting weird results
.... The year was coming out as "0003" instead of "2003"

Thanks.
Jul 19 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.