Of Stack

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;

Related articles: