SQL Server 2008创建含指定格式日期列的表的方法咨询

首先得澄清一个关键概念：数据库中的datetime类型并不会以你指定的22-Apr-2018这种字符串格式存储，它是用数据库内部的二进制格式来保存日期时间信息的，存储格式和显示格式是完全分离的。所以你没法直接在CREATE TABLE语句里定义datetime列的显示格式。

不过你可以通过以下几种方式实现查询时按22-Apr-2018格式展示日期：

1. 查询时使用函数格式化输出 #

这是最常用也最灵活的方式，针对不同数据库有不同的函数：

MySQL/MariaDB #

用DATE_FORMAT函数：

SELECT 
  DATE_FORMAT(dtFrom, '%d-%b-%Y') AS dtFrom_formatted,
  DATE_FORMAT(dtTo, '%d-%b-%Y') AS dtTo_formatted,
  service_time
FROM tbl_previous_policy;

这里%d代表两位日期，%b代表缩写月份（比如Apr），%Y代表四位年份。

SQL Server #

用CONVERT或FORMAT函数：

SELECT 
  REPLACE(CONVERT(VARCHAR, dtFrom, 106), ' ', '-') AS dtFrom_formatted, -- 转换为22-Apr-2018格式
  REPLACE(CONVERT(VARCHAR, dtTo, 106), ' ', '-') AS dtTo_formatted,
  service_time
FROM tbl_previous_policy;

或者用更直观的FORMAT函数：

SELECT 
  FORMAT(dtFrom, 'dd-MMM-yyyy', 'en-US') AS dtFrom_formatted,
  FORMAT(dtTo, 'dd-MMM-yyyy', 'en-US') AS dtTo_formatted,
  service_time
FROM tbl_previous_policy;

Oracle #

用TO_CHAR函数：

SELECT 
  TO_CHAR(dtFrom, 'DD-Mon-YYYY') AS dtFrom_formatted,
  TO_CHAR(dtTo, 'DD-Mon-YYYY') AS dtTo_formatted,
  service_time
FROM tbl_previous_policy;

2. 设置数据库/会话的默认日期格式 #

如果希望所有查询默认都用这个格式显示，可以修改数据库的全局设置或当前会话的参数：

MySQL/MariaDB #

设置会话级别的日期格式：

SET session date_format = '%d-%b-%Y';

之后查询datetime列就会自动按这个格式显示。如果要全局生效，需要修改配置文件（比如my.cnf）里的date_format参数。

Oracle #

设置会话级别的NLS_DATE_FORMAT：

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-Mon-YYYY';

全局设置则需要修改数据库的初始化参数。

不推荐的方案：用VARCHAR存储日期字符串 #

如果你非要在表中直接存储22-Apr-2018这种格式的字符串，可以把dtFrom和dtTo定义为VARCHAR类型，但这会带来很多问题：

• 无法直接进行日期计算（比如计算两个日期的间隔）
• 排序时可能出现错误（字符串排序和日期排序逻辑不同）
• 无法利用datetime类型的索引优化查询
• 容易出现格式不统一的数据（比如有人输入22/04/2018，有人输入2018-04-22）

所以强烈建议还是使用datetime类型存储，在查询时再格式化显示。

内容的提问来源于stack exchange，提问作者user2990735