CODEWITHSUNDEEP
sql-serverdatessisetlderived-column
HappyCoder123
HappyCoder123

Reputation: 67

SSIS convert string with no 0's to date format

I'm working trying to convert a string data type to a date that does not contain 0's in the string of the month and day part and need to convert it into a date format yyyy-MM-dd, anything that is a single digit will come without a zero

For example, the dates come in as

162022  it should be (01-06-2022)

or

762022 it should be (07-06-2022)

or

1262022 it should be (12-06-2022)

How can I get the proper formatting to get it into a date data type using a Derived Column?

Upvotes: 1

Views: 318

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81930

You could try a brute-force approach, but as the sample below illustrates, there can be more than 1 possible date.

It is a risky format, and extreme caution is required.

Example

Declare @YourTable Table ([SomeCol] int)  Insert Into @YourTable Values 
 (162022)
,(7182022)
,(1262022)
,(12182022)

Set DateFormat MDY

Select SomeCol
      ,AsDate 
      ,Cnt    = sum(1) over (partition by [SomeCol])
 From  @YourTable A
 Cross Apply ( values ( stuff(reverse(SomeCol),5,0,'-') ) ) B(rStr)
 Cross Apply ( values ( stuff(rStr,7,0,'-') ) 
                     ,( stuff(rStr,8,0,'-') ) 
                     ,( stuff(rStr,9,0,'-') ) 
             )  C(tStr)
 Cross Apply ( values ( try_convert(date,reverse(tStr)) ) ) D(AsDate)
 Where AsDate is not null

Results

SomeCol     AsDate      Cnt
162022      2022-01-06  1
1262022     2022-01-26  2  <<< Two valid dates
1262022     2022-12-06  2  <<< Two valid dates
7182022     2022-07-18  1
12182022    2022-12-18  1

Upvotes: 1

Hadi
Hadi

Reputation: 37313

Try using the following expression:

LEN([StringColumn]) == 6 ? "0" + LEFT([StringColumn],1) + "-0" + SUBSTRING([StringColumn],2,1) + "-" + RIGHT([StringColumn],4) : 
LEN([StringColumn]) == 8 ? LEFT([StringColumn],2) + "-" + SUBSTRING([StringColumn],3,2) + "-" + RIGHT([StringColumn],4) :
LEN([StringColumn]) == 7 ? 
(DT_I4)SUBSTRING([StringColumn],2,2) > 12 ? LEFT([StringColumn],2) + "-0" + SUBSTRING([StringColumn],3,1) + "-" + RIGHT([StringColumn],4) : "0" + LEFT([StringColumn],1) + "-" + SUBSTRING([StringColumn],2,2) + "-" + RIGHT([StringColumn],4) : [StringColumn]

This expression logic can be summarized in the following table

Input length Date Format Example How to convert into date
6 dmyyyy 162021 add zero before the first and second digits
7 dmmyyyy or ddmyyyy 1262021 or 2122021 check if the second and third digits are greater than 12 then consider that the first two digits are for the day part else consider only the first digit for the day
8 ddmmyyyy 01062021 do nothing
< 6 or > 8 ? ? do nothing

This expression is valid except with values like 1112021 where it is not easy to predict if the day part is 11 or the month part us 11.

Upvotes: 1

Related Questions