sql - Confused with DateTime format

Question

I have String like 'Wed Jan 22 13:30:52 UTC 2020', need to convert this string To IST format using Sql Server.

I don't aware the about above format.

标签： sqlsql-server

Answer 1

这似乎不是 SQL Server 内部识别的任何格式：

https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

所以，你必须自己转换它。假设我们有您的样本日期：

declare @date varchar(50) = 'Wed Jan 22 13:30:52 UTC 2020'

您可以使用子字符串获取日期部分（假设每个日期都使用完全相同的格式）：

select substring(@date, 1, 3) as day,
       substring(@date, 5, 3) as month,
       substring(@date, 9, 3) as year,
       substring(@date, 12, 2) as hour,
       substring(@date, 15, 2) as minute,
       substring(@date, 18, 2) as second,
       substring(@date, 25, 4) as utc

现在您只需要从这些日期部分构建一个日期时间值：

select datetimefromparts(convert(int, substring(@date, 9, 3)) + 2000,
                         case substring(@date, 5, 3) when 'Jan' then 1
                                                     when 'Feb' then 2
                                                     ...     
                         end,
                         case substring(@date, 1, 3) when 'Mon' then 1
                                                     when 'Tue' then 2
                                                     when 'Wed' then 3
                                                     ...  
                         end,                        
                         convert(int, substring(@date, 12, 2)),
                         convert(int, substring(@date, 15, 2)),
                         convert(int, substring(@date, 18, 2)),
                         0)

最后，您可以添加AT TIME ZONE 'India Standard Time'以定义您的 IST 时区。

select datetimefromparts(convert(int, substring(@date, 9, 3)) + 2000,
                         case substring(@date, 5, 3) when 'Jan' then 1
                                                     when 'Feb' then 2
                                                     ...     
                         end,
                         case substring(@date, 1, 3) when 'Mon' then 1
                                                     when 'Tue' then 2
                                                     when 'Wed' then 3
                                                     ...  
                         end,                        
                         convert(int, substring(@date, 12, 2)),
                         convert(int, substring(@date, 15, 2)),
                         convert(int, substring(@date, 18, 2)),
                         0)
       AT TIME ZONE 'India Standard Time'