09/10/2018, 22:44
vấn đề về DATE
Chào mấy bạn
Mình có dữ liệu dạng DATE trong Mysql (YYYY-MM-DD)
bi giờ mình muốn + 40 ngày thì làm sao , mấy huynh giúp với .
Mình có dữ liệu dạng DATE trong Mysql (YYYY-MM-DD)
bi giờ mình muốn + 40 ngày thì làm sao , mấy huynh giúp với .
Bài liên quan
DATE_ADD("YYYY-MM-DD", INTERVAL 40 DAY)
Vd: Update MyTable Set Date_Data = DATE_ADD(Date_Data, INTERVAL 40 DAY) WHERE ...
bạn ơi
trong PHP hay Mysql ko có hàm DATE_ADD("YYYY-MM-DD", INTERVAL 40 DAY)
bạn co' nhầm lẩn ko ??
$date = $row["date"]; // yyyy-mm-dd
$date = explode("-",$date);
// 40 ngày sẽ là 1 tháng 10 ngày
$newdate = $date[3] + 10;
$newmonth = $date[2] + 1;
Đây mình chỉ nói cách cộng vào thôi .. còn nếu sau khi cộng vào tháng vượt qua' 12 hoặc ngày vượt quá 31 thì bạn tự xử lí nha !
còn tháng có 31 ngày , 28 ngày thì seo
chã lẽ viết thêm 1 đoạn code dài thòng sao ?
ko tối ưu
DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)
ADDDATE(date,INTERVAL expr type)
SUBDATE(date,INTERVAL expr type)
These functions perform date arithmetic. They are new for MySQL Version 3.22. ADDDATE() and SUBDATE() are synonyms for DATE_ADD() and DATE_SUB().
In MySQL Version 3.23, you can use + and - instead of DATE_ADD() and DATE_SUB() if the expression on the right side is a date or datetime column. (See example)
date is a DATETIME or DATE value specifying the starting date. expr is an expression specifying the interval value to be added or subtracted from the starting date. expr is a string; it may start with a - for negative intervals. type is a keyword indicating how the expression should be interpreted.
The related function EXTRACT(type FROM date) returns the 'type' interval from the date.
The following table shows how the type and expr arguments are related:
type value Expected expr format
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
MONTH MONTHS
YEAR YEARS
MINUTE_SECOND "MINUTES:SECONDS"
HOUR_MINUTE "HOURS:MINUTES"
DAY_HOUR "DAYS HOURS"
YEAR_MONTH "YEARS-MONTHS"
HOUR_SECOND "HOURS:MINUTES:SECONDS"
DAY_MINUTE "DAYS HOURS:MINUTES"
DAY_SECOND "DAYS HOURS:MINUTES:SECONDS"
MySQL allows any punctuation delimiter in the expr format. Those shown in the table are the suggested delimiters. If the date argument is a DATE value and your calculations involve only YEAR, MONTH, and DAY parts (that is, no time parts), the result is a DATE value. Otherwise the result is a DATETIME value:
mysql> SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
-> 1998-01-01 00:00:00
mysql> SELECT INTERVAL 1 DAY + "1997-12-31";
-> 1998-01-01
mysql> SELECT "1998-01-01" - INTERVAL 1 SECOND;
-> 1997-12-31 23:59:59
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
INTERVAL 1 SECOND);
-> 1998-01-01 00:00:00
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
INTERVAL 1 DAY);
-> 1998-01-01 23:59:59
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
INTERVAL "1:1" MINUTE_SECOND);
-> 1998-01-01 00:01:00
mysql> SELECT DATE_SUB("1998-01-01 00:00:00",
INTERVAL "1 1:1:1" DAY_SECOND);
-> 1997-12-30 22:58:59
mysql> SELECT DATE_ADD("1998-01-01 00:00:00",
INTERVAL "-1 10" DAY_HOUR);
-> 1997-12-30 14:00:00
mysql> SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);
-> 1997-12-02
If you specify an interval value that is too short (does not include all the interval parts that would be expected from the type keyword), MySQL assumes you have left out the leftmost parts of the interval value. For example, if you specify a type of DAY_SECOND, the value of expr is expected to have days, hours, minutes, and seconds parts. If you specify a value like "1:10", MySQL assumes that the days and hours parts are missing and the value represents minutes and seconds. In other words, "1:10" DAY_SECOND is interpreted in such a way that it is equivalent to "1:10" MINUTE_SECOND. This is analogous to the way that MySQL interprets TIME values as representing elapsed time rather than as time of day.
Note that if you add or subtract a date value against something that contains a time part, the date value will be automatically converted to a datetime value:
mysql> select date_add("1999-01-01", interval 1 day);
-> 1999-01-02
mysql> select date_add("1999-01-01", interval 1 hour);
-> 1999-01-01 01:00:00
If you use really incorrect dates, the result is NULL. If you add MONTH, YEAR_MONTH, or YEAR and the resulting date has a day that is larger than the maximum day for the new month, the day is adjusted to the maximum days in the new month:
mysql> select DATE_ADD('1998-01-30', Interval 1 month);
-> 1998-02-28
Note from the preceding example that the word INTERVAL and the type keyword are not case sensitive.
để mình xem lại
cảm ơn nhé