0

I am loading a flat file to a database table, and with prior help with SSIS Derived Column task, I was able to convert the date of data type STRING from YYYY-MM-DD in the flat file to MM/DD/YYYY in the database table. However, I notice that it converted the date to include leading zeros in the MM and DD sections of the date, but I need to exclude leading zeroes when the Month and Day are single numbers, e.g. the month of June should be "6" instead of "06", and the date should be "1" instead of "01".

Therefore the full date should be, e.g. 6/1/2019 instead of 06/01/2019.

Is there a way for me to do this in SSIS Derived Column?

If not, how can I code it in SQL-Server?

derivedColumns for Date   Expression 
  year                    SUBSTRING(DateCol, 1, 4)
  day                     RIGHT(DateCol, 2)
  Month                   SUBSTRING(DateCol, 6, 2)

I used the following SSIS Derived Column codes:

 Derived Column Name:  START_DATE 
 Derived Column:       Replace START_DATE
 Expression:           SUBSTRING([START_DATE],6,2) + "/" + RIGHT([START_DATE],2) + "/" + SUBSTRING([START_DATE],1,4)
 DATA TYPE:            Unicode string [DT_WSTR]
 Length:               50

This question was different from the last one. In the last question, the date column was data type DateTime. But in this question, the date is a string, and when I used the Derived Column to change the date from YYYY-MM-DD to MM/DD/YYYY, it kept the leading zeroes in MM and DD. The issue then became, not just changing the date format, but also removing the leading zeroes from the Month and Day.

However, I researched and came up with a better solution in SSIS for changing the date value with data type string, as the database I am working with stores the date in that format.

I removed the Derived Column from my Data Source Task, and added an Execute SQL Task in the Control Flow, then added the following Update statement which not only changes the format from YYYY-MM-DD to MM/DD/YYYY, but also removes the leading zeroes from Month and Day. The CONCAT function I used in the sample SQL below changed the format from YYYY-MM-DD to MM/DD/YYYY, while the Convert function changed the MM and DD values to data type INT which removed any leading Zeros. This solution allowed the date to remain a string, as that was the table format I had to work with. 

UPDATE [StagingTable] 
SET START_DATE = 
CONCAT( CONVERT(INT, SUBSTRING(START_DATE, 6,2)), '/', CONVERT(INT, RIGHT(START_DATE, 2)),'/', LEFT(START_DATE,4) )

Thanks to everyone for their comments, as it helped me to think outside the box and determine this solution.

Sast77
  • 97
  • 1
  • 9
  • 2
    Dates have no format in SQL Server, they are stored as binary values. Formatting is done in the presentation layer. You can't changed the stored "format", because there isn't one. – Thom A Nov 01 '19 at 22:49
  • 1
    Stop right there. If your database columns are not a date datatype (`date`,`datetime`,`datetime2`). Then you're doing it wrong. Never store date data in a non date data type. – Nick.Mc Nov 02 '19 at 01:15
  • Unfortunately, the database I am working with stores the date as `Unicode string [DT_WSTR]`. In SSIS, isn't there anyway to do this conversion to not show any leading zeros? Could Derived Column help here? – Sast77 Nov 04 '19 at 17:25
  • Thanks to everyone for their comments. However, I found a better solution in SSIS. I removed the Derived Column from my Data Source Task, added an Execute SQL Task in the Control Flow, and added the following Update statement, which not only changed the format from YYYY-MM-DD to MM/DD/YYYY, but also removed the leading zeroes from the Month and Day. The Convert statement changed the MM and DD values to data type INT, which removed the leading Zeros while allowing the date to remain a string, as that was the table format I had to work with. The SQL Statement is as follows: – Sast77 Nov 07 '19 at 21:37
  • `UPDATE [StagingTable] SET START_DATE = CONCAT( CONVERT(INT, SUBSTRING(START_DATE, 6,2)), '/', CONVERT(INT, RIGHT(START_DATE, 2)),'/', LEFT(START_DATE,4) )` – Sast77 Nov 07 '19 at 21:39
  • That statement solved the entire issue. – Sast77 Nov 07 '19 at 21:40
  • 1
    **DON'T STORE DATE COLUMNS AS STRINGS!** Instead, take a look at the [`FORMAT()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-ver15) function, with will let you set single values for the month and year specifiers. What you _can't do_, no matter how hard you try, is updated a datetime column in the table to have a specific format. The format for a datetime column is a binary data that isn't human readable, and this is not changeable. What you want to do instead is call `FORMAT()` or `CONVERT()` when you extract data via a SELECT query. – Joel Coehoorn Nov 08 '19 at 14:26
  • Thank you for your advise and for your valued answers. However, I had to maintain the dates as strings because that was the format of the table I got to work with. – Sast77 Nov 12 '19 at 19:50

0 Answers0