Oracle To_char Date Conversion string statement sharing _oracle

1 "in 12-hour system display

Sql>select to_char (sysdate, ' yyyy-mm-dd HH12:MI:SS AM ') from dual;
To_char (sysdate, ' YYYY-MM-DDHH1
------------------------------
2007-06-29 02:50:06 pm

2 "in 24-hour system display

Sql> Select To_char (sysdate, ' yyyy-mm-dd HH24:MI:SS AM ') from dual;
To_char (sysdate, ' YYYY-MM-DDHH2
------------------------------
2007-06-29 15:00:58 pm

3 a minute before 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 a minute after getting 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 to get the current time before the hour

Select To_char (sysdate-1/24, ' HH24:MI:SS ') from dual;
To_char (sysdate-1/24, ' Hh24:mi:
------------------------------
14:03:13

6 Gets the hour after 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 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 function

The biggest relationship with date operations is two conversion functions: To_date (), To_char ()
The to_date () action converts a character type to a date type in a certain format:
Specific usage: to_date (' 2004-11-27 ', ' yyyy-mm-dd '), the former is a string, the latter is the conversion date format, note that the two should be a corresponding.
To_date (' 2004-11-27 13:34:43 ', ' yyyy-mm-dd hh24:mi:ss ') will get specific time
Multiple date formats:
YYYY: four-bit year
Yyy,yy,y: Last three-bit, two-bit, or one-digit of year, defaults to current century
Month Number of mm:01~12
MONTH: Month of nine characters, padding on right with space
MON: Month abbreviation for three-bit characters
WW: Week of the Year
D: Day of the week
DD: Day of the month
DDD: The first day of the year
Day: The full name of the days of nine characters, padded with spaces on the right
Hh,hh12: The first few hours of the day, the 12 notation
HH24: The first few hours of the day, with a value of 00~23
MI: minutes in one hour
SS: seconds in one minute
SSSS: The number of seconds since midnight
To_char (): Convert date to character type in a certain format
Sql> Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss ') time from dual;
Time
-------------------
2004-10-08 15:22:58
Convert current time to character type by YYYY-MM-DD HH24:MI:SS format
Processing Date Encyclopedia 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
24-hour format of the time range: 0:00:00-23:59:59 ....
12-hour format of the time range: 1:00:00-12:59:59 ....
[ZT] 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
Show two hundred twenty-two
3.
How many days is 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 Date language
ALTER session SET nls_date_language= ' American ';
I can do that.
To_date (' 2002-08-26 ', ' yyyy-mm-dd ', ' nls_date_language = American ')
4.
Days of two days
Select Floor (sysdate-to_date (' 20020405 ', ' YYYYMMDD ')) from dual;
5. The use of time as null
Select ID, active_date from table1
UNION
Select 1, to_date (null) from dual;
Note to use to_date (NULL)
6.
A_date between To_date (' 20011201 ', ' YYYYMMDD ') and to_date (' 20011231 ', ' YYYYMMDD ')
The December 31 is not included in this range until after 12 o'clock noon and December 1 of 12 points.
So, when time needs to be accurate, feel to_char is still necessary
7. Date format conflict issues
The format you enter depends on the type of Oracle character set you have installed, such as: Us7ascii, the date format type is: ' 01-jan-01 '
alter system Set Nls_date_language = American
Alter session Set Nls_date_language = American
or write in To_date.
Select To_char (to_date (' 2002-08-26 ', ' yyyy-mm-dd '), ' Day ', ' nls_date_language = American ') from dual;
Notice I'm just lifting up the nls_date_language, and of course there's plenty,
To view
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 (' 2002-02-28 ', ' yyyy-mm-dd ')-To_date (' 2002-
02-01 ', ' yyyy-mm-dd ') +1
)
where To_char (to_date (' 2002-02-01 ', ' yyyy-mm-dd ') +rnum-1, ' D ')
Not
In (' 1 ', ' 7 ')
Find days between 2002-02-28 and 2002-02-01 with the exception of Monday and seven
The dbms_utility are called before and after each other. Get_time to subtract the result (get 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
Usage of Next_day
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 the last line
You can create a function to handle 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.
Get the number of hours
SELECT EXTRACT (HOUR from TIMESTAMP ' 2001-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 ')
-------------------- -----------------------
2003-10-13 19:35:21 19
Get the date of the year and similar
13.
The processing of the month and the 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)),
) MONTHS,
Newer_date,
Older_date
From (select HireDate older_date,
Add_months (hiredate,rownum) +rownum newer_date
From EMP)
)
14.
Ways to handle days of the month
Select To_char (Add_months (Last_day (sysdate) +1,-2), ' YYYYMMDD '), Last_day (sysdate) from dual
16.
Find out the number of days this year
Select Add_months (trunc (Sysdate, ' year ')-Trunc (Sysdate, "year") from dual
How to deal with leap years
To_char (Last_day (to_date ' | |: Year, ' mmyyyy '), ' DD ')
If it's 28, it's not a leap year.
17.
The difference between YYYY and RRRR
' YYYY99 To_c
------- ----
YYYY 99 0099
RRRR 99 1999
yyyy 01 0001
Rrrr 01 2001
18. Processing of different time zones
Select To_char (New_time (sysdate, ' GMT ', ' EST '), ' dd/mm/yyyy hh:mi:ss '), sysdate
from dual;
19.
5 seconds, one interval.
Select to_date (FLOOR (To_char (sysdate, ' sssss ')/300) *, ' sssss '), To_char (sysdate, ' sssss ')
From dual
2002-11-1 9:55:00 35786
SSSSS represents 5-digit seconds
20.
The first day of the year
Select To_char (sysdate, ' DDD '), sysdate from dual
310 2002-11-6 10:03:51
21. Calculate hours, minutes, seconds, milliseconds
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) A
From dual
)
SELECT * FROM TabName
Order by decode (mode, ' FIFO ', 1,-1) *to_char (RQ, ' Yyyymmddhh24miss ');
//
Floor ((date2-date1)/365) as the year
Floor ((date2-date1, 365)/30) as Month
MoD (mod (date2-date1, 365), 30) as day.
23.next_day function
Next_day (sysdate,6) is from the current beginning of the next Friday. The figures for the following are from Sunday onwards.
1 2 3 4 5 6 7
Day 123456
There are many functions about dates in Oracle
There are many functions about dates in Oracle, such as:
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)
Cases:
Sql> Select Add_months (sysdate,12) "Next year" from dual;
Next year
----------
1 March-November-04
Sql> Select Add_months (sysdate,112) "Last year" from dual;
Last year
----------
1 March-March-13
Sql>
2. Current_date () returns the current date that will be placed in the time zone
Date_value:=current_date
sql> column Sessiontimezone for A15
Sql> select Sessiontimezone,current_date from dual;
Sessiontimezone Current_da
--------------- ----------
+08:00 1 March-November-03
Sql> alter session set Time_zone= ' -11:00 '
2/
The session has changed.
Sql> select Sessiontimezone,current_timestamp from dual;
Sessiontimezone Current_timestamp
--------------- ------------------------------------
-11:00 December-November-03 04.59.13.668000 Afternoon-11:
00
Sql>
3, Current_timestamp () returns the current date in the time zone by timestamp with the 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
--------------- ------------------------------------
+08:00 1 March-November-03 11.56.28.160000 a.m. + 08:
00
Sql> alter session set Time_zone= ' -11:00 '
2/
The session has changed.
Sql> select Sessiontimezone,current_timestamp from dual;
Sessiontimezone Current_timestamp
--------------- ------------------------------------
-11:00 December-November-03 04.58.00.243000 Afternoon-11:
00
Sql>
4, Dbtimezone () return time zone
Varchar_value:=dbtimezone
Sql> select Dbtimezone from dual;
DBTIME
------
-07:00
Sql>
5, extract () to find the date or interval value of the field 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 ' 2000-02-01 ') "Leap Yr?" from dual;
Leap Yr?
----------
2 September-February-00
Sql> Select Last_day (sysdate) "Last day of this month" from dual;
Last Day O
----------
30月-November-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
----------------------------
1 March-November-03 12.09.15.433000
Afternoon
Sql> select Localtimestamp,current_timestamp from dual;
Localtimestamp Current_timestamp
---------------------------- ------------------------------------
1 March-November-03 12.09.31.006000 1 March-November-03 12.09.31.006000 pm + 08:
Afternoon 00
Sql> alter session set time_zone= ' -11:00 ';
The session has changed.
Sql> Select Localtimestamp,to_char (sysdate, ' dd-mm-yyyy HH:MI:SS AM ') "Sysdate" from dual;
Localtimestamp sysdate
---------------------------- ------------------------
December-November-03 05.11.31.259000 13-11-2003 12:11:31 pm
Afternoon
Sql>
8, Months_between () to determine the number of months between two dates
Number_value:=months_between (Date_value,date_value)
Sql> Select Months_between (sysdate,date ' 1971-05-18 ') from dual;
Months_between (sysdate,date ' 1971-05-18 ')
----------------------------------------
389.855143
Sql> Select Months_between (sysdate,date ' 2001-01-01 ') from dual;
Months_between (sysdate,date ' 2001-01-01 ')
----------------------------------------
34.4035409
Sql>
9, Next_day () given a date value, returns the date value indicated by the second argument for the first time out now (the name string of the corresponding day should be returned)
With the week-related date functions
1. Query the first day of the week

Select Trunc (Decode (WW, 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 the week

Select Trunc (Decode (WW, 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 ') ( 
select To_date substr (' 2004-33 ', 1,4) | | 001 ' +rownum-1, ' yyyyddd ') min_date from 
all_tables 
where rownum <= (mod (decode (' 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 
)