给不合法的格式转换成标准格式(案例)

网上有人问，数据格式转换，需要给不合法的格式转换成标准格式：

创建一张表，把网友提供的数据填充至表：

 

CREATE TABLE [dbo].[DT]
([Source] NVARCHAR(50))

INSERT INTO [dbo].[DT] ([Source]) VALUES
('2017-05-31 15：00'),('2017-06-02 16：15'),('2017-06-12 14：00'),('2017-06-16 10：30'),('2017-06-16 13：00'),('2017-06-26 8：30'),
('2017-06-29 11：00'),('2017-07-17 16：20'),('2017-07-23 11：00'),('2017-08-0917：15'),('2017-08-09 9：30'),('2017-08-1020:30'),
('2017-08-15 17：20'),('2017-08-1621:00'),('2017-08-17 20：00'),('2017-08-21 10：30'),('2017-08-2116:30'),('2017-08-21 20：30'),
('2017-08-23 17：30'),('2017-08-24 15：30'),('2017-08-29 11：30'),('2017-08-3017:30'),('2017-09-02 10：00'),('2017-09-0415:45'),
('2017-09-05 15：30'),('2017-09-07 9：40'),('2017-09-12 15：30'),('2017-09-12 16：00'),('2017-09-13 13：00'),('2017-09-14 15：30'),
('2017-09-14 19：10'),('2017-09-14 21：00'),('2017-09-15 9：40'),('2017-09-25 9：50'),('2017-09-25 22：45'),('2017-11-03'),
('2017-10-06 15：30'),('2017-10-3016:00'),('2017-11-01 11：30'),('2017-11-1015:20'),('2017-12-05 16：30'),('2017-12-19 16：50'),('2018-07-18 16：00')

 

下面是Insus.NET的解决方法，写一个自定义函数：

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Insus.NET
-- Blog:        https://insus.cnblogs.com
-- Create date: 2019-05-30
-- Update date: 2019-05-30
-- Description: 转换正确日期
-- =============================================
CREATE FUNCTION [dbo].[svf_ConvertToCorrectDateTime] 
( 
    @DateTimeString NVARCHAR(MAX) 
)
RETURNS DATETIME
AS
BEGIN
    DECLARE @Source NVARCHAR(MAX) = LTRIM(RTRIM(ISNULL(@DateTimeString,'')))
    
    DECLARE @date_Str NVARCHAR(10) = N''
    DECLARE @hour_Str NVARCHAR(2) = N'00'
    DECLARE @minute_Str NVARCHAR(2) = N'00'
    DECLARE @second_Str NVARCHAR(2) = N'00'
   
    IF LEN(@Source) >= 10         
        SET @date_Str = SUBSTRING(@Source,1,10)

    IF LEN(@Source) - 10 > 0
    BEGIN
        DECLARE @time_Str NVARCHAR(30) = RTRIM(LTRIM(SUBSTRING(@DateTimeString,11,LEN(@DateTimeString))))        
        SET @hour_Str = SUBSTRING(@time_Str,1,PATINDEX('%[^0-9]%',@time_Str) -1)
        SET @minute_Str = SUBSTRING(REVERSE(@time_Str),1,PATINDEX('%[^0-9]%',REVERSE(@time_Str)) -1)    
    END
    
    RETURN  @date_str + ' ' + @hour_Str + ':' + @minute_Str + ':' + @second_Str
END

 

下面是检验函数和网友提供的数据：

 

SELECT [Source],[dbo].[svf_ConvertToCorrectDateTime] ([Source]) FROM [DT]

 

昨晚分享之后，发现程序有一些问题，现在Insus.NET把它修正了，参考：

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Insus.NET
-- Blog:        https://insus.cnblogs.com
-- Create date: 2019-05-30
-- Update date: 2019-05-31
-- Description: 转换正确日期
-- =============================================
CREATE FUNCTION [dbo].[svf_ConvertToCorrectDateTime] 
( 
    @DateTimeString NVARCHAR(MAX) 
)
RETURNS DATETIME
AS
BEGIN
    DECLARE @Source NVARCHAR(MAX) = LTRIM(RTRIM(ISNULL(@DateTimeString,'')))
    
    DECLARE @date NVARCHAR(10) = N''
    DECLARE @time NVARCHAR(20) = N''
   
    IF LEN(@Source) >= 10         
        SET @date = SUBSTRING(@Source,1,10)

    IF LEN(@Source) - 10 > 0
    BEGIN      
        DECLARE @time_Str NVARCHAR(30) = REPLACE(RTRIM(LTRIM(SUBSTRING(@DateTimeString,11,LEN(@DateTimeString)))),' ','')     
        DECLARE @I INT = 1
                
        WHILE @I <= LEN(@time_Str) 
        BEGIN 
            SET @time = @time + CASE WHEN SUBSTRING(@time_Str, @I, 1) LIKE '[0-9]' THEN SUBSTRING(@time_Str, @I, 1) ELSE N':' END 
   
            SET @I = @I + 1 
        END

        WHILE (CHARINDEX('::',@time) <> 0) 
            SET @time = REPLACE(@time,'::',':')   
    END
    
    RETURN  @date + ' ' + @time
END

 

再次检验结果：