Re: simple query not so simple
- From: HansF <News.Hans@xxxxxxxxx>
- Date: Mon, 10 Oct 2005 15:28:03 GMT
On Sun, 09 Oct 2005 22:42:51 -0700, Bob interested us by writing:
> SQL=
>
> select count(*),sum(atp_amount) from fdsmastrep where trans_month =
> 10/1/2004
If that is truly your SQL statement, you have just asked to find all
entries where trans_month is 0.004990020 (10 divided by 1 divided by 2004)
Assuming the trans_month column is of type date, you want to perform a
character to date conversion before invoking the selection.
You need to understand Oracle's storage vs presentation semantics when
using dates to avoid problems as you bring to the group.
In Oracle all dates contain 'century, year, month, date, hour, minute,
second' (2 digits for each). However Oracle uses presentation rules to
display the date or accept it in a where condition (and elsewhere). In a
default installation, the presentation rule is 'display only year, month,
day using the 'DD-MON-RR' format spec', although you can adjust that for
any session using the 'alter session ' command.
To get around this, realize that the date you use in comparison is a
string - if you can read it, it's a string, not a date - so use string
semantics with quotes as appropriate. Then, convert the string to a
date, either implicitly (if your string is in the session's date format)
or explicitly (to supply the format mask) using the to_date function.
Please go to the SQL Reference manual for your version of Oracle at
http://docs.oracle.com, look at chapter 2 under datatypes for a more
formal discussion of this, and then the chapter for functions to make your
life easier.
--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** I no longer assist with top-posted newsgroup queries ***
.
- References:
- simple query not so simple
- From: Bob
- simple query not so simple
- Prev by Date: Re: Oracle buys Innobase (i.e. key bit in MySQL)
- Next by Date: Re: What do Oracle professionals think of Fabian Pascal?
- Previous by thread: Re: simple query not so simple
- Index(es):
Relevant Pages
|
