将 nvarchar 列更改为日期格式 [英] Changing a nvarchar column into a Date format

问题描述

我有一个名为 DT_APP 的列，其中包含两种格式的日期

I have a column called DT_APP which contains dates in two formats

1. 2012 年 9 月 4 日下午 6:19
2. 2013-04-30 23:38.34

我需要将列中的 1) 转换为 2) 但我需要它在 nvarchar(19) 中，因为数据类型在 nvarchar(19).

I need to convert 1) into 2) in the column but I need it to be in nvarchar(19) as the data type is in nvarchar(19).

我很欣赏它应该是 Datetime 格式，但它被设置为 nvarchar(19).

I appreciate it should be in Datetime format but Its been set to nvarchar(19).

谢谢，

推荐答案

因此，假设您的专栏只有这两种格式，那么您可以执行以下操作:

So, assuming that those are the only two formats of your column, then you can do the following:

SELECT CONVERT(NVARCHAR(19),CONVERT(DATETIME,DT_APP,100),120)
FROM YourTable
WHERE DT_APP LIKE '%[aA-zZ]%'

更新

好的，如果你想要另一列，那么你可以先创建它然后填充值:

Ok, if you want another column, then you can first create it and then fill the values:

-- First create a new column
ALTER TABLE YourTable
ADD DT_APP2 DATETIME;

-- Fill that column with DATETIME values
UPDATE YourTable
SET DT_APP2 =   CASE WHEN DT_APP LIKE '%[aA-zZ]%'
                THEN CONVERT(DATETIME,DT_APP,100)
                ELSE CONVERT(DATETIME,DT_APP,120) END

之后，您可以检查该列以查看值是否正确，然后才能删除DT_APP 列.

After that, you can check the column to see if the values are correct and only then you should delete the DT_APP column.

更新 2如果您只需要更新当前值，那么只需执行以下操作:

UPDATE 2 If you just need to update current values, then just do:

UPDATE YourTable
SET DT_APP = CONVERT(NVARCHAR(19),CONVERT(DATETIME,DT_APP,100),120)
WHERE DT_APP LIKE '%[aA-zZ]%'

这篇关于将 nvarchar 列更改为日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！