Oracle to_char date conversion string statement sharing, oracleto_char

Oracle to_char date conversion string statement sharing, oracleto_char

1 is displayed in 12-hour format

SQL> select to_char (sysdate, 'yyyy-MM-DD HH12: MI: SS am') from dual;
TO_CHAR (SYSDATE, 'yyyy-MM-DDHH1
------------------------------
02:50:06 pm

2 In 24-hour format

SQL> select to_char (sysdate, 'yyyy-MM-DD HH24: MI: SS am') from dual;
TO_CHAR (SYSDATE, 'yyyy-MM-DDHH2
------------------------------
15:00:58 pm

3. Get the first minute of the current time.

Select to_char (Sysdate-1/21/60, 'hh24: MI: ss') from dual;
TO_CHAR (SYSDATE-1/21/60, 'hh24:
------------------------------
15:00:54

4. Get the last minute of the current time.

Select to_char (Sysdate + 1/21/60, 'hh24: MI: ss') from dual;
TO_CHAR (SYSDATE + 1/21/60, 'hh24:
------------------------------
15:03:53

5. Get the first hour of the current time.

Select to_char (Sysdate-1/24, 'hh24: MI: ss') from dual;
TO_CHAR (SYSDATE-1/24, 'hh24: MI:
------------------------------
14:03:13

6. Get the last hour of the current time.

Select to_char (Sysdate + 1/24, 'hh24: MI: ss') from dual;
TO_CHAR (SYSDATE + 1/24, 'hh24: MI:
------------------------------
16:03:32

7. Get the day after the current time

Select to_char (Sysdate + 1, 'yyyy-MM-DD ') from dual;
TO_CHAR (SYSDATE + 1, 'yyyy-MM-DD'
------------------------------

Query the first six months of the current time

Select add_months (sysdate,-6) from dual

1. conversion functions

The biggest relationship with the date operation is the two conversion functions: to_date (), to_char ()
To_date () converts the character type to the date type in a certain format:
Specific usage: to_date ('1970-11-27 ', 'yyyy-mm-dd'). The former is a string, and the latter is a conversion date format. Note that the first and second data must be in the correct format.
For example, to_date ('2017-11-27 13:34:43 ', 'yyyy-mm-dd hh24: mi: ss') will get the specific 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 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 time range in the 12-hour format is: 1:00:00-12:59:59 ....
[ZT] Total number of Date and Time Functions
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 ('1970-08-26 ', 'yyyy-mm-dd'), 'day') from dual;
Monday
Select to_char (to_date ('1970-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 ('1970-08-26 ', 'yyyy-mm-dd', 'nls _ DATE_LANGUAGE = American ')
4.
Number of days in a two-day period
Select floor (sysdate-to_date ('20140901', '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 ('20140901', 'yyyymmdd') and to_date ('20140901', '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 ('1970-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
8.
Select count (*)
From (select rownum-1 (rnum)
From all_objects
Where rownum <= to_date ('2017-02-28 ', 'yyyy-mm-dd')-to_date ('2017-
02-01 ', 'yyyy-mm-dd') + 1
)
Where to_char (to_date ('1970-02-01 ', 'yyyy-mm-dd') + rnum-1, 'd ')
Not
In ('1', '7 ')
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
11
Select to_char (sysdate, 'hh: mi: ss') TIME from all_objects
Note: the TIME of the first record is the same as that of the last record.
You can create a function to solve this problem.
Create or replace function sys_date return date is
Begin
Return sysdate;
End;
Select to_char (sys_date, 'hh: mi: ss') from all_objects;
12.
Hours
Select extract (hour from timestamp '2017-02-16 2:38:40 ') from offer
SQL> select sysdate, to_char (sysdate, 'hh') from dual;
SYSDATE TO_CHAR (SYSDATE, 'hh ')
-----------------------------------------
2003-10-13 19:35:21 07
SQL> select sysdate, to_char (sysdate, 'hh24') from dual;
SYSDATE TO_CHAR (SYSDATE, 'hh24 ')
-------------------------------------------
19:35:21 19
Obtain the year, month, and day.
13.
Processing of year, month, and day
Select older_date,
Newer_date,
Years,
Months,
Abs (
Trunc (
Newer_date-
Add_months (older_date, years * 12 + months)
)
) Days
From (select
Trunc (months_between (newer_date, older_date)/12) YEARS,
Mod (trunc (months_between (newer_date, older_date )),
12) MONTHS,
Newer_date,
Older_date
From (select hiredate older_date,
Add_months (hiredate, rownum) + rownum newer_date
From emp)
)
14.
How to deal with the uncertainty of the number of days in a month
Select to_char (add_months (last_day (sysdate) + 1,-2), 'yyyymmdd'), last_day (sysdate) from dual
16.
Find the number of days this year
Select add_months (trunc (sysdate, 'Year'), 12)-trunc (sysdate, 'Year') from dual
How to deal with a leap year
To_char (last_day (to_date ('02 '|: year, 'mmyyyy'), 'dd ')
If it is 28, it is not a leap year.
17.
Difference between yyyy and rrrr
'Yyyy99 TO_C
-----------
Yyyy 99 0099
Rrrr 99 1999
Yyyy 01 0001
Rrrr 01 2001
18. Processing in different time zones
Select to_char (NEW_TIME (sysdate, 'gmt', 'est '), 'dd/mm/yyyy hh: mi: ss'), sysdate
From dual;
19.
One interval in five seconds
Select TO_DATE (FLOOR (TO_CHAR (sysdate, 'ssss')/300) * 300, 'ssss'), TO_CHAR (sysdate, 'ssss ')
From dual
2002-11-1 9:55:00 35786
SSSSS indicates five seconds
20.
The day of the year
Select TO_CHAR (SYSDATE, 'ddd '), sysdate from dual
310 10:03:51
21. Computing hour, minute, second, millisecond
Select
Days,
A,
TRUNC (A * 24) Hours,
TRUNC (A * 24*60-60 * TRUNC (A * 24) Minutes,
TRUNC (A * 24*60*60-60 * TRUNC (A * 24*60) Seconds,
TRUNC (A * 24*60*60*100-100 * TRUNC (A * 24*60*60) mSeconds
From
(
Select
Trunc (sysdate) Days,
Sysdate-trunc (sysdate)
From dual
)
Select * from tabname
Order by decode (mode, 'fifo ', 1,-1) * to_char (rq, 'yyyymmddhh24miss ');
//
Floor (date2-date1)/365) as year
Floor (date2-date1, 365)/30) as month
Mod (date2-date1, 365), 30) as daily.
23. next_day Function
Next_day (sysdate, 6) is the next Friday from the current start. The following number is counted from Sunday.
1 2 3 4 5 6 7
October 6, 1234
Oracle has many date functions.
There are many date functions in oracle, such:
1. add_months () is used to increase or decrease the number of months from a date value.
Date_value: = add_months (date_value, number_of_months)
Example:
SQL> select add_months (sysdate, 12) "Next Year" from dual;
Next Year
----------
April 11-04
SQL> select add_months (sysdate, 112) "Last Year" from dual;
Last Year
----------
13-3-13
SQL>
2. current_date () returns the current date in the current time zone.
Date_value: = current_date
SQL> column sessiontimezone for a15
SQL> select sessiontimezone, current_date from dual;
SESSIONTIMEZONE CURRENT_DA
-------------------------
+-11-03
SQL> alter session set time_zone = '-11: 00'
2/
The session has been changed.
SQL> select sessiontimezone, current_timestamp from dual;
SESSIONTIMEZONE CURRENT_TIMESTAMP
---------------------------------------------------
--03 04.59.13.668000 pm-11:
00
SQL>
3. current_timestamp () returns the current date in the current time zone as the timestamp with time zone Data Type
Timestamp_with_time_zone_value: = current_timestamp ([timestamp_precision])
SQL> column sessiontimezone for a15
SQL> column current_timestamp format a36
SQL> select sessiontimezone, current_timestamp from dual;
SESSIONTIMEZONE CURRENT_TIMESTAMP
---------------------------------------------------
+-03 11.56.28.160000 AM + 08:
00
SQL> alter session set time_zone = '-11: 00'
2/
The session has been changed.
SQL> select sessiontimezone, current_timestamp from dual;
SESSIONTIMEZONE CURRENT_TIMESTAMP
---------------------------------------------------
--03 04.58.00.243000 pm-11:
00
SQL>
4. dbtimezone () returns the time zone
Varchar_value: = dbtimezone
SQL> select dbtimezone from dual;
DBTIME
------
-07:00
SQL>
5. extract () identifies the field value of the date or interval value
Date_value: = extract (date_field from [datetime_value | interval_value])
SQL> select extract (month from sysdate) "This Month" from dual;
This Month
----------
11
SQL> select extract (year from add_months (sysdate, 36) "3 Years Out" from dual;
3 Years Out
-----------
2006
SQL>
6. last_day () returns the date of the last day of the month that contains the date parameter.
Date_value: = last_day (date_value)
SQL> select last_day (date '2017-02-01 ') "Leap Yr? "From dual;
Leap Yr?
----------
29-2-00
SQL> select last_day (sysdate) "Last day of this month" from dual;
Last day o
----------
30-11-03
SQL>
7. localtimestamp () returns the date and time in the session.
Timestamp_value: = localtimestamp
SQL> column localtimestamp format a28
SQL> select localtimestamp from dual;
LOCALTIMESTAMP
----------------------------
13-11-03 12.09.15.433000
Afternoon
SQL> select localtimestamp, current_timestamp from dual;
LOCALTIMESTAMP CURRENT_TIMESTAMP
----------------------------------------------------------------
13-11-03 12.09.31.006000 13-11-03 12.09.31.006000 PM + 08:
00 pm
SQL> alter session set time_zone = '-11: 00 ';
The session has been changed.
SQL> select localtimestamp, to_char (sysdate, 'dd-MM-YYYY HH: MI: SS am') "SYSDATE" from dual;
LOCALTIMESTAMP SYSDATE
----------------------------------------------------
12-11-03 05.11.31.259000 13-11-2003 12:11:31 pm
Afternoon
SQL>
8. months_between () determines the number of months between two dates.
Number_value: = months_between (date_value, date_value)
SQL> select months_between (sysdate, date '2017-05-18 ') from dual;
MONTHS_BETWEEN (SYSDATE, DATE '2017-05-18 ')
----------------------------------------
389.855143
SQL> select months_between (sysdate, date '2017-01-01 ') from dual;
MONTHS_BETWEEN (SYSDATE, DATE '2017-01-01 ')
----------------------------------------
34.4035409
SQL>
9. Given a date value, next_day () returns the date value that appears for the first time on the day indicated by the second parameter (the name string of the corresponding day should be returned)
Weekly date function
1. query the first day of a week

select trunc(decode(ww, 53, to_date(yy || '3112', 'yyyyddmm'), to_date(yy || '-' || to_char(ww * 7), 'yyyy-ddd')), 'd') last_day from (select substr('2004-32', 1, 4) yy, to_number(substr('2004-32', 6)) ww from dual) select trunc(to_date(substr('2003-01',1,5)||to_char((to_number(substr('2003-01',6)))*7),'yyyy-ddd'),'d')-6 first_day from dual select min(v_date) from (select (to_date('200201','yyyymm') + rownum) v_date from all_tables where rownum < 370) where to_char(v_date,'yyyy-iw') = '2002-49' 

2. query the last day of a week

select trunc(decode(ww, 53, to_date(yy || '3112', 'yyyyddmm'), to_date(yy || '-' || to_char(ww * 7), 'yyyy-ddd')), 'd') - 6 first_day from (select substr('2004-33', 1, 4) yy, to_number(substr('2004-33', 6)) ww from dual) select trunc(to_date(substr('2003-01',1,5)||to_char((to_number(substr('2003-01',6)))*7),'yyyy-ddd'),'d') last_day from dual select max(v_date) from (select (to_date('200408','yyyymm') + rownum) v_date from all_tables where rownum < 370) where to_char(v_date,'yyyy-iw') = '2004-33' 

3. query the date of a week

select min_date, to_char(min_date,'day') day from (select to_date(substr('2004-33',1,4)||'001'+rownum-1,'yyyyddd') min_date from all_tables where rownum <= decode(mod(to_number(substr('2004-33',1,4)),4),0,366,365) union select to_date(substr('2004-33',1,4)-1|| decode(mod(to_number(substr('2004-33',1,4))-1,4),0,359,358)+rownum,'yyyyddd') min_date from all_tables where rownum <= 7 union select to_date(substr('2004-33',1,4)+1||'001'+rownum-1,'yyyyddd') min_date from all_tables where rownum <= 7 ) where to_char(min_date,'yyyy-iw') ='2004-33' 

Oracle converts a string to a date format, for example, converting string 20121021 to the date type?

-- Convert string to date format:
Select to_date ('20140901', 'yyyy-mm-dd') from dual; -- the result is 20121021
-- Convert the date format to a string:
Select to_char (sysdate, 'yyyy-mm-dd') from dual; -- the output result is '2017-11-23 '(to explain, the output result is not '', I add it here to make it clearer)

Oracle string to date format

Your string is difficult to identify directly by the database, but fortunately it is regular and can be converted according to the following method.
Assume that the table name is TBL and the field name for storing the column data is DDATE. Use the following statements to convert the date.
Select ddate,
TO_DATE (SUBSTR (DDATE, 8, 4) | DECODE (SUBSTR (DDATE, 1, 3 ),
'Feb ',
'01 ',
'Jan ',
'02 ',
'Mar ',
'03') |
LPAD (TRIM (SUBSTR (DDATE, 5, 2), 2, '0') |
LPAD (TRIM (SUBSTR (DDATE, 13, 8), 8, '0'), 'yyyy-MM-DD HH: MI: ss ')
FROM TBL
In the preceding section, you must fill in the abbreviations of the remaining nine months and the corresponding months in the DECODE section.