Cách sử dụng Single-Row Functions kiểu date trong Oracle SQL

By
Digital Transformation VN
-
  1. Giới thiệu

Trong bài trước chúng ta đã hiểu được Single-row Function là gì, làm quen và biết cách sử dụng các Single-row Function kiểu character, kiểu number. Trong bài này SysVN tiếp tục giới thiệu Single-row Function với kiểu date. Mời các bạn theo dõi.

2. Mục tiêu

Sau khi hiểu bài này các bạn có thể hiểu được định dạng kiểu date trong Oracle Database, hiểu được định dạng kiểu date theo dạng RR, sử dụng các hàm kiểu date, tính toán với kiểu date.

3. Làm việc với date

  • Oracle Database lưu trữ kiểu ngày theo dạng: Thế kỷ, năm, tháng, ngày, giờ, phút, giây
  • Định dạng mặc định kiểu date trong Oracle Database theo dạng DD-MON-RR. Trên thực tế hiếm khi sử dụng dạng RR mà sử dụng theo định dang riêng cho dễ đọc
  • Mặc định định dạng hiển thị là DD-MON-RR. Oracle hỗ trợ ngày tháng từ 1/1/4712 BC đến 31/12/9999 AD. Cho phép lưu ngày tháng thế kỷ 21 vào thế kỷ 20 hay ngược lại nhờ sử dụng 2 chữ số cuối của năm:

  • Nếu 2 chữ số cuối của năm hiện tại là 00 – 49:

    Nếu 2 chữ số cuối của năm trong giá trị ngày tháng là 00 – 49: năm trong thế kỷ hiện tại (RR, YY)
    Nếu 2 chữ số cuối của năm trong giá trị ngày tháng là 50 – 99: năm ở thế kỷ trước đó (RR), hiện tại (YY)

  • Nếu 2 chữ số cuối của năm hiện tại là 50 – 99:

    Nếu 2 chữ số cuối của năm trong giá trị ngày tháng là 00 – 49: năm trong thế kỷ tiếp theo (RR), hiện tại (YY)
    Nếu 2 chữ số cuối của năm trong giá trị ngày tháng là 50 – 99: năm ở thế kỷ hiện tại (RR, YY)

Ví dụ: 

Ngày tháng trong Oracle được lưu theo dạng

CENTURYYEARMONTHDAYHOURMINUTESECOND
19870617171043

Để xem ngày, giờ hiện tại trong hệ thống database ta dùng hàm sysdate

SELECT sysdate
FROM dual;

4. Toán học trong kiểu date

Về ý nghĩa ngày tháng cũng là một con số, cho nên ta có thể áp dụng các phép toán: cộng, trừ

  • Cộng, trừ một số với  date kết quả là date
  • Date – date kết quả là số ngày (khoảng cách giữa 2 ngày)
  • Date + number/24 kết quà là date (cộng thêm giờ)

Ví dụ: Hiển thị tên, số tuần làm việc của những nhân viên ở phòng ban id là 90

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees
WHERE department_id = 90;

LAST_NAME WEEKS
————————- ———-
King 719.713151
Kochhar 601.570294
De Haan 846.141723

Ví dụ: Hiển thị ngày tháng năm của năm ngày sắp tới so với ngày hiện tại

SELECT sysdate + 5 FROM dual;

SYSDATE+5
———
06-APR-17

Ví dụ: Hiển thị ngày tháng năm cách đây năm ngày so với ngày hiện tại

SELECT sysdate -5 FROM dual;

SYSDATE-5
———
27-MAR-17

Ví dụ: 

SELECT TO_CHAR(sysdate + 5/24, ‘dd-mm-yyyy hh24:mi:ss’) FROM dual;

TO_CHAR(SYSDATE+5/2
——————-
02-04-2017 04:55:39

4. Date Functions

HàmCú phápKết quả
MONTHS_BETWEENMONTHS_BETWEEN(date1, date2)Tính số tháng giữa date1 và date2. Nếu date1 > date 2, kết quả ra số dương. Nếu date1 < date2, kết quả ra số âm
ADD_MONTHSADD_MONTHS(date, n)Cộng thêm n tháng vào thời điểm date, n là số nguyên có thể âm
NEXT_DAYNEXT_DAY(date, ‘char‘)Tìm ra ngày kế tiếp của ngày được chỉ ra, char đại diện cho ngày trong tuần
LAST_DAYLAST_DAY(date)Tìm ra ngày cuối cùng của tháng chứa date
ROUNDROUND(date[,’fmt‘])Làm tròn ngày đến đơn vị định dạng fmt. Nếu không có fmt date được làm tròn đến ngày gần nhất.
TRUNCTRUNC(date[, ‘fmt‘])trả về ngày đầu tiên theo đơn vị fmt, nếu không có fmt thì trả về đầu ngày. Nói dễ hiểu thì nếu không có fmt sẽ trả về thời điểm đầu ngày (0 giờ 0 phút 0 giây), nếu cắt về tuần thì trả về đầu tuần, cắt về tháng thì trả về đầu tháng, cắt về quý thì trả về đầu quý

5. Một số ví dụ

SELECT MONTHS_BETWEEN(sysdate, TO_DATE(’30-MAR-2013′,’DD-MON-YYYY’))
FROM dual;

MONTHS_BETWEEN(SYSDATE,TO_DATE(’30-MAR-2013′,’DD-MON-YYYY’))
————————————————————
48.0986302

SELECT ADD_MONTHS(sysdate, 10)
FROM dual;

ADD_MONTH
———
02-FEB-18

SELECT NEXT_DAY(’13-FEB-1984′, ‘monday’)
FROM dual; 

NEXT_DAY(
———
20-FEB-84

select LAST_DAY(SYSDATE)
FROM dual;
LAST_DAY(
———
30-APR-17

SELECT employee_id, hire_date, MONTHS_BETWEEN (SYSDATE, hire_date)
TENURE, ADD_MONTHS (hire_date, 6) REVIEW, NEXT_DAY (hire_date,
‘FRIDAY’), LAST_DAY(hire_date)
FROM employees WHERE MONTHS_BETWEEN (SYSDATE, hire_date) < 150;

Giả sử  SYSDATE = ’25-JUL-03′ xem bảng ví dụ sau

SELECT employee_id, hire_date,
ROUND(hire_date, ‘MONTH’), TRUNC(hire_date, ‘MONTH’)
FROM employees
WHERE hire_date LIKE ‘%08’

Bài liên quan
prev next

Bài tương tựXem thêm