SQL to_char,to_date Date String conversion problem

1. Conversion function
The most significant relationship with the date operation is two conversion functions: To_date (), To_char ()
The To_date () function 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 a conversion date format, note, before and after the two to a corresponding.
such as; To_date (' 2004-11-27 13:34:43 ', ' yyyy-mm-dd hh24:mi:ss ') will get specific time

Multiple date formats:

YYYY: Year four-bit representation
Yyy,yy,y: The last three, two, or a bit of the year, the default is the current century
Month Number of mm:01~12
Month: Nine characters in months, and the right is filled with a space
MON: three-character month abbreviation
WW: The week of the year
D: The day of the week
DD: The day ordinal of the month
DDD: The first day of the year
Day: Nine characters full name, right with space
Hh,hh12: The first hour of the day, 12 binary notation
HH24: The first hour of the day, the value is 00~23
MI: Minutes in an hour
SS: seconds in one minute
SSSS: The number of seconds to go from midnight

To_char (): convert date to a certain format for 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 into a character type in YYYY-MM-DD hh24:mi:ss format

Working with date Daquan 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 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] Date and time function
1.
Date and character conversion function usage (TO_DATE,TO_CHAR)

2.
Select To_char (to_date (222, ' J '), ' JSP ') from dual

Showing the Hundred Twenty-two

3.
Ask what day it is
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
Setting the date language
ALTER SESSION SET nls_date_language= ' AMERICAN ';
You can do that.
To_date (' 2002-08-26 ', ' yyyy-mm-dd ', ' nls_date_language = American ')

4.
Days of the two-day period
Select Floor (sysdate-to_date (' 20020405 ', ' YYYYMMDD ')) from dual;

5. The use of time 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 ')
It is not included in this range until after 12 o'clock noon on December 31 and before 12 o ' December 1.
So, when time needs to be accurate, I think to_char is still necessary.
7. Date format conflict issues
The input format depends on the type of Oracle character set you installed, such as: Us7ascii, the type of date format 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;
Note that I'm just lifting the nls_date_language, and of course there's a lot of
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 ')

Look for days between 2002-02-28 and 2002-02-01 except Monday and seven
Call Dbms_utility before and after each. Get_time, let's 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 set up 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 ') by 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 month and year day similar to this
13.
The treatment of the 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)),
) MONTHS,
Newer_date,
Older_date
From (select HireDate older_date,
Add_months (hiredate,rownum) +rownum newer_date
From EMP)
)

14.
Ways to deal with the indefinite number of months
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.

17.
The 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.
5 Seconds an 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-bit seconds

20.
The day ordinal of a 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 the 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 following numbers are counted from Sunday onwards.
1 2 3 4 5 6 7
Day 123456

There are many functions for dates in Oracle

There are many functions about dates in Oracle, such as:
1, add_months () to increase or decrease some 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 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 pm-11:
00

Sql>

3. Current_timestamp () returns the current date in the current time zone with the timestamp with date 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 morning + 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 pm-11:
00

Sql>

4, Dbtimezone () return time zone
Varchar_value:=dbtimezone
Sql> select Dbtimezone from dual;

DBTIME
------
-07:00

Sql>

5, extract () Find 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 containing 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 () 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 that was indicated by the second argument for the first time out of the day (should return the name string for the corresponding day)

Date function with week

1. Query the first day of a week
Select Trunc (WW, decode, 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. Check the last day of a week
Select Trunc (WW, decode, 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 <= 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 '

Ext.: http://blog.163.com/zsy_19880518/blog/static/185258127201291552731193/

SQL to_char,to_date Date String conversion problem