CODEWITHSUNDEEP
sql-servert-sqlssis
Mayur Darji
Mayur Darji

Reputation: 87

Change Date format from yyyy-mm-dd to mm/dd/yyyy Derived Column

I have a .txt flat file source. I am importing that into ssis to do some validation, manipulation to generate a .csv file. Thus: the date comes in as string and needs to go out as string, the output is not for a database, but a csv that is loaded by a data import tool.

I am in need to convert the date format which is in yyyy-mm-dd to mm/dd/yyyy for certain date column. For example: DOB column has date 1984-03-16 needs to be converted to 03/16/1984. I am using derived column transformation but not able to figure it out.

Upvotes: 2

Views: 7597

Answers (2)

M.Ali
M.Ali

Reputation: 69564

Say your Date Column is called DateCol

You will need to extract Date parts from the column using string function and then you can use those date parts to form a date in the format you need.

Expressions to Extract Date Parts

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

The Expression for the derived column:

NewDateColumn AS   SUBSTRING(DateCol, 6, 2) 
                 + "/" 
                 + RIGHT(DateCol, 2)
                 + "/"
                 + SUBSTRING(DateCol, 1, 4)

Your NewDate Column will be in your desired format

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280570

It wasn't clear if the original column was DATE or a string. Here is an example that demonstrates transforming the date, as requested, for both:

DECLARE @t TABLE(d DATE, s CHAR(10));

INSERT @t VALUES('1984-03-16', '1984-03-16');

SELECT d,
  CONVERT(CHAR(10), d, 101),
  CONVERT(DATE, s),
  CONVERT(CHAR(10), CONVERT(DATE, s), 101)
FROM @t;

Results:

1984-03-16          03/16/1984            1984-03-16          03/16/1984
^^^^^^^^^^ date     ^^^^^^^^^^ string     ^^^^^^^^^^ date     ^^^^^^^^^^ string

If you are storing this as a string, you really shouldn't be doing that. You lose all kinds of important things when you choose the wrong data type for things you think you need, like format. Validation is the biggest one - if you use a CHAR(10) for YYYY-MM-DD dates, what is stopping someone from entering 9999-99-99 or yeah-no-go?

In any case, this is a formatting job that is much better handled on the other end of whatever is consuming this data. Until it is printed on a piece of paper or displayed on a report, there is absolutely no reason to take a date or datetime value and coerce it to act like a string. Even at the presentation side I question the wisdom of presenting a regional, ambiguous format like mm/dd/yyyy. Are you sure your entire audience, present and future, understands that 09/06/2013 is September 6th and not June 9th? Have any friends in England or Canada you want to run that by?

Upvotes: 3

Related Questions