How to convert functions in Oracle time applications

This article mainly introduces the relationship between the Conversion Function in the actual application of Oracle time and the actual operation of date, we all know that the greatest correlation between the Conversion Function and the actual date operation is two convertible functions, namely, to_date () and to_char ()

To_date () converts the character type to the date type in a certain format.

Usage: to_date ('1970-11-27 '', ''yyyy-mm-dd''). The former is a string, and the latter is a conversion date format. Note, the two must be one-to-one.

For example, to_date ('2017-11-27 13:34:43 '', ''yyyy-mm-dd hh24: mi: s'') will obtain the specific Oracle time.

Multiple date formats:

YYYY: Year in four bits

YYY, YY, Y: the last three digits, two digits, or one digit of the year. The default value is the current century.

MM: 01 ~ 12 month number

MONTH: The MONTH, which contains nine characters. Fill the MONTH with spaces on the right.

MON: the abbreviation of A Three-character month

WW: week of the year

D: The day of the week

DD: The day of the month.

DDD: The day of the year

DAY: The full name of the DAY, expressed in nine characters, and filled with spaces on the right

HH, HH12: the hour of the day, 12 hexadecimal notation

HH24: The hour in a day. The value ranges from 00 ~ 23

MI: minute in an hour

SS: seconds in one minute

SSSS: the number of seconds since midnight

To_char (): Convert the date to a certain format to the character type

 
 

  
  
SQL> select to_char(sysdate,''yyyy-mm-dd hh24:mi:ss'') time from dual;  
  
  
TIME  
  
  
2004-10-08 15:22:58 
 
 

Converts the current Oracle time to the character type in yyyy-mm-dd hh24: mi: ss format.

Processing date in oracle

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 Oracle time range in the 12-hour format is: 1:00:00-12:59:59 ....

[ZT] Date and ?? Letter ???

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  
 
 

Set the date language

 
 

  
  
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

Q: Find the number of days between and except Monday and seven.

Call DBMS_UTILITY.GET_TIME to subtract the result (1/100 seconds instead of milliseconds ).

9.

 
 

  
  
select months_between(to_date(''01-31-1999'',''MM-DD-YYYY''),   
  
  
to_date(''12-31-1998'',''MM-DD-YYYY'')) "MONTHS" FROM DUAL;   
  
  
1  
  
  
select months_between(to_date(''02-01-1999'',''MM-DD-YYYY''),   
  
  
to_date(''12-31-1998'',''MM-DD-YYYY'')) "MONTHS" FROM DUAL;  
  
  
1.03225806451613   
  
  
 
 
 

10. Next_day usage

 
 

  
  
Next_day(date, day)  
  
  
Monday-Sunday, for format code DAY   
  
  
Mon-Sun, for format code DY   
  
  
1-7, for format code D  
 
 

The above content is an introduction to Oracle time. I hope you will get some benefits.

Article by: http://www.programbbs.com/doc/class10-3.htm