CODEWITHSUNDEEP
sqlsql-serverssisetlderived-column
an1234
an1234

Reputation: 165

replace string with date value

Currently, I have a Derived Column transformation within my package that will look for a NULL value and if it is NULL it will give it a default date with forwarding slashes as seen below

REPLACENULL([date],"2/2/1999")

However, if that field is Not NULL it will have a string date which will look like 20200202. I am wanting to add on to the current expression to where if the field is not null that it replaces 20200202 with 2020-02-02. I would appreciate the help and will rate it highly!

Upvotes: 1

Views: 708

Answers (2)

Hadi
Hadi

Reputation: 37313

First of all, you should use 1999-02-02 as a default value to make sure that all values are stored in the same format. The following expression will convert the date format from yyyyMMdd to yyyy-MM-dd:

REPLACENULL([date],"") == "" ? "1999-02-02" : LEFT([date],4) + "-" + SUBSTRING([date],5,2) + "-" + RIGHT([date],2)

Upvotes: 0

critical_error
critical_error

Reputation: 6706

Here's how I would do that in SQL Server.

Note: FORMAT requires SQL Server 2012+

DECLARE @StringDate varchar(20);

    -- NON-NULL date.
    SET @StringDate = '20200202'
    SELECT
        ISNULL ( FORMAT( CAST ( @StringDate AS date ), 'yyyy-MM-dd' ), '2/2/1999' ) AS NonNullStringDate;

    -- NULL date.
    SET @StringDate = NULL;
    SELECT
        ISNULL ( FORMAT( CAST ( @StringDate AS date ), 'yyyy-MM-dd' ), '2/2/1999' ) AS NullStringDate;

Returns (respectively)

+-------------------+
| NonNullStringDate |
+-------------------+
| 2020-02-02        |
+-------------------+

+----------------+
| NullStringDate |
+----------------+
| 2/2/1999       |
+----------------+

For date formatting consistency, you may want to consider changing 2/2/1999 to 1999-2-2.

Upvotes: 1

Related Questions