วันนี้ผมได้งานจากที่่ทำงานมาให้เขียน Query เพื่อแจ้งเตือนสินค้าที่หมดอายุล่วงหน้าตามวันที่กำหนดไว้ ระหว่างวันที่กำหนดจนถึงจำนวนวันที่แจ้งเตือนล่วงหน้า โดยเอาผลัลพธ์ที่ได้มาจัดโปรโมชั่นลดแลกแจกแถมกัน (สมมติว่าเป็น 7 วัน) โดยผมค่อยๆแนะนำไปทีละขั้นตอน ดังนี้
เตรียมข้อมูล
- กำหนดโครงสร้างตาราง ดังรูป
- ใส่ช้อมูลเตรียมลงไป ดังรูป
ลองผิดลองถูก
จากโจทย์มาลองแปลงเป็นแนวคิด "เลือกสินค้าที่มีวันหมดอายุอยู่ในช่วง วันที่ปัจจุบัน จนถึง วันที่ปัจจุบัน + 7 " ถ้าเอามาแปลงเป็น Query ต้องแปลงอย่างไร ใช้อะไรบ้าง ซึ่งใช้ได้หลากหลายแบบ ตอนนี้ผมขอใช้ Between ก่อน ปัญหาถัดมา คือ วันที่ปัจจุบัน + 7 สามารถเขียน Query ได้อย่างไร เขียนแทนค่าตรงๆ หรือใช้ฟังก์ชั่นช่วย
ปัญหา คือ "2013-11-07"+7 ใช้ได้ หรือไม่ ?
- ลองเขียน Query ดังนี้
DECLARE @ProcessDate As Date = '2013-11-01' SELECT Product_ID, Product_Name, Unit, PricePerUnit, ExpireDate FROM PRODUCT WHERE ExpireDate BETWEEN @ProcessDate AND @ProcessDate + 7
- ลองรันดูพบว่าใช้ไม่ได้ เกิด Error Message ซึ่งสาเหตุของ Error เกิดจากตัวแปร Process Date มีชนิดข้อมูล Date ซึ่งไม่สามารถนำมาบวกกับ 7 ซึ่งมีชนิดข้อมูล Integer ได้
เพิ่มเติม: หากกำหนดชนิดข้อมูลแบบ Datetime ตัว SQL Server ยอมให้ ลบกับข้อมูลประเภท Integer ได้
ลองทำฟังก์ชัน DATEADD ซึ่งมีหน้าที่จัดการบวก/ลบวันที่
รูปแบบการใช้งาน ดังนี้
DATEADD (datepart , number , date )
- datepart คือ รูปแบบของเวลา เช่น ชั่วโมง(DAY), สัปดาห์(WEEK) หรือ ไตรมาส(QUARTER) เป็นต้น
- number คือ จำนวนเวลาที่ต้องการเพิ่ม หรือ ลบ ออก จากโจทย์ เราต้องแจ้งเตือนล่วงหน้า 7 วัน ดังนั้น number เท่ากับ 7
- date คือ วันตั้งต้น จากโจทย์ คือ ProcessDate น้ันเอง
ลองเขียน Query โดยใช้ DateADD ดังนี้
DECLARE @ProcessDate As Date = '2013-11-01' SELECT Product_ID, Product_Name, Unit, PricePerUnit, ExpireDate FROM PRODUCT WHERE ExpireDate BETWEEN @ProcessDate AND DATEADD(DAY, 7, @ProcessDate)
เมื่อลองรัน Query พบว่าแสดงข้อมูลได้ผลลัพธ์ตรงตามต้องการ ดังรูป
ตัวอย่างอื่นๆที่ใช้ DATEADD โดยกำหนดค่า ProcessDate ดังนี้
DECLARE @ProcessDate As Datetime = '2013-04-23 13:05:07.256'
| รูปแบบเวลา | รูปแบบเวลา (ตัวย่อ) | Query | ผลลัพธ์ |
|---|---|---|---|
| MILLISECOND | ms | SELECT DATEADD(MILLISECOND,1500,@ProcessDate) | 2013-04-23 13:05:08.757 |
| MINUTE | mi, n | SELECT DATEADD(MINUTE,15,@ProcessDate) | 2013-04-23 13:20:07.257 |
| HOUR | hh | SELECT DATEADD(HOUR,-3,@ProcessDate) | 2013-04-23 10:05:07.257 |
| WEEKDAY | dw, w | SELECT DATEADD(WEEKDAY,-2,@ProcessDate) | 2013-04-21 13:05:07.257 |
| WEEK | wk, ww | SELECT DATEADD(WEEK,4,@ProcessDate) | 2013-05-21 13:05:07.257 |
| DAY | dd, d | SELECT DATEADD(DD,4,@ProcessDate) | 2013-04-27 13:05:07.257 |
| MONTH | mm, m | SELECT DATEADD(MM,-4,@ProcessDate) | 2012-12-23 13:05:07.257 |
| QUARTER | qq, q | SELECT DATEADD(Q,-2,@ProcessDate) | 2012-10-23 13:05:07.257 |
| YEAR | yy, yyyy | SELECT DATEADD(YYYY,-5,@ProcessDate) | 2008-04-23 13:05:07.257 |
สรุปผล
จากโจทย์นี้สามารถเขียน Query ได้หลายแบบมากๆ โดยตัวอย่างที่ผมเขียนเป็นการสอนการใช้ฟังก์ชั่น DATEADD ซึ่งสรรพคุณของฟังก์ชั่นนี้สามารถลดจำนวน Code ที่ผู้อ่านเขียนได้แน่นอน และช่วยให้การทำงานของโปรแกรมของเรามีประสิทธิภาพมากยิ่งขึ้นด้วย
แหล่งอ้างอิง
- Microsoft MSDN: DATEADD (Transact-SQL)
- ตาราง Data Type Conversation จาก (Microsoft MSDN: CAST and CONVERT (Transact-SQL)
Discover more from naiwaen@DebuggingSoft
Subscribe to get the latest posts sent to your email.
