Plsql_oracle plsql Processing Date mode Daquan (concept)

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 ....


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. What 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
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. Number of days between two dates
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.

Reprinted: Http://www.blogjava.net/xzclog/archive/2006/08/10/62819.html