在Mysql上保存2月30日(日期格式) [英] Saving 30-Feb on Mysql (Date Formating)

问题描述

我在保存预定日期"时遇到问题，因为这种方式的每个月每个都有30天.我需要使用类似日期类型(而不是varchar/text/blob/etc)的类型来保存元素(2014-02-30)来保存它，因为在此项目中我们需要它.有可能吗?

I have a problem saving 'contable dates' because every month on this way has 30 days each. I need to save a element (2014-02-30) using a type date-like (not a varchar/text/blob/etc) to save this because in this project we need that. Is it possible?

推荐答案

可以使用sql_mode DATE或DATETIME列中. mysql.com/doc/refman/5.6/zh-CN/sql-mode.html#sqlmode_allow_invalid_dates"rel =" nofollow> ALLOW_INVALID_DATES ，并且没有严格模式:

Saving such a DATE "value" in a DATE or DATETIME column is possible using the sql_mode ALLOW_INVALID_DATES and no strict mode:

ALLOW_INVALID_DATES

ALLOW_INVALID_DATES

不对日期进行全面检查.仅检查月份 范围是1到12，日期范围是1到31. 对于获取年份，月份， 和日期在三个不同的字段中，您想确切存储什么 用户插入的内容(无日期验证).此模式适用于DATE 和DATETIME列.它不应用TIMESTAMP列， 总是需要一个有效的日期.

Do not perform full checking of dates. Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31. This is very convenient for Web applications where you obtain year, month, and day in three different fields and you want to store exactly what the user inserted (without date validation). This mode applies to DATE and DATETIME columns. It does not apply TIMESTAMP columns, which always require a valid date.

因此，可以使用触发器来检查允许的可允许日期的日期，因为也没有其他检查.我认为对于此应用程序，每个月的31号都是无效的日期.

So checking the date for an allowed contable date could be done with triggers, since there's no other check too. I assume that for this application the 31th of each month would be an invalid date.

示例:

CREATE TABLE example (
  contable_date DATE NOT NULL
) ENGINE=INNODB;

-- set the sql_mode (here for the session)
SET @@SESSION.sql_mode = 'ALLOW_INVALID_DATES';

INSERT INTO example (contable_date) VALUES ("2014-02-30");

SELECT 
    DAY(contable_date) as cday,
    MONTH(contable_date) as cmonth,
    TIMESTAMPDIFF(DAY, contable_date, '2014-03-30') as cdiff
FROM 
    example;

结果:

cday  cmonth  cdiff
-------------------
  30       2     28

演示

使用我随附的MySQL Workbench

Using MySQL Workbench I get with

SELECT contable_date FROM example

以下结果:

contable_date
-------------
   2014-02-30

，但这在sqlfiddle.com上不起作用.

but this doesn't work at sqlfiddle.com.

我不建议这样做，特别是因为一个人不能使用严格的SQL模式.还应该考虑对日期和时间函数的影响.

I wouldn't recommend this though, especially because one's not able to use strict SQL mode. One should consider the effect on the date and time functions too.

这篇关于在Mysql上保存2月30日(日期格式)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！