ในการพัฒนาระบบ หลาย ๆ ครั้งต้องมีการคำนวณหาจำนวนวันเดือนปี ที่พบบ่อย ๆ เช่น การคำนวณวันเกิด การคำนวณอายุงาน หรือแม้กระทั่งการคำนวณหาอายุงานเมื่อเกษียณอายุราชการ เพื่อใช้ในการคำนวณบำเหน็จบำนาญหรือสวัสดิการต่าง ๆ

ตาราง : test_cal_year
field
– CUR_DATE = วันที่ปัจจุบันที่ต้องการคำนวณ
– BIRTH_DATE = วันเกิด

1. หาจำนวนปี

จะใช้คำสั่ง DATEDIFF ในการหาจำนวนปี

SELECT      cast(GETDATE() as date) CUR_DATE ,BIRTH_DATE,
                  DATEDIFF(YEAR, BIRTH_DATE, GETDATE()) AS YEAR
FROM        test_cal_year

แต่คำสั่ง DATEDIFF นั้น จะเป็นการคำนวณหาจำนวนปี จาก ปีปัจจุบัน ลบด้วยปีเกิด โดยไม่ได้มีการเช็คว่า ครบรอบวันเกิดหรือไม่ ทำให้ข้อมูลเกิดความผิดพลาดได้ ดังนั้น จึงต้องเพิ่มเงื่อนไขการตรวจสอบเข้าไป ว่าครบถึงรอบวันเกิดในปีนั้นหรือยัง โดยเพิ่ม query เข้าไปตรวจสอบ

CASE WHEN MONTH(birth_date) > MONTH(GETDATE()) OR             
           (MONTH(birth_date) = MONTH(GETDATE()) AND
           DAY(birth_date) > DAY(GETDATE())) THEN 1 ELSE 0   AS ADJUST

อธิบาย

จะ check 2 เงื่อนไข คือ ถ้า
– เดือนเกิด มากกว่า เดือนปัจจุบัน หรือ
– เดือนเกิด คือ เดือนปัจจุบัน และ วันเกิดมากกว่า วันปัจจุบัน
จะให้ค่า 1 นอกนั้น ให้ค่า 0

เมื่อได้ค่า ADJUST แล้ว ให้ไปลบออกจาก YEAR ที่คำนวณได้ก่อนหน้า จะได้เป็น อายุ (ปี) ที่แท้จริง

SELECT      cast(GETDATE() as date) CUR_DATE,BIRTH_DATE,
                     DATEDIFF(YEAR, BIRTH_DATE, GETDATE())  AS YEAR,
                     CASE WHEN MONTH(birth_date) > MONTH(GETDATE()) OR 
                     MONTH(birth_date) = MONTH(GETDATE()) AND DAY(birth_date)
                      > DAY(GETDATE())) THEN 1 ELSE 0 end as ADJUST,
                     DATEDIFF(YEAR, BIRTH_DATE, GETDATE())  – CASE WHEN
                     MONTH(birth_date) > MONTH(GETDATE()) OR
                     (MONTH(birth_date) = MONTH(GETDATE()) AND
                     DAY(birth_date) > DAY(GETDATE())) THEN 1 ELSE 0 END AS
                     YEAR_ADJUST

FROM        test_cal_year

2. หาจำนวนเดือน

จะใช้คำสั่ง DATEDIFF ในการหาจำนวนเดือน

SELECT      cast(GETDATE() as date) CUR_DATE,BIRTH_DATE,                                                          DATEDIFF(MONTH, BIRTH_DATE, GETDATE()) AS MONTH

FROM        test_cal_year

เช่นเดียวกับการหาจำนวนปี คำสั่ง DATEDIFF จะหาจำนวนเดือนที่เป็นจำนวนเต็ม โดยไม่ได้เช็คว่าครบรอบวันเกิดหรือยัง จึงต้องจะเพิ่มเงื่อนไขการตรวจสอบเข้าไป

CASE WHEN DAY(GETDATE()) < DAY(BIRTH_DATE) THEN 1 ELSE 0 end AS ADJUST

เมื่อได้ค่า ADJUST แล้ว ให้ไปทำการลบออกจากเดือนที่คำนวณได้ และหาจำนวนเดือนที่เป็นเศษ โดยใช้ % 12 (MOD ด้วย 12 เดือน) ค่าที่ได้ คือ จำนวนเดือน

