Convert Date field to YYYYMMDD from YYYY-MM-DD in SSIS

Question

Convert Date field to YYYYMMDD from YYYY-MM-DD in SSIS

Oscar_Boots

Old Hand

Points: 362
More actions
May 15, 2020 at 1:37 am

#3751880

Hi,
I've got a table containing Dates in YYYY-MM-DD which I need to change to YYYYMMDD in SSIS.
I've made some progress after watching this video from Steve Fox.
https://www.youtube.com/watch?v=fETNQjkM6mc
I'm using SSIS & this all works OK until I want to create a Derived Column on a Date field.
In the video, some expressions are created to creaate the parts of the Date format such as one for 'YY', one for MM etc. They are then Concatenated and a Data Conversion Modules changes the data type to Date.
I found that using RIGHT won't work on dates, so my workaround is to use the Data Conversion Module to change the Date Data Type to String & then try the Derived Column to apply those String functions.
The problem is that no String format I want to apply to this Date field will allow me to then use in the Derived Column transformation?
Can anyone help here? I know the syntax for the Derived Column is correct because it works on non Date fields.
Thanks Peter

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

don't do that on SSIS - do it on the select from the table where it will be a lot easier to do and will be faster than doing it on SSIS.

And just to make sure of what you are saying - your source table has a column type DATE or does it have a column type CHAR/VARCHAR with a string that represents a data?

Oscar_Boots Old Hand Points: 362 More actions · Answer 2

Thanks frederico_fons,

Ok, I could create a Table that converts the Date (My date fields are in Data Type Date) to the 'YYYYMMDD' format

Can you help me change the Format without changing the Data Type?

Thanks

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

I think you have issues understanding exactly what you need - a DATE field has no format.

you can convert a date to any format you want for writing to a file or for displaying on screen - but once you write to a file for example it is no longer a DATE datatype but it is a string representation of a date

And no I am not telling you to create a table with a varchar field representing a date.

what I am saying is on your dataflow, when you select from your source table, you CONVERT your date to a string on your required format - on this case it would be convert(varchar(8), mydatefield, 112) - where 112 represents format YYYYMMDD (RTFM is quite helpful on these!!!) so when it goes to your destination it goes as a STRING - not a date object

Oscar_Boots Old Hand Points: 362 More actions · Answer 4

Thanks frederico_fons,

I get what your saying as I've noticed that any transform I've been doing changes the data type to String. So I thought the best idea was to use a select statement first & then connect the table to an SSIS package and change it back to Date using a Data Conversion Transform.

This task needs to be used in an existing SSIS Package.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

Oscar_Boots wrote:

Thanks frederico_fons,
I get what your saying as I've noticed that any transform I've been doing changes the data type to String. So I thought the best idea was to use a select statement first & then connect the table to an SSIS package and change it back to Date using a Data Conversion Transform.
This task needs to be used in an existing SSIS Package.

It's not really clear what you are trying to achieve and why. If you have a source column which has a datatype of DATE, why do you need to transform it at all?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Oscar_Boots Old Hand Points: 362 More actions · Answer 6

Thanks Phil, I appreciate your advice but this data is to be loaded into an CRM system that will only accept dates in this format.

I've since thought that I could put the SQL into a Stored Proc & create a Table in SSIS with the dates as strings & then change the format using the Data Conversion Transformation.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 7

Oscar_Boots wrote:

Thanks Phil, I appreciate your advice but this data is to be loaded into an CRM system that will only accept dates in this format.
I've since thought that I could put the SQL into a Stored Proc & create a Table in SSIS with the dates as strings & then change the format using the Data Conversion Transformation.

OK. Are you outputting to a file which will subsequently be loaded into the CRM system? If not, how are you doing the transfer?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

scott.fountain Old Hand Points: 308 More actions · Answer 8

Hi there! If the transformations have to take place in the SSIS data flow, then try the DATEPART SSIS expression (see links below).

The Microsoft document discusses the SSIS expression and the stack overflow article discusses a similar problem & possible solution.

If you are using a 3rd party component to push data to the CRM, be sure to test it out! I know those types of components can be quite picky!

Hope this helps!

https://docs.microsoft.com/en-us/sql/integration-services/expressions/datepart-ssis-expression?view=sql-server-ver15

https://stackoverflow.com/questions/33442516/convert-getdate-to-yyyymmddhhmmss-as-derived-column