Detailed Usage of to_date in oracle Example of oracle Date Format Conversion
- 2021-10-25 08:14:53
- OfStack
TO_DATE format (taking time: 2007-11-02 13:45:25 as an example)
1. Usage of date and character conversion functions (to_date, to_char)
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; // Date converted to string
select to_char(sysdate,'yyyy') as nowYear from dual; // Year of getting time
select to_char(sysdate,'mm') as nowMonth from dual; // Get the month of the time
select to_char(sysdate,'dd') as nowDay from dual; // Get the day of the time
select to_char(sysdate,'hh24') as nowHour from dual; // Gets the time of the time
select to_char(sysdate,'mi') as nowMinute from dual; // Get the points of time
select to_char(sysdate,'ss') as nowSecond from dual; // Get the seconds of the time
2. String and time interchange
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. Ask what day of the week it is
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual; // Week 1
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';
// It can also be like this
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')
4. Days in a two-day period
select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;
5. Time is the usage of null
select id, active_date from table1
UNION
select 1, TO_DATE(null) from dual; // Be careful to use TO_DATE(null)
6. Month difference
a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')
// Then 12 Month 31 Noon 12 After point and 12 Month 1 No. 12 Point before is not included in this range.
// So, when the time needs to be accurate, I feel that to_char It is still necessary
7. Date format conflicts
The format of the input depends on the type of ORACLE character set you installed, such as US7ASCII, and the type of date format is' 01-Jan-01 '
alter system set NLS_DATE_LANGUAGE = American
alter session set NLS_DATE_LANGUAGE = American
// Or in to_date Middle writing
select to_char(to_date('2002-08-26','yyyy-mm-dd'),
'day','NLS_DATE_LANGUAGE = American') from dual;
// Notice that I'm just lifting it NLS_DATE_LANGUAGE Of course, there are many more, which can be viewed
select * from nls_session_parameters
select * from V$NLS_PARAMETERS
8. Query the number of days for special conditions
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 2002-02-28 To 2002-02-01 Divide between weeks 1 And 7 Days of
// Called before and after respectively DBMS_UTILITY.GET_TIME, Subtract the result after letting ( What I get is 1/100 Seconds , Instead of milliseconds )
9. Find Month
select months_between(to_date('01-31-1999','MM-DD-YYYY'),
to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
// The 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;
// The results are: 1.03225806451613
10. 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. Get hours
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
0
12. Treatment of Month, Year and Day
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
1
13. Ways to deal with uncertain months and days
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
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual
// How to Deal with Leap Year
to_char( last_day( to_date('02' | | :year,'mmyyyy') ), 'dd' )
// If it is 28 It's not a leap year
15. Differences between yyyy and rrrr
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
4
16. Handling of different time zones
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
5
17. 5 seconds 1 interval
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
6
Day of 18.1
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
7
19. Calculate hours, minutes, seconds and milliseconds
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
8
20. next_day function
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
9
21. round [rounding to nearest date] (day: rounding to nearest Sunday)
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual; // Week 1
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';
// It can also be like this
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')
0
22. trunc [truncated to the nearest date in days] returns the date type
select sysdate S1,
trunc(sysdate) S2, // Returns the current date , No minutes and seconds
trunc(sysdate,'year') YEAR, // Returns the of the current year 1 Month 1 Day , No minutes and seconds
trunc(sysdate,'month') MONTH , // Returns the of the current month 1 Day , No minutes and seconds
trunc(sysdate,'day') DAY // Returns the Sunday of the current week , No minutes and seconds
from dual
23. Returns the latest date in the date list
select greatest('01-1 Month -04','04-1 Month -04','10-2 Month -04') from dual
24. Calculate the time difference
Note :oracle The time difference is in days , So converting adult months , 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 difference - 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 difference - 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 difference - Hour
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 - Points
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 - Seconds
25. Update time
//oracle Time is added and subtracted in days , Let the change amount be n, So converting adult months , 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 - Hour
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 - Points
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 - Seconds
26. Find Day 1, Last Day of Month
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;