| To_char date with and without format [message #682398] |
Thu, 22 October 2020 00:45  |
 |
OraFerro
Messages: 433
Registered: July 2011
|
Senior Member |
|
|
Dear All,
A question about how character comparison and min function
I have the following 2 queries:
select min(to_char(d)) from
(
select to_date('01-08-2019','dd-mm-yyyy') d from dual
union
select to_date('01-02-2025','dd-mm-yyyy') d from dual
);
while:
select min(to_char(d,'dd-mm-yyyy')) from
(
select to_date('01-08-2019','dd-mm-yyyy') d from dual
union
select to_date('01-02-2025','dd-mm-yyyy') d from dual
);
and I need to understand the following:
- I can see that because August starts with an A it was selected before Feb which is why the to_char without formatting worked. Also I can see that because no formatting was given, server formatting was used. However I dont get what went wrong when the formatting is passed (which means that now Oracle can understand that this field is a date and which format exactly is to use).
Thanks,
Ferro
|
|
|
|
| Re: To_char date with and without format [message #682400 is a reply to message #682398] |
Thu, 22 October 2020 01:22  |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote: I can see that because August starts with an A it was selected before Feb which is why the to_char without formatting worked.
It always works; you mean it "works" (the min string is the same than the min date) but this is only by chance.
Quote:lso I can see that because no formatting was given, server formatting was used.
No this is SESSION default format that is used, not server (in fact INSTANCE one, not server one) unless no session is defined.
Quote:(which means that now Oracle can understand that this field is a date and which format exactly is to use).
No it means how YOU want to see the dates, Oracle knows it is a date as you tell it with TO_DATE but using then TO_CHAR you convert it to a string.
In both cases you compare STRINGS to get the MIN value, dates disappeared when you used TO_CHAR.
[Updated on: Thu, 22 October 2020 01:22] Report message to a moderator |
|
|
|
| Re: To_char date with and without format [message #682404 is a reply to message #682398] |
Thu, 22 October 2020 06:35 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Meditate over:
select min(to_char(d,'dd-mm-yyyy')) from
(
select to_date('01-08-2019','dd-mm-yyyy') d from dual
union
select to_date('01-02-2025','dd-mm-yyyy') d from dual
);
MIN(TO_CHA
----------
01-02-2025
SQL>
select to_char(min(d),'dd-mm-yyyy') from
(
select to_date('01-08-2019','dd-mm-yyyy') d from dual
union
select to_date('01-02-2025','dd-mm-yyyy') d from dual
);
TO_CHAR(MI
----------
01-08-2019
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
