Oracle SQL query by date

**Jim Kennedy** · Jun 27 '08, 06:36 PM

Re: Oracle SQL query by date

"vnl" <vnl999@vnl999. invalidwrote in message
news:Xns9535C4F A974ABvnl999@21 6.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

**Alex Filonov** · Jun 27 '08, 06:37 PM

Re: Oracle SQL query by date

vnl <vnl999@vnl999. invalidwrote in message news:<Xns9535C4 FA974ABvnl999@2 16.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.

**vnl** · Jun 27 '08, 06:37 PM

Re: Oracle SQL query by date

afilonov@yahoo. com (Alex Filonov) wrote in
news:336da121.0 407300808.690de d59@posting.goo gle.com:

vnl <vnl999@vnl999. invalidwrote in message
news:<Xns9535C4 FA974ABvnl999@2 16.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.

**Mark C. Stock** · Jun 27 '08, 06:37 PM

Re: Oracle SQL query by date

"vnl" <vnl999@vnl999. invalidwrote in message
news:Xns9536A0A 6881EEvnl999@21 6.196.97.131...
| afilonov@yahoo. com (Alex Filonov) wrote in
| news:336da121.0 407300808.690de d59@posting.goo gle.com:
|
| vnl <vnl999@vnl999. invalidwrote in message
| news:<Xns9535C4 FA974ABvnl999@2 16.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

**vnl** · Jun 27 '08, 06:37 PM

Re: Oracle SQL query by date

"Mark C. Stock" <mcstockX@Xenqu ery .comwrote in
news:KKOdnRkaRK CxIJfcRVn-gQ@comcast.com:

>
"vnl" <vnl999@vnl999. invalidwrote in message
news:Xns9536A0A 6881EEvnl999@21 6.196.97.131...
>| afilonov@yahoo. com (Alex Filonov) wrote in
>| news:336da121.0 407300808.690de d59@posting.goo gle.com:
>|
>| vnl <vnl999@vnl999. invalidwrote in message
>| news:<Xns9535C4 FA974ABvnl999@2 16.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=T O_DATE('31-dec-2002','dd-MON-yyyy')

Thanks.

**Mark C. Stock** · Jun 27 '08, 06:37 PM

Re: Oracle SQL query by date

"vnl" <vnl999@vnl999. invalidwrote in message
news:Xns9536D39 16232Avnl999@21 6.196.97.131...
| "Mark C. Stock" <mcstockX@Xenqu ery .comwrote in
| news:KKOdnRkaRK CxIJfcRVn-gQ@comcast.com:
|
| >
| "vnl" <vnl999@vnl999. invalidwrote in message
| news:Xns9536A0A 6881EEvnl999@21 6.196.97.131...
| >| afilonov@yahoo. com (Alex Filonov) wrote in
| >| news:336da121.0 407300808.690de d59@posting.goo gle.com:
| >|
| >| vnl <vnl999@vnl999. invalidwrote in message
| >| news:<Xns9535C4 FA974ABvnl999@2 16.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=T O_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

**vnl** · Jun 27 '08, 06:37 PM

Re: Oracle SQL query by date

"Mark C. Stock" <mcstockX@Xenqu ery .comwrote in
news:ObCdndEMaf Kn6JbcRVn-hw@comcast.com:

>
"vnl" <vnl999@vnl999. invalidwrote in message
news:Xns9536D39 16232Avnl999@21 6.196.97.131...
>| "Mark C. Stock" <mcstockX@Xenqu ery .comwrote in
>| news:KKOdnRkaRK CxIJfcRVn-gQ@comcast.com:
>|
>| >
>| "vnl" <vnl999@vnl999. invalidwrote in message
>| news:Xns9536A0A 6881EEvnl999@21 6.196.97.131...
>| >| afilonov@yahoo. com (Alex Filonov) wrote in
>| >| news:336da121.0 407300808.690de d59@posting.goo gle.com:
>| >|
>| >| vnl <vnl999@vnl999. invalidwrote in message
>| >| news:<Xns9535C4 FA974ABvnl999@2 16.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=T O_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_an d_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.

Oracle SQL query by date

Oracle SQL query by date

Comment

Comment

Comment

Comment

Comment

Comment

Comment