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

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

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

 

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')
Source Code

 

下面是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
Source Code

 

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

 

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

 

昨晚分享之后,发现程序有一些问题,现在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
Source Code

 

再次检验结果:

 

posted @ 2019-05-30 22:19 Insus.NET 阅读(...) 评论(...) 编辑 收藏