Home » SQL & PL/SQL » SQL & PL/SQL » To_char date with and without format (11.2.0.1.0)
To_char date with and without format [message #682398] Thu, 22 October 2020 00:45 Go to next message
OraFerro
Messages: 393
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
);

and the result is: 01-AUC-2019

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
);
results in: 01-02-2025

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 Go to previous messageGo to next message
Michel Cadot
Messages: 67540
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 Go to previous message
Solomon Yakobson
Messages: 3006
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>
and:

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
SY.
Previous Topic: Tree order, Sum of valid values
Next Topic: Split Partition with Online / Update global indexes clause
Goto Forum:
  


Current Time: Tue Nov 24 12:11:05 CST 2020