将DD-MON-YYYY AM / PM转换或转换为YYYY-MM-DD [英] Cast or convert DD-MON-YYYY AM/PM to YYYY-MM-DD

问题描述

我一直在寻找如何转换的答案：
07-DEC-18 01.00.54.984000 PM 到 2018 -12-07 13.00.54.984000

I'm stuck in finding an answer on how to convert: 07-DEC-18 01.00.54.984000 PM to 2018-12-07 13.00.54.984000

我认为时间 01.00.54 PM 是13小时0分54秒

I think the time of 01.00.54 PM is 13hours , 0 minutes and 54 seconds

我尝试用112进行转换，但我仍然找不到如何转换或转换它的方法。

I have try to convert with 112 but still i can't find out how to cast or convert this.

推荐答案

下面是一种方法，它可以分别转换日期和时间部分，然后使用 DATEADD 合并结果。假设实际时间精度不大于毫秒。请注意，您需要使用 datetime2 而不是 datetime 来避免四舍五入到1/300毫秒。

Below is one method that converts the date and time portions separately, and then uses DATEADD to combine the results. This assumes the actual time precision is not greater than milliseconds. Note that you need to use datetime2 instead of datetime to avoid rounding to 1/300 milliseconds.

DECLARE @DateTimeString varchar(30) = '07-DEC-18 01.00.54.984000 PM';
SELECT DATEADD(
      millisecond
    , DATEDIFF(millisecond, '', CAST(REPLACE(SUBSTRING(@DateTimeString, 11, 8), '.', ':') + RIGHT(@DateTimeString, 10) AS time))
    , CAST(LEFT(@DateTimeString, 9) AS datetime2)
    );

这篇关于将DD-MON-YYYY AM / PM转换或转换为YYYY-MM-DD的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！