SQL日期和时间

一、日期是如何存储的

    1、日期和时间的标准数据类型
        日期和时间(DATETIME)存储的标准SQL数据类型有3种
        DATE：存储日期。DATE的格式是YYYY-MM-DD，范围是从0001-01-01到9999-12-31。
        TIME：存储时间。TIME的格式是HH:MI:SS.nn…，范围是从00:00:00…到23:59:61.999…。
        TIMESTAMP：存储日期和时间。TIMESTAMP的格式是YYYY-MM-DD HH:MI:SS.nn…,范围是从0001-01-01 00:00:00…到9999-12-31 23:59:61.999…。

    2、DATETIME元素
        DATETIME元素 有效范围
        YEAR  0001到9999
        MONTH 01到12
        DAY  01到31        HOUR 00到23
        MINUTE 00到59
        SECOND 00.000…到61.999…

    3、不同实现的日期类型

不同平台的日期类型

产品	数据类型	用途
Oracle	DATE	存储日期和时间信息
SQL Server	DATETIME	存储日期和时间信息
	SMALLDATETIME	存储日期和时间信息，但取值范围小于DATETIME
	DATE	存储日期值
	TIME	存储时间值
MySQL	DATETIME	存储日期和时间值
	TIMESTAMP	存储日期和时间值
	DATE	存储日期值
	TIME	存储时间值
	YEAR	单字节，表示年

二、日期函数

    日期函数用于调整日期和时间的数据外观，以适当的方式显示日期和时间数据、进行比较、计算日期之间的间隔等。

    1、当前日期
        从根本上来说，当前日期保存在数据库所在的计算机上时，被称为系统日期。

        不同实现里获取系统日期的方法：

        SQL Server使用GETDATE()的函数获取系统日期。
        SELECT GETDATE()

        MySQL使用NOW()函数获取当前日期和时间。NOW被称为伪字段。
        SELECT NOW()

        Oracle使用SYSDATE函数获取当前日期和时间，并使用了Oracle中的DUAL表。
        SELECT SYSDATE FROM DUAL

    2、时区
        在处理日期和时间信息时，可能要考虑时区。不同时区，不同日期和时间信息。

    3、时间和日期相加

        SQL Server的DATEADD函数：
        DATEADD(MONTH, 1, DATE_HIRE)

        Oracle的ADD_MONTHS函数：
        ADD_MONTHS(DATE_HIRE,1)

        Oracle的日期增加一天：
        DATE_HIRE + 1

        MySQL使用ANSI标准的INTERVAL命令：
        DATE_ADD(DATE_HIRE, INTERVAL 1 DAY)

    4、其他日期函数

其它日期函数

产品	日期函数	用途
SQL Server	DATEPART	返回日期的某个元素的整数值
	DATENAME	返回日期的某个元素的文本值
	GETDATE()	返回系统时间
	DATEDIFF	返回两个日期之间由指定日期元素表示的间隔，比如天数、分钟数和秒数
Oracle	NEXT_DAY	返回指定日期之后的下一天(比如FRIDAY)
	MONTHS_BETWEEN	返回两个日期之间相差的月数
MySQL	DAYNAME(date)	显示星期几
	DAYOFMONTH(date)	显示几日
	DAYOFWEEK(date)	显示星期几
	DAYOFYEAR(date)	显示一年中的第几天

三、日期转换

    进行日期转换的典型原因有：
    比较不同数据类型的日期值；
    把日期格式转换为字符串；
    把字符串转换为日期格式。

    ANSI的CAST操作符可以把一种数据类型转换为另一种，其基本语法如下：
    CAST(EXPRESSION AS NEW_DATA_TYPE)

    1、日期描述
        日期描述由格式元素组成，用于从数据库以期望的格式提取日期和时间信息。
        常见的日期元素

    2、日期转换为字符串

        SQL Server使用的DATETIME
        DATE_HIRE = DATETIME(MONTH, DATE_HIRE)

        Oracle使用的TO_CHAR函数
        TO_CHAR(DATE_HIRE, ‘Month dd, yyyy’)

    3、字符串转换为日期

        MySQL的STR_TO_DATE
        STR_TO_DATE('01/01/2010 12:00:00 AM', '%m/%d/%Y %h:%i:%s %p')

        SQL Server的CONVERT
        CONVER(DATETIME,’02/25/2010 12:00:00 AM’)