How does the mask to date?

[Rafael_Santos]

Dear gentlemen,
I know it's a very simple question, but during my studies I'vê been realizing
What masks are my Achilles tendon.
If someone has availability to teach me these damn masks, it would be very happy.

Doubt:

Select all

SELECT TO_DATE('10/10/2013 12:48:55','dd/mm/yyyy')

If I take this SELECT, the expected result should be 10/10/2013 [/ u] should?

I have already performed the change of NLS_DATE_FORMAT = 'DD / MM / YYYY HH24: MI: SS'

grateful for help gentlemen.

[gleisoncandido]

Rafael_santos alright
first of the following command:

Select all

select sysdate from dual;

and looks like this is the format of your date on the bank, Oracle will follow Sysdate's padding. Below more masks you can use:

Select all

to_date('16-Feb-09', 'DD-Mon-YY') 
to_date('02/16/09', 'MM/DD/YY') 
to_date('021609', 'MMDDYY') 
to_date('16-Feb-09', 'DD-Mon-YY HH:MI:SS')  
to_date('Feb/16/09', 'Mon/DD/YY HH:MI:SS') 
to_date('February.16.2009', 'Month.DD.YYYY HH:MI:SS')

View here the format of the dates: [[2]

Select all

AM   - AM ou PM    
CC   - Século  
D    - Dia da semana (1-7) 
DAY  - Dia da semana ('SUNDAY')  
DD   - Dia do mês (1-31) 
DDD  - Dia do ano 
DY   - Dia da semana abreviado ('SUN') 
FM   - Tira os blanks ou Zeros da esquerda 
HH   - Hora do dia (0-12) 
HH24 - Hora do dia (0-24) 
MI   - Minutos da Hora 
MM   - Mês com 2 dígitos 
MON  - Mês abreviado ('NOV') 
MONTH- Mês por extenso ('NOVEMBER') 
PM   - AM ou PM 
RR   - Ano com 2 dígitos - especial 
RRRR - Ano com 4 dígitos 
SS   - Segundos do minuto(0 - 59) 
SSSSS- Segundos do dia 
W    - Semana do Mês 
WW   - Semana do Ano 
YEAR - Ano por extenso 
YY   - Ano com 2 dígitos 
YYYY - Ano com 4 dígitos

To change in the bank definition, you must go to the Bank Startup parameter and open the inisid.ora and change or add the following line:
NLS_DATE_FORMAT = 'DD / MM / YY HH24: MI: SS '

I hope I have helped.

[DanielNN]

Hi Rafael,

Select all

TO_DATE('10/10/2013 12:48:55','dd/mm/yyyy')

It gives problem because when you go convert string (char) to date, you need to inform exactly a mask that fits the string. In your case, as you reported date and time in your string, your date mask should contain hours as well.

Select all

TO_DATE('10/10/2013 12:48:55','dd/mm/yyyy hh24:mi:ss')

The "unique" mask possible for this conversion is this.

In these cases string-> to_date needs to inform the mask precisely.

Already when you will transform date-> to_char, it is not necessary to report with this accuracy. To_char becomes only the way you want to display your date in text (opposite to_date that you need to convert to date). Internally Date is treated as if it were a number. Therefore, there are no firewas of languages, masks and so on.

Let's say you have your field date (My_Date) with the date "10/10/2013 12:48:55". You can do n forms of to_char with it, that is, you have n form of display .

Select all

TO_CHAR(my_date,'dd/mm/yyyy') = "10/10/2013" 
TO_CHAR(my_date,'dd-mm-yy') = "10-10-13" 
TO_CHAR(my_date,'RRRRMMDD') = "20131010" 

I hope I have helped.

[Rafael_Santos]

Hello Noctifero and Gleisoncendo,
Thank you very much for the explanation was excellent, the explanations were completed.
In summarizing:
The to_date mask serves the bank to choose how to perform the transformation of the char to date.
The to_char mascara is to display information.
The to_date result will always appear as standard set by the bank, if established by nls_date_format with time will appear with time even if the compatible mask is without horá the result will appear at 00:00:00.

Thanks to everyone, now return to studies!

Hugs

[Rafael_Santos]

Gentlemen only one more question.
How to show the date this way: 02/22/2013 04:32:33 pm

I performed the following procedure:

Select all

alter session set nls_date_format = 'dd/mm/yyyy hh:mi:ss am'; 
select sysdate from dual;

99] I obtained the following result:

Select all

22/02/2013 04:32:33 tarde

Does anyone know what is missing?

[rogenaro]

The problem is with the IDOMA used in the session.

You can change the language with the to_char as well:

Select all

 
select to_char( sysdate, 'dd/mm/yyyy hh:mi:ss pm', 'nls_date_language=''brazilian portuguese''' ) from dual 
union all 
select to_char( sysdate, 'dd/mm/yyyy hh:mi:ss pm', 'nls_date_language=''english''' ) from dual 

[Rafael_Santos]

Thank you Rogenaro.

Hugs to all.

[Francois.Nogueira]

I would like to thank everyone, I had the same doubts, with the help of all I managed to solve my doubts.
Thanks ...
: -O