Re: Distinct not working when convert DATE using TO_CHAR

"SunScreen" <alampro_at_yahoo.com> wrote in message news:1135090948.318228.16790_at_g14g2000cwa.googlegroups.com...
> Hi all,
>
> First I used this query:
> SELECT DISTINCT Field1, Field2 FROM Table;
> The results was distinct records. So far so good. Note that Field2 is
> DATE datatype.
>
> Then I used this one:
> SELECT DISTINCT Field1, to_char(Field2, 'MM/DD/YYYY HH24:MI:SS') "Date"
> FROM Table;
> The result was all records, that is not distinct records.
>
> Any thoughts?
>
> Thanks in advance
> SunScreen
>
>
>
> keywords: DISTNCT; SELECT; TO_CHAR; DATE; DATATYPE
>

what makes you think they are not distinct? do you have any sample output?

i'm guessing that without the to_char your default format mask (NLS_DATE_FORMAT) does not include seconds - or may not even include time, and when you supply an explicit format mask you've added 'precision' that changes the basis for the distinct comparison

note also that the DISTINCT keyword applies to the column and expression list you provide, not to the whole record

++ mcs Received on Tue Dec 20 2005 - 09:24:55 CST