Oracle PL/SQL/Date Timezone/LAST DAY
Содержание
- 1 Calculating the Number of Days of Summer in June
- 2 Finding the last day of the month starting summer
- 3 LAST_DAY: Get the last day of each month
- 4 LAST_DAY(hire_date)+1
- 5 LAST_DAY(x): get the date of the last day of the month that contains x.
- 6 select last_day( date"2000-02-01" ) "Leap Yr?"
- 7 select last_day( sysdate ) "Last day of this month"
- 8 Simple demo for LAST_DAY function
Calculating the Number of Days of Summer in June
SQL> SELECT LAST_DAY("20-JUN-99") "Last_Day",
2 LAST_DAY("20-JUN-99") - TO_DATE("20-JUN-99") "Days_Summer"
3 from DUAL;
Last_Day Days_Summer
-------------------- -----------
30-JUN-0099 00:00:00 10
1 row selected.
SQL>
SQL>
SQL> --
Finding the last day of the month starting summer
SQL>
SQL>
SQL> -- Finding the last day of the month starting summer.
SQL> SELECT TO_CHAR(LAST_DAY("30-JUN-97"),"MM/DD/YYYY HH:MM:SS AM") "Last_Day" from DUAL;
Last_Day
----------------------
06/30/1997 12:06:00 AM
SQL>
SQL>
LAST_DAY: Get the last day of each month
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> --LAST_DAY: Get the last day of each month:
SQL>
SQL> SELECT ID, Start_Date, LAST_DAY(Start_Date) AS FirstPayment FROM Employee;
ID START_DAT FIRSTPAYM
---- --------- ---------
01 25-JUL-96 31-JUL-96
02 21-MAR-76 31-MAR-76
03 12-DEC-78 31-DEC-78
04 24-OCT-82 31-OCT-82
05 15-JAN-84 31-JAN-84
06 30-JUL-87 31-JUL-87
07 31-DEC-90 31-DEC-90
08 17-SEP-96 30-SEP-96
8 rows selected.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
LAST_DAY(hire_date)+1
SQL>
SQL> CREATE TABLE person (
2 person_code VARCHAR2(3),
3 first_name VARCHAR2(15),
4 last_name VARCHAR2(20),
5 hire_date DATE
6 );
Table created.
SQL>
SQL> INSERT INTO person VALUES ("CA", "Charlene", "Atlas", "01-FEB-02");
1 row created.
SQL> INSERT INTO person VALUES ("GA", "Gary", "Anderson", "15-FEB-02");
1 row created.
SQL> INSERT INTO person VALUES ("BB", "Bobby", "Barkenhagen", "28-FEB-02");
1 row created.
SQL> INSERT INTO person VALUES ("LB", "Laren", "Baxter", "01-MAR-02");
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT first_name,
2 last_name,
3 hire_date,
4 LAST_DAY(hire_date)+1 INSURANCE_START_DATE
5 FROM person;
FIRST_NAME LAST_NAME HIRE_DATE INSURANCE
--------------- -------------------- --------- ---------
Charlene Atlas 01-FEB-02 01-MAR-02
Gary Anderson 15-FEB-02 01-MAR-02
Bobby Barkenhagen 28-FEB-02 01-MAR-02
Laren Baxter 01-MAR-02 01-APR-02
SQL>
SQL> drop table PERSON;
Table dropped.
SQL>
SQL>
SQL>
LAST_DAY(x): get the date of the last day of the month that contains x.
SQL>
SQL> SELECT LAST_DAY("01-JAN-2005") FROM dual;
LAST_DAY(
---------
31-JAN-05
SQL>
select last_day( date"2000-02-01" ) "Leap Yr?"
SQL>
SQL>
SQL> select last_day( date"2000-02-01" ) "Leap Yr?"
2 from dual;
Leap Yr?
---------
29-FEB-00
1 row selected.
SQL>
SQL> --
select last_day( sysdate ) "Last day of this month"
SQL>
SQL> select last_day( sysdate ) "Last day of this month"
2 from dual;
Last day
---------
30-JUN-08
1 row selected.
SQL>
SQL> --
Simple demo for LAST_DAY function
SQL>
SQL>
SQL> --The LAST_DAY function returns the last day of any month
SQL>
SQL> SELECT TO_CHAR(LAST_DAY("23SEP2006")) FROM dual;
TO_CHAR(L
---------
30-SEP-06
SQL>
SQL>