sql - 将小时/分钟添加到日期时间
问题描述
我在如何将偏移量添加到 EventDateTime 时遇到问题。有没有办法做到这一点?我会将其转换为十进制数,但不能,因为 5:30 不是 5.3 小时,而是 5 小时 30 分钟。
数据如下所示,具有预期的输出。如果可能,请提供帮助。
样本数据:
+-----------+-----------+---------------------+-------------------------+--------+
| EventDate | EventTime | EventDateTime | TimeZone | Offset |
+-----------+-----------+---------------------+-------------------------+--------+
| 10/1/2020 | 5:00 PM | 2020-10-01 5:00 PM | UTC +5:30 IST New Delhi | +5:30 |
| 10/1/2020 | 8:00 PM | 2020-10-01 8:00 PM | UTC +5:30 IST New Delhi | +5:30 |
| 10/2/2020 | 10:00 AM | 2020-10-02 10:00 AM | UTC -4 EDT New York | -4 |
| 10/2/2020 | 10:00 AM | 2020-10-02 10:00 AM | UTC -7 PDT Los Angeles | -7 |
| 10/2/2020 | 2:00 PM | 2020-10-02 2:00 PM | UTC +1 BST London | 1 |
| 10/2/2020 | 3:00 PM | 2020-10-02 3:00 PM | UTC -7 PDT Los Angeles | -7 |
| 10/2/2020 | 8:00 AM | 2020-10-02 8:00 AM | UTC -7 PDT Los Angeles | -7 |
| 10/5/2020 | 10:00 AM | 2020-10-05 10:00 AM | UTC +5:30 IST New Delhi | +5:30 |
| 10/5/2020 | 10:00 AM | 2020-10-05 10:00 AM | UTC -5 CDT Chicago | -5 |
+-----------+-----------+---------------------+-------------------------+--------+
预期结果:
+-----------+-----------+---------------------+-------------------------+--------+---------------------+
| EventDate | EventTime | EventDateTime | TimeZone | Offset | UTCDateTime |
+-----------+-----------+---------------------+-------------------------+--------+---------------------+
| 10/1/2020 | 5:00 PM | 2020-10-01 5:00 PM | UTC +5:30 IST New Delhi | +5:30 | 2020-10-01 10:30 PM |
| 10/1/2020 | 8:00 PM | 2020-10-01 8:00 PM | UTC +5:30 IST New Delhi | +5:30 | 2020-10-02 1:30 AM |
| 10/2/2020 | 10:00 AM | 2020-10-02 10:00 AM | UTC -4 EDT New York | -4 | 2020-10-02 6:00 AM |
| 10/2/2020 | 10:00 AM | 2020-10-02 10:00 AM | UTC -7 PDT Los Angeles | -7 | 2020-10-02 3:00 AM |
| 10/2/2020 | 2:00 PM | 2020-10-02 2:00 PM | UTC +1 BST London | 1 | 2020-10-02 3:00 PM |
| 10/2/2020 | 3:00 PM | 2020-10-02 3:00 PM | UTC -7 PDT Los Angeles | -7 | 2020-10-02 8:00 AM |
| 10/2/2020 | 8:00 AM | 2020-10-02 8:00 AM | UTC -7 PDT Los Angeles | -7 | 2020-10-02 1:00 AM |
| 10/5/2020 | 10:00 AM | 2020-10-05 10:00 AM | UTC +5:30 IST New Delhi | +5:30 | 2020-10-05 3:30 PM |
| 10/5/2020 | 10:00 AM | 2020-10-05 10:00 AM | UTC -5 CDT Chicago | -5 | 2020-10-05 5:00 AM |
+-----------+-----------+---------------------+-------------------------+--------+---------------------+
到目前为止我做了什么:
解决方案
不完全漂亮,但它的工作...
DECLARE @eventDateTime VARCHAR(50), @offset varchar(10)
SET @eventDateTime = '2020-09-30 5:00AM'
SET @offset = '-5:30'
SELECT CASE WHEN @offset LIKE '-%' THEN CONVERT(DATETIME,@eventDateTime) - REPLACE(@offset,'-','')
ELSE CONVERT(DATETIME,@eventDateTime) + REPLACE(@offset,'+','')
END
推荐阅读
- mongodb - 为没有引用的嵌套数据确定数据库的类型和结构
- java - 我如何验证我是否将属性添加到相等的列表中?
- gcloud - 无法在云构建中使用 gcloud scp 复制隐藏文件 - 远程构建器
- python - How to copy specific files from the sub-folders to a new folder in python?
- python - CNN 为生产中的不同图像返回相同的分类
- javascript - 运行 grunt 任务时出现 grunt 语法错误问题
- freeradius - Chilli 不会断开与 freeradius 的客户端会话
- sql - 如何编写sql查询来检查房间可用性
- visual-studio-code - 卸载了我能找到的所有东西,但扩展主机仍然意外终止
- xslt - 将 XSLT 中的变量字符串与 IF 条件进行比较