Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: simple query not so simple
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 ***Received on Mon Oct 10 2005 - 10:28:03 CDT