Handling different date formats in Azure SQL

Question

I need help with writing a stored procedure in Azure SQL that updates datetime values stored as varchar columns with different date formats to a consistent format of 'MM/dd/yyyy hh:mm:ss'. Specifically, the columns whose names end with 'DP' need to be updated. Can someone guide me on how to handle different date formats in Azure SQL and update the columns? Thank you for your assistance.

Thanks,

Deepthi

Answer 1

Hello Sodi, Deepthika,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

Problem

Sequel to your questions, I understand that you need to develop a stored procedure in Azure SQL to standardize datetime values stored as varchar columns with various date formats into a uniform format ('MM/dd/yyyy hh:mm:ss'). This process should specifically target columns ending with 'DP'.

Scenario

Deepthi, who manages databases for a company using Azure SQL Database, has run into a problem with data consistency. The issue stems from datetime values stored as text in columns across the database, each using a different date format. To address this, Deepthi needs to devise a stored procedure that can standardise these varied formats into a single, consistent format ('MM/dd/yyyy hh:mm:ss'). The focus of this procedure should be on columns specifically ending with 'DP'.

Solution

This solution will be based on the scenario given and your questions, while focusing on the problem statement.

Your question:

Can someone guide me on how to handle different date formats in Azure SQL and update the columns.

First to handle different date format.

CREATE PROCEDURE UpdateDateColumns
AS
BEGIN
    DECLARE @DateFormat varchar(50) = 'MM/dd/yyyy hh:mm:ss'; -- Desired date format
    -- Update columns ending with 'DP' to consistent format
    UPDATE YourTableName
    SET YourDateColumn = 
        CASE 
            WHEN YourDateColumn LIKE '%/[0-9][0-9]/[0-9][0-9][0-9][0-9] %' -- Check for date format like 'dd/MM/yyyy'
                THEN CONVERT(varchar(50), TRY_CONVERT(datetime, YourDateColumn, 103), 121) -- Convert to desired format
            WHEN YourDateColumn LIKE '%-[0-9][0-9]-[0-9][0-9][0-9][0-9] %' -- Check for date format like 'yyyy-MM-dd'
                THEN CONVERT(varchar(50), TRY_CONVERT(datetime, YourDateColumn, 120), 121) -- Convert to desired format
            -- Add more conditions for other date formats as needed
            ELSE YourDateColumn -- Leave unchanged if not recognized
        END
    WHERE YourDateColumn IS NOT NULL
      AND YourDateColumn LIKE '%DP'; -- Update columns ending with 'DP'
END

The above code snippet covers common date formats and provides a structured approach to handle different scenarios, there might be some edge cases or specific formats that it doesn't cover perfectly.

Finally

To be more precise and cover some edge cases, especially by directly attempting to convert each varchar column value to datetime using TRY_CONVERT within the CASE statement. It checks for two common date formats (103 and 120) used in SQL Server and converts them to the desired format ('MM/dd/yyyy hh:mm:ss').

CREATE PROCEDURE UpdateDateColumns
AS
BEGIN
    DECLARE @DateFormat varchar(50) = 'MM/dd/yyyy hh:mm:ss'; -- Desired date format
    DECLARE @ColumnName varchar(100); -- Variable to store column name
    
    -- Cursor to iterate through identified columns
    DECLARE column_cursor CURSOR FOR
    SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'YourTableName' -- Replace with your table name
      AND DATA_TYPE = 'varchar'
      AND COLUMN_NAME LIKE '%DP';
    OPEN column_cursor;
    FETCH NEXT FROM column_cursor INTO @ColumnName;
    -- Loop through columns and update datetime values
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC('
            UPDATE YourTableName
            SET ' + @ColumnName + ' = 
                CASE 
                    WHEN TRY_CONVERT(datetime, ' + @ColumnName + ', 103) IS NOT NULL 
                        THEN CONVERT(varchar(50), TRY_CONVERT(datetime, ' + @ColumnName + ', 103), 121) 
                    WHEN TRY_CONVERT(datetime, ' + @ColumnName + ', 120) IS NOT NULL 
                        THEN CONVERT(varchar(50), TRY_CONVERT(datetime, ' + @ColumnName + ', 120), 121) 
                    ELSE ' + @ColumnName + ' 
                END
            WHERE ' + @ColumnName + ' IS NOT NULL
        ');
        
        FETCH NEXT FROM column_cursor INTO @ColumnName;
    END;
    CLOSE column_cursor;
    DEALLOCATE column_cursor;
END;

References

For more reading and review, kindly utilize the additional reading available by the right side of this page. Especially,

1. S2SS0092: The conversion for provided date format is not supported (Error) - SQL Server Describes why SQL Server Migration Assistant (SSMA) for Sybase does not convert a call to CONVERT function with specified style argument.
2. Convert date format with datatype

Source: Microsoft Learn, access 4/27/2024.

https://learn.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-ver16

and

mssql-scripter app.

Accept Answer

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful ** so that others in the community facing similar issues can easily find the solution.

Best Regards,

Sina Salam