如何将Datetime值日期部分默认设为1900-01-01并保留时间

嘿，这个需求我之前帮不少人解决过，针对不同的SQL数据库，实现方式略有不同，我给你整理了几种主流数据库的可行方案，直接上手就能用：

SQL Server #

有两种简洁的方式：

1. 利用类型转换：SQL Server中把datetime/datetime2类型转成time类型会自动丢弃日期部分，再转回datetime时默认日期就是1900-01-01，完美符合你的需求：

SELECT CAST(CAST(your_datetime_column AS time) AS datetime) AS modified_datetime
FROM your_table;

比如原值是2020-02-08 19:58:18.320，执行后会得到1900-01-01 19:58:18.320。

2. 日期偏移计算：通过计算原日期与1900-01-01的天数差，再把这个差值从原日期中减去，本质是保留时间部分：

SELECT DATEADD(day, DATEDIFF(day, your_datetime_column, '1900-01-01'), your_datetime_column) AS modified_datetime
FROM your_table;

这种方法在处理一些特殊精度的datetime类型时兼容性更好。

MySQL #

MySQL可以通过提取时间部分再与固定日期拼接来实现：

1. 使用TIMESTAMP函数：直接把固定日期和提取的时间部分组合成timestamp：

SELECT TIMESTAMP('1900-01-01', TIME(your_datetime_column)) AS modified_datetime
FROM your_table;

2. 字符串转换法：先把时间部分转成字符串，再用STR_TO_DATE转成datetime：

SELECT STR_TO_DATE(TIME(your_datetime_column), '%H:%i:%s.%f') AS modified_datetime
FROM your_table;

注：%f用来匹配毫秒/微秒部分，如果你的时间没有小数位，可以去掉.%f。

PostgreSQL #

PostgreSQL中可以通过时间偏移的方式实现：

1. 直接拼接时间部分：把固定日期和原字段的时间部分相加：

SELECT TIMESTAMP '1900-01-01' + (your_datetime_column::time) AS modified_datetime
FROM your_table;

2. 计算时间差后叠加：先计算原日期的时间偏移量（即当天的时间部分），再加到1900-01-01上：

SELECT (your_datetime_column - DATE_TRUNC('day', your_datetime_column)) + TIMESTAMP '1900-01-01' AS modified_datetime
FROM your_table;

• 不同数据库对时间精度的支持不同，如果你的字段包含微秒级数据，要确保使用的函数能保留这些精度（比如上述方案都支持毫秒/微秒）；
• 如果你的字段是NULL，这些函数都会返回NULL，如果需要默认值，可以搭配COALESCE函数处理。

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