如何将字符串转换为日期在SQL中的所有项目在同一列?

Question

目前，SQL表中有一列包含DMMYYYY格式的date，列dtype为VARCHAR。但我想将其转换为日期类型格式，即DD/MM/YYYY格式

当前格式:

8012005
9012005
10012005

所需格式:

08/01/2005
09/01/2005
10/01/2005

我的尝试

SELECT [serial]
,[srn]
,[ticker]
,[series]
,[QT]
,[DQ]
,[DQ/QT]
,[TIMESTAMP]
FROM [Indexer].[dbo].[EquityNSEDAT]
SELECT CONVERT(TIMESTAMP, '%D/%m/%y');

Answer 1

真正的问题是你的表，你需要修复你的设计。为此，需要将列的数据类型从(假定)基于字符串的数据类型更改为date数据类型。我还假设您有坏数据，因此将这些坏数据保留在一个新列中。之后你需要检查这些数据并手动修复。

首先，修正你的设计。这会将字符串从一个模棱两可的值更改为ISO格式(yyyyMMdd)，然后更改列的数据类型:

USE Sandbox;
GO
CREATE TABLE dbo.YourTable (YourDate varchar(8) NULL);
INSERT INTO dbo.YourTable
VALUES ('8012005'),
('9012005'),
('10012005'),
('31112005'),
('4132005');
GO
ALTER TABLE dbo.YourTable ADD BadDate varchar(8) NULL;
GO
UPDATE YT
SET BadDate = CASE WHEN TRY_CONVERT(date,FD.FormattedDate) IS NULL THEN YT.YourDate END,
YourDate = CONVERT(varchar(8),TRY_CONVERT(date,FD.FormattedDate,112),112)
FROM dbo.YourTable YT
CROSS APPLY (VALUES(RIGHT('00000000' + YT.YourDate,8)))PD(PaddedDate)
CROSS APPLY (VALUES(CONCAT(RIGHT(PD.PaddedDate,4), SUBSTRING(PD.PaddedDate,3,2),LEFT(PD.PaddedDate,2))))FD(FormattedDate);
GO
ALTER TABLE dbo.YourTable ALTER COLUMN YourDate date NULL;
GO
SELECT *
FROM dbo.YourTable;
GO
--Get bad data
SELECT BadDate
FROM dbo.YourTable
WHERE BadDate IS NOT NULL;
GO
DROP TABLE dbo.YourTable;

完成这些之后，要使值显示为您想要的，请在表示层中配置它，而不是SQL层;在SQL层中保留数据类型为date。

Answer 2

要在SQL查询中将DMMYYYY格式的日期(例如"8012005")转换为DD/MM/YYYY格式(例如"08/01/2005")，请使用convert()函数和适当的样式参数。

在您的示例中，下面的查询将TIMESTAMP列从DMMYYYY格式转换为DD/MM/YYYY格式:

SELECT [serial]
,[srn]
,[ticker]
,[series]
,[QT]
,[DQ]
,[DQ/QT]
,CONVERT(DATE, TIMESTAMP, 105) AS TIMESTAMP
FROM [Indexer].[dbo].[EquityNSEDAT]