SELECT      cast(GETDATE() as date) CUR_DATE,BIRTH_DATE,
                     DATEDIFF(MONTH, BIRTH_DATE, GETDATE()) AS MONTH,
                     CASE WHEN DAY(GETDATE()) < DAY(BIRTH_DATE) THEN 1 ELSE 
                     0 end AS ADJUST,
                    (DATEDIFF(MONTH, START_DATE, GETDATE()) – CASE WHEN                                  DAY(GETDATE()) < DAY(start_date) THEN 1 ELSE 0 END) % 12 AS
                     MONTH_ADJUST

FROM        test_cal_year

3. หาจำนวนวัน

การหาจำนวนวันจะแตกต่างจากการจำนวนปี และเดือน คือ จะเป็นการหาจำนวนวันที่เหลือ หลังจากหัก ปี และเดือน ออกไปแล้ว มีขั้นตอน ดังนี้

1. หาวันที่ครบรอบของเดือนล่าสุด จากการหาจำนวนเดือนของ query ที่แล้ว จะใช้คำสั่ง DATEADD เพื่อหาวันที่ครบรอบของเดือนล่าสุด

DATEADD(MONTH, DATEDIFF(MONTH, BIRTH_DATE, GETDATE()) – CASE WHEN DAY(GETDATE()) < DAY(BIRTH_DATE) THEN 1 ELSE 0 END, BIRTH_DATE) AS LAST_DAY

2. หาผลต่างจากข้อ 1  ถึงวันปัจจุบัน เป็นจำนวนวัน โดยใช้คำสั่ง DATEDIFF เพิ่มเข้าไป

DATEDIFF(DAY,DATEADD(MONTH, DATEDIFF(MONTH, BIRTH_DATE, GETDATE()) – CASE WHEN DAY(GETDATE()) < DAY(BIRTH_DATE) THEN 1 ELSE 0 END, BIRTH_DATE), GETDATE())  AS DAY

3. เมื่อได้จำนวนวันมาแล้ว ให้ทำการ +1 เข้าไป เพื่อนับถึงวันปัจจุบัน (หากไม่ต้องการนับถึงวันปัจจุบัน ไม่ต้อง +1 ขึ้นอยู่กับเงื่อนไขข้อมูล)

DATEDIFF(DAY,DATEADD(MONTH, DATEDIFF(MONTH, BIRTH_DATE, GETDATE()) – CASE WHEN DAY(GETDATE()) < DAY(BIRTH_DATE) THEN 1 ELSE 0 END, BIRTH_DATE), GETDATE())+1  AS ADJUST_DAY

เมื่อหา ปี เดือน วัน ได้แล้ว จะได้คำสั่ง เป็น

SELECT cast(GETDATE() as date) CUR_DATE,BIRTH_DATE,
                DATEDIFF(YEAR,BIRTH_DATE, GETDATE()) - 
                CASE WHEN MONTH(birth_date) > MONTH(GETDATE()) OR 
                (MONTH(birth_date) = MONTH(GETDATE()) AND DAY(birth_date) > 
                DAY(GETDATE())) THEN 1 ELSE 0 END AS YEAR_ADJUST,
                (DATEDIFF(MONTH, START_DATE, GETDATE()) - 
                CASE WHEN DAY(GETDATE()) < DAY(start_date) THEN 1 ELSE 0 
                END) % 12 AS MONTH_ADJUST ,
                DATEDIFF(DAY,DATEADD(MONTH, DATEDIFF(MONTH, 
                BIRTH_DATE, GETDATE()) - CASE WHEN DAY(GETDATE()) < 
                DAY(BIRTH_DATE) THEN 1 ELSE 0 END, BIRTH_DATE), 
                GETDATE())+1 AS ADJUST_DAY 
FROM test_cal_year

จากตัวอย่าง เป็นการคำนวณหาอายุ ซึ่งคำนวณจาก วันเกิด – วันปัจจุบัน ซึ่งเมื่อวันปัจจุบันเปลี่ยนไป จำนวนวัน เดือน ปี ก็จะเปลี่ยนแปลงตามไปด้วย Query นี้ สามารถนำไปประยุกต์ใช้ในการคำนวนหาจำนวน ปี เดือน วัน ของข้อมูล จากช่วงวันที่ 2 วันที่อื่น ๆ ได้ เช่น อายุงานทั้งหมดตั้งแต่เริ่มรับราชการ จนถึงวันที่เกษียณอายุราชการ  ซึ่งเป็นวันที่ที่เป็นค่าคงที่ โดยการแทนค่าข้อมูลจาก GETDATE เป็น วันที่เกษียณอายุราชการ , BIRTH_DATE เป็นวันที่เริ่มรับราชการ