SSIS convert string with no 0's to date format

Question

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?

Fix the source. How are you going to know if `1122002` is Feb 11 or Dec 1? — Aaron Bertrand, Jan 07 '22 at 00:40

Hadi · Answer 1 · 2022-01-07T00:46:50.813

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.

John Cappelletti · Answer 2 · 2022-01-07T03:42:37.907

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

SSIS convert string with no 0's to date format

2 Answers2