sql - SQL Server 即时将斜杠添加到日期

Question

我试图弄清楚如何为我的数据添加某种掩码。目前我有一个这样的查询：

SELECT [EmployeeTC_No] AS "Employee TC#"
      ,[pye_nlast] AS "Name Last"
      ,[pye_nfirst] AS "Name First"
      ,[Dept] AS "Department"
      ,[pye_status] AS "Active"
      ,[HireDate] AS "Hire Date"
      ,[SeparationDate] AS "Separation Date"
  FROM [testing].[dbo].[testing]

雇用日期和分离日期列中的数据显示为 09282015，但我需要日期来显示斜线（“/”），如 09/28/2015 有没有办法为这些动态添加某种掩码？

标签： sqlsql-servertsql

Answer 1

您真正需要做的是修复列的数据类型。varchar不是一种适合所有数据类型的尺寸，不应用于存储日期。就像我说的，09282015不是“之后” ，但根据你的数据，它是。06242019

您可以通过执行以下操作来修复数据：

USE testing;
GO

--Change the data to the ISO yyyyMMdd format
UPDATE dbo.testing
SET HireDate = CONVERT(varchar(8),CONVERT(date,STUFF(STUFF(SeparationDate,5,0,'/'),3,0,'/'),101),112),
    SeparationDate = CONVERT(varchar(8),CONVERT(date,STUFF(STUFF(SeparationDate,5,0,'/'),3,0,'/'),101),112);
GO

--Change the data types
ALTER TABLE dbo.testing ALTER COLUMN HireDate date;
ALTER TABLE dbo.testing ALTER COLUMN SeparationDate date;
GO

--And now you can select, and change the datatype to a format, with ease
SELECT [EmployeeTC_No] AS [Employee TC#],
       [pye_nlast] AS [Name Last],
       [pye_nfirst] AS [Name First],
       [Dept] AS [Department],
       [pye_status] AS [Active],
       CONVERT(varchar(10),[HireDate],101) AS [Hire Date],
       CONVERT(varchar(10),[SeparationDate],101) AS [Separation Date]
  FROM [testing].[dbo].[testing];