# แสดงเดือนปัจจุบันดังนี้ month(now()); year(now());, day
#SELECT DATE_FORMAT(NOW(), '%M %D') returns "December 27th"
-%m =01-12
-%Y = 2011
-%y = 11
| Specifier | Description |
|---|---|
%a | Abbreviated weekday name (Sun..Sat) |
%b | Abbreviated month name (Jan..Dec) |
%c | Month, numeric (0..12) |
%D | Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) |
%d | Day of the month, numeric (00..31) |
%e | Day of the month, numeric (0..31) |
%f | Microseconds (000000..999999) |
%H | Hour (00..23) |
%h | Hour (01..12) |
%I | Hour (01..12) |
%i | Minutes, numeric (00..59) |
%j | Day of year (001..366) |
%k | Hour (0..23) |
%l | Hour (1..12) |
%M | Month name (January..December) |
%m | Month, numeric (00..12) |
%p | AM or PM |
%r | Time, 12-hour (hh:mm:ss followed by AM or PM) |
%S | Seconds (00..59) |
%s | Seconds (00..59) |
%T | Time, 24-hour (hh:mm:ss) |
%U | Week (00..53), where Sunday is the first day of the week |
%u | Week (00..53), where Monday is the first day of the week |
%V | Week (01..53), where Sunday is the first day of the week; used with %X |
%v | Week (01..53), where Monday is the first day of the week; used with %x |
%W | Weekday name (Sunday..Saturday) |
%w | Day of the week (0=Sunday..6=Saturday) |
%X | Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V |
%x | Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v |
%Y | Year, numeric, four digits |
%y | Year, numeric (two digits) |
%% | A literal “%” character |
% | x, for any “x” not listed above |
#เเพิ่ม ลดเดือนลง 1 เดือน
SELECT * FROM tickets
WHERE MONTH(created) = MONTH(DATE_ADD(now(), INTERVAL -1 MONTH))
#ถ้าต้องการเพิ่มก็ให้เป้น MONTH(DATE_ADD(now(), INTERVAL 1 MONTH))
SQL MYSQL DATE_ADD()
เป็นคำสั่งที่ใช้สำหรับการระบุเงื่อนไขการเลือกข้อมูลในตาราง (Table) โดยทำการ บอก หรือ ลบ วันที่ ที่ต้องการ
Database : MySQL
Syntax
DATE_ADD(date,INTERVAL expr unit)
unit Value | Expected expr Format |
|---|---|
| MICROSECOND | MICROSECONDS |
| SECOND | SECONDS |
| MINUTE | MINUTES |
| HOUR | HOURS |
| DAY | DAYS |
| WEEK | WEEKS |
| MONTH | MONTHS |
| QUARTER | QUARTERS |
| YEAR | YEARS |
| SECOND_MICROSECOND | 'SECONDS.MICROSECONDS' |
| MINUTE_MICROSECOND | 'MINUTES:SECONDS.MICROSECONDS' |
| MINUTE_SECOND | 'MINUTES:SECONDS' |
| HOUR_MICROSECOND | 'HOURS:MINUTES:SECONDS.MICROSECONDS' |
| HOUR_SECOND | 'HOURS:MINUTES:SECONDS' |
| HOUR_MINUTE | 'HOURS:MINUTES' |
| DAY_MICROSECOND | 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' |
| DAY_SECOND | 'DAYS HOURS:MINUTES:SECONDS' |
| DAY_MINUTE | 'DAYS HOURS:MINUTES' |
| DAY_HOUR | 'DAYS HOURS' |
| YEAR_MONTH | 'YEARS-MONTHS' |
Table : member
SELECT Name, DATE_ADD(OrderDate,INTERVAL 30 DAY) As RegisterDate FROM customer
สามารถใส่ค่าเป็น บวก หรือ ลบ ได้
Output
Name | Register Date |
|---|---|
Win Weerachai | 2010-12-27 18:34:00 |
CustomerID | Name | Email | ReisgerDate |
|---|---|---|---|
C001 | Win Weerachai | win.weerachai@thaicreate.com | 2010-11-27 18:34:00 |
Sample1 เลือกข้อมูล Column ชื่อ RegisterDate โดยทำบวกวันที่เข้าไปอีก 1 เดือน
ตัวอย่าง
SELECT (plantreal.r_date) as lr_date,
(plantreal.r_time) AS ltime,
(plantreal.fore) as lfore,
(plantreal.tail) as ltail,
(plantreal.head) as lhead,
(plantreal.evap) as levap,
(plantreal.stor) as lstor,
(plantreal.infl24) as linfl24,
(plantreal.rel24) as lrel24
FROM plantreal
WHERE plantreal.r_date = DATE_ADD(curdate(), INTERVAL -1 DAY) AND
plantreal.r_time = '24:00'
ไม่มีความคิดเห็น:
แสดงความคิดเห็น