Date language settings in Oracle processing date

Are you confused about the actual Oracle processing date operations? If you are very interested in the actual Oracle processing date operations, you can browse the following related content. I hope you will understand the actual application operations on Oracle processing date.

Converts a number to any time format. For example, seconds: You need to convert it to days/hours.

 
 

  
  
SELECT to_char(floor(TRUNC(936000/(60*60))/24)) 
 
 

Tian '| to_char (mod (TRUNC (936000/(60*60), 24)' hour 'FROM DUAL

TO_DATE format

 
 

  
  
Day:  
  
  
dd number 12  
  
  
dy abbreviated fri  
  
  
day spelled out friday  
  
  
ddspth spelled out, ordinal twelfth  
  
  
Month:  
  
  
mm number 03  
  
  
mon abbreviated mar  
  
  
month spelled out march  
  
  
Year:  
  
  
yy two digits 98  
  
  
yyyy four digits 1998  
 
 

The time range in the 24-hour format is: 0:00:00-23:59:59 ....

The time range in the 12-hour format is: 1:00:00-12:59:59 ....

1.

Date and character conversion function usage to_date, to_char)

2.

 
 

  
  
select to_char( to_date(222,'J'),'Jsp') from dual 
 
 

Display Two Hundred Twenty-Two

3.

The day of the week.

 
 

  
  
select to_char
(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual; 
 
 

Monday

 
 

  
  
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;  
  
  
monday     
 
 

Date language settings in Oracle processing date

 
 

  
  
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN'; 
 
 

You can also

 
 

  
  
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American') 
 
 

4.

Number of days in a two-day period

 
 

  
  
select floor(sysdate - to_date('20020405','yyyymmdd')) from dual; 
 
 

5. Use a time of null

 
 

  
  
select id, active_date from table1  
  
  
UNION  
  
  
select 1, TO_DATE(null) from dual;  
 
 

Note that TO_DATE (null) is used)

6.

 
 

  
  
a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd') 
 
 

Therefore, it is not included in this range after on January 1, December 31 and before on January 1, December 1.

Therefore, when the time needs to be accurate, to_char is still necessary.

7. Date Format conflict

The input format depends on the type of the Oracle character set you installed, for example, US7ASCII. The date format is '01-Jan-01'

 
 

  
  
alter system set NLS_DATE_LANGUAGE = American 
  
  
alter session set NLS_DATE_LANGUAGE = American 
 
 

Or write it in to_date.

 
 

  
  
select to_char
(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') 
from dual; 
 
 

Note that I just mentioned NLS_DATE_LANGUAGE. Of course there are many more,

Available

 
 

  
  
select * from nls_session_parameters  
  
  
select * from V$NLS_PARAMETERS  
 
 

The above content is an introduction to the Oracle processing date. I hope you will have some gains.