Search code examples
sqlsql-servert-sql

Convert and insert incorrect date format of example ''May 1 202'' to date format

I have a table called invoice where columns as 'invoiceno', 'invoicedate', 'invoicedate2'. Due to a mistake the invoicedate column datatype is set as nvarchar(10) and the data is passed as date therefore the data is saved in the format of below example: "May 1 202" the year last character is missing and I need to change the data into correct format for year 2024 and save as datetime format in column invoicedate2

Expected is May 1 202 --> 2024-05-01

Solution

  • You can try something like:

    UPDATE invoice
SET invoicedate2 = NULLIF(COALESCE(
    TRY_CONVERT(DATETIME, invoicedate),
    TRY_CONVERT(DATETIME, invoicedate + '4')
    ), '1900-01-01')

    The above will attempt to convert the date text first without and then with a "4" appended. If unsuccessful, the result is null. Note that an empty string will convert to a 1900-01-01, so the NULLIF() function replaces that with a null.

    If you know that all values follow the "mmm dd yyy" (truncated) form, you can use the following simplified version.

    UPDATE invoice
SET invoicedate2 = CONVERT(DATETIME, invoicedate + '4')

    Sample results:

    invoicedate invoicedate2
    2024-01-31 2024-01-31 00:00:00.000
    MAY 1 202 2024-05-01 00:00:00.000
               null
    junk null
    null null

    See this db<>fiddle.