SQL 转换日期时间并减去小时数 [英] SQL convert datetime and subtract hours
问题描述
我正在尝试将日期时间 (createTime) 字段从其默认的 2012-10-06 02:29:37.243 格式转换为 am/pm 所以我使用
I am trying to convert a datetime (createTime) field from its default 2012-10-06 02:29:37.243 format to am/pm so I use
convert(varchar, CreateTime, 100) as CreateTime
并获得 2012 年 10 月 6 日凌晨 4:29.
到目前为止一切顺利,但在同一个语句中,我想减去 4 小时,然后显示该字段(格林威治标准时间问题).
So far so good but in the same statement I want to subtract 4 hours and then display the field (GMT issues).
我在这里浏览了一些帖子,但找不到任何可以在单个语句中完成的内容.
I have gone through some posts here but could not find anything that can do it in single statement.
推荐答案
declare @createTime datetime = '2012-10-06 02:29:37.243';
-- original value, default formatting
select @createTime;
-- formatted
select convert(varchar, @createTime, 100);
-- subtract 4 hours, formatted
select convert(varchar, dateadd(hour, -4, @createTime), 100);
上面使用 dateadd
的查询将始终减去 4 小时.如果您的目标是将任意日期时间从 UTC 转换为本地时间,那么它会更加复杂,因为您需要添加/减去的偏移量取决于原始日期时间.像 -4 这样的单一值并不总是有效.以下是处理一般情况的一些想法:
The query above that uses dateadd
will always subtract 4 hours. If your goal is to convert an arbitrary datetime from UTC to local time, then it's more complicated because the offset that you need to add/subtract depends on the original datetime. A single value like -4 won't always work. Here are some ideas for dealing with the general case:
有效转换日期SQL 2005 中 UTC 和本地(即 PST)时间之间的时间
这篇关于SQL 转换日期时间并减去小时数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!