时间戳

首页 > 解决方案 > 如何检查sql server中是否存在多种日期格式？

sql - 如何检查sql server中是否存在多种日期格式？

问题描述

我需要检查 sql server 的同一列中是否存在多个日期格式？

列的数据类型是 nvarchar

drop table dateformats

create table dateformats(datevalue varchar(50))

insert dateformats
values ('01-january-2019'),('31-JAN-2019'),('2019-01-01'),
('16-07-2019'),('20-12-2019'),('16-10-2019'),('16-AUG-2019'),('AUG-07-2019'),('16/07/2019'),('07/16/2019'),('01-jan-2019'),('01-january-2019'),(''),(NULL)

SELECT datevalue  
FROM dateformats

返回以显示该列具有多种日期格式的查询。

标签： sqlsql-server

解决方案

您可以尝试使用此查询。

现在，我已经通过 ofDMY和MDYin 查询修复了 4 种格式，但是如果您已经在数组或 SQL 表中拥有所有格式，您可以在查询中使用它。

此查询将返回您在表的特定列中使用的所有日期格式

SET DATEFORMAT dmy 
SELECT formats into #temp FROM (

SELECT 
max( 
CASE WHEN isdate(datevalue) = 1 then
(CASE WHEN CAST(datevalue AS VARCHAR(50)) = CAST(FORMAT(CAST(datevalue AS DATE),'dd-MM-yyyy') AS VARCHAR(50)) THEN 'dd-MM-yyyy' ELSE '' END)
ELSE '' END )
AS formats from dateformats 

UNION 

SELECT 
max( 
CASE WHEN isdate(datevalue) = 1 then
(CASE WHEN CAST(datevalue AS VARCHAR(50)) = CAST(FORMAT(CAST(datevalue AS DATE),'dd/MM/yyyy') AS VARCHAR(50)) THEN 'dd/MM/yyyy' ELSE '' END)
ELSE '' END )
AS formats from dateformats 

UNION 

SELECT 
max( 
CASE WHEN isdate(datevalue) = 1 then
(CASE WHEN CAST(datevalue AS VARCHAR(50)) = CAST(FORMAT(CAST(datevalue AS DATE),'dd-MMM-yyyy') AS VARCHAR(50)) THEN 'dd-MMM-yyyy' ELSE '' END) 
ELSE '' END )
AS formats from dateformats

) AS a

WHERE formats <> '';

  set dateformat MDY  insert into #temp
    SELECT formats  FROM (

    SELECT 
    max( 
    CASE WHEN isdate(datevalue) = 1 then
    (CASE WHEN CAST(datevalue AS VARCHAR(50)) = CAST(FORMAT(CAST(datevalue AS DATE),'MM-dd-yyyy') AS VARCHAR(50)) THEN 'MM-dd-yyyy' ELSE '' END)
    ELSE '' END )
    AS formats from dateformats 

    UNION 

    SELECT 
    max( 
    CASE WHEN isdate(datevalue) = 1 then
    (CASE WHEN CAST(datevalue AS VARCHAR(50)) = CAST(FORMAT(CAST(datevalue AS DATE),'MM/dd/yyyy') AS VARCHAR(50)) THEN 'MM/dd/yyyy' ELSE '' END) 
    ELSE '' END )
    AS formats from dateformats

    ) AS b

    WHERE formats <> '';

select * from #temp

演示

推荐阅读