To_date format (in time: 2007-11-02 13:45:25 for example)
1. Date and character conversion function usage (TO_DATE,TO_CHAR)
Copy Code code as follows:
Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') as nowtime from dual; Convert Date to String
Select To_char (sysdate, ' yyyy ') as nowyear from dual; Get the year of time
Select To_char (sysdate, ' mm ') as nowmonth from dual; Get the month of time
Select To_char (sysdate, ' DD ') as nowday from dual; Day of getting time
Select To_char (sysdate, ' hh24 ') as nowhour from dual; When to get time
Select To_char (sysdate, ' mi ') as Nowminute from dual; Get a minute of time
Select To_char (sysdate, ' SS ') as Nowsecond from dual; Get seconds of time
2. String and Time Interchange
Copy Code code as follows:
Select To_date (' 2004-05-07 13:23:44 ', ' yyyy-mm-dd hh24:mi:ss ') from dual
Select To_char (to_date (222, ' J '), ' JSP ') from dual//display, two hundred twenty-two
3. How many days is the day of the week
Copy Code code as follows:
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. Number of days between two dates
Copy Code code as follows:
Select Floor (sysdate-to_date (' 20020405 ', ' YYYYMMDD ')) from dual;
5. The use of time as null
Copy Code code as follows:
Select ID, active_date from table1
UNION
Select 1, to_date (null) from dual; Note to use to_date (NULL)
6. Month Difference
Copy Code code as follows:
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 '
Copy Code code as follows:
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 a lot more to see
SELECT * FROM Nls_session_parameters
SELECT * FROM V$nls_parameters
8. Query Special Condition Days
Copy Code code as follows:
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. Find the Month
Copy Code code as follows:
Select Months_between (to_date (' 01-31-1999 ', ' mm-dd-yyyy '),
To_date (' 12-31-1998 ', ' mm-dd-yyyy ')) "MONTHS" from DUAL;
Results are: 1
Select Months_between (to_date (' 02-01-1999 ', ' mm-dd-yyyy '),
To_date (' 12-31-1998 ', ' mm-dd-yyyy ')) "MONTHS" from DUAL;
Results are: 1.03225806451613
Usage of Next_day
Copy Code code as follows:
Next_day (date, day)
Monday-sunday, for Format code day
Mon-sun, for format code DY
1-7, for format code D
11. Hours of Access
Copy Code code as follows:
Extract () To find the field value of a date or interval value
SELECT EXTRACT (HOUR from TIMESTAMP ' 2001-02-16 2:38:40 ') from offer
Select Sysdate, To_char (sysdate, ' hh ') from dual;
Sysdate to_char (sysdate, ' HH ')
-------------------- ---------------------
2003-10-13 19:35:21 07
Select Sysdate, To_char (sysdate, ' hh24 ') from dual;
Sysdate to_char (sysdate, ' HH24 ')
-------------------- -----------------------
2003-10-13 19:35:21 19
12. The processing of the day and month
Copy Code code as follows:
SELECT
Older_date,
Newer_date,
Years
Months
ABS (
TRUNC (
Newer_date-add_months (Older_date, years * + 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
)
)
13. Method of dealing with the indefinite number of days of month
Copy Code code as follows:
Select To_char (Add_months (Last_day (sysdate) +1,-2), ' YYYYMMDD '), Last_day (sysdate) from dual
14. Find out the number of days this year
Copy Code code as follows:
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.
The difference between 15.yyyy and rrrr
Copy Code code as follows:
YYYY99 To_c
------- ----
YYYY 99 0099
RRRR 99 1999
yyyy 01 0001
Rrrr 01 2001
16. Processing of different time zones
Copy Code code as follows:
Select To_char (New_time (sysdate, ' GMT ', ' EST '), ' dd/mm/yyyy hh:mi:ss '),
Sysdate from dual;
17.5 Seconds One interval
Copy Code code as follows:
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
18. The first day of the year
Copy Code code as follows:
Select To_char (sysdate, ' DDD '), sysdate from dual
310 2002-11-6 10:03:51
19. Calculate hours, minutes, seconds, milliseconds
Copy Code code as follows:
SELECT
Days,
A
TRUNC (A *) Hours,
TRUNC (A * * 60-60 * TRUNC (A *)) Minutes,
TRUNC (
A * * 60-60 * TRUNC (A * 24 * 60)
) Seconds,
TRUNC (
* * * 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
D (MoD (date2-date1, 365), 30) as day.
20.next_day function
Copy Code code as follows:
Returns the date of the next week, day is 1-7 or Sunday-Saturday, 1 means Sunday
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
Select (Sysdate-to_date (' 2003-12-03 12:55:45 ', ' yyyy-mm-dd hh24:mi:ss ')) *24*60*60 from dual
The date returned is natural and then converted to SS
21,round[rounded to nearest date] (day: rounded to the nearest Sunday)
Copy Code code as follows:
Select Sysdate S1,
Round (sysdate) S2,
Round (sysdate, ' year '),
Round (sysdate, ' Month ') month,
Round (sysdate, ' Day ') Day from dual
22,trunc[truncated to the nearest date, the Unit is day, and returns the date type
Copy Code code as follows:
Select Sysdate S1,
Trunc (sysdate) S2,//return to current date, no time seconds
Trunc (Sysdate, ' year ') years,//return to the January 1 of the current age, no time seconds
Trunc (sysdate, ' Month ') month,//return to the 1st of the current month, no time seconds
Trunc (sysdate, ' Day ') day//Return to the current week of Sunday, no time seconds
From dual
23, returns the latest date in the list of dates
Copy Code code as follows:
Select Greatest (' January-January-04 ', ' April-January-04 ', ' October-February -04 ') from dual
24. Calculate Time Lag
Copy Code code as follows:
Note: Oracle time difference is in the number of days, so converted into years, day
Select Floor (To_number sysdate-to_date (' 2007-11-02 15:55:03 ',
' Yyyy-mm-dd hh24:mi:ss ')/365) as Spanyears from dual//time lag-year
Select Ceil (Moths_between sysdate-to_date (' 2007-11-02 15:55:03 ',
' Yyyy-mm-dd hh24:mi:ss ')) as spanmonths from dual//time lag-month
Select Floor (To_number sysdate-to_date (' 2007-11-02 15:55:03 ',
' Yyyy-mm-dd hh24:mi:ss ')) as spandays from dual//time difference-days
Select Floor (To_number sysdate-to_date (' 2007-11-02 15:55:03 ',
' Yyyy-mm-dd hh24:mi:ss ') *24) as spanhours from dual//time lag-when
Select Floor (To_number sysdate-to_date (' 2007-11-02 15:55:03 ',
' Yyyy-mm-dd hh24:mi:ss ') *24*60) as spanminutes from dual//time difference-min
Select Floor (To_number sysdate-to_date (' 2007-11-02 15:55:03 ',
' Yyyy-mm-dd hh24:mi:ss ') *24*60*60) as Spanseconds from dual//Time Difference-sec
25. Update Time
Copy Code code as follows:
Oracle time plus minus is in the number of days, set the change amount to N, so converted into years, day
Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '),
To_char (sysdate+n*365, ' Yyyy-mm-dd hh24:mi:ss ') as NewTime from dual//change time-year
Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '),
Add_months (Sysdate,n) as newtime from dual//change time-month
Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '),
To_char (Sysdate+n, ' Yyyy-mm-dd hh24:mi:ss ') as NewTime from dual//change Time-day
Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '),
To_char (sysdate+n/24, ' Yyyy-mm-dd hh24:mi:ss ') as NewTime from dual//change Time-when
Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '),
To_char (SYSDATE+N/24/60, ' Yyyy-mm-dd hh24:mi:ss ') as NewTime from dual//change Time-min
Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '),
To_char (SYSDATE+N/24/60/60, ' Yyyy-mm-dd hh24:mi:ss ') as NewTime from dual//Change Time-Sec
26. Find the first day of the month, the last day
Copy Code code as follows:
SELECT Trunc (Trunc (sysdate, ' MONTH ')-1, ' MONTH ') First_day_last_month,
Trunc (sysdate, ' MONTH ')-1/86400 last_day_last_month,
Trunc (sysdate, ' MONTH ') First_day_cur_month,
Last_day (Trunc (sysdate, ' MONTH ')) + 1-1/86400 Last_day_cur_month
from dual;
