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

  • Oscar_Boots

    Old Hand

    Points: 313

    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

     

     

     

     

     

  • frederico_fonseca

    SSChampion

    Points: 14685

    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: 313

    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

    SSChampion

    Points: 14685

    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: 313

    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: 244578

    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?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Oscar_Boots

    Old Hand

    Points: 313

    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: 244578

    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?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • scott.fountain

    SSC Veteran

    Points: 248

    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

     

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

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