none
Converting a string Italian date into a datetime column - SSIS 2014-2016 RRS feed

  • Question

  • Hi,

    I'm reading a string Italian date from a flat file. I can read "Maggio 2019", "Giugno 2019" and so on and I need to convert these string values in 01/05/2019, 01/06/2019, etc., possibly without transforming the string month in a number.

    Any suggests to me, please? Thanks

    Wednesday, November 13, 2019 4:51 PM

All replies

  • Hi pscorca,

    You can use a Derived Column Transformation. The expression could be along the following:

    TOKEN([columnName], " ", 1) == "Gennaio" ? TOKEN([columnName], " ", 2) + "-01-01" : ?
    ..
    TOKEN([columnName], " ", 1) == "Maggio" ? TOKEN([columnName], " ", 2) + "-05-01" : ?
    TOKEN([columnName], " ", 1) == "Giugno" ? TOKEN([columnName], " ", 2) + "-06-01" : ?
    ..
    TOKEN([columnName], " ", 1) == "Dicembre" ? TOKEN([columnName], " ", 2) + "-12-01" : "1900-01-01"



    Wednesday, November 13, 2019 5:36 PM
  • Hi, thanks for your reply.

    I hoped in a more easy solution. Thanks

    Wednesday, November 13, 2019 9:20 PM
  • Hi pscorca,

    What's the latest?

    Is the proposed solution working for you?

    Thursday, November 14, 2019 12:00 AM
  • Hi, thanks for your reply.

    I hoped in a more easy solution. Thanks

    Hi pscora,

    Since you need a more clear solution, please develop your habit of closing your threads by marking useful replies as answers, I haven't found that you have closed you threads here. Are you friendly to us?

    https://social.technet.microsoft.com/Forums/en-US/user/threads?user=pscorca&filter=alltypes&sort=firstpostdesc&searchTerm=undefined

    For such question, you need to know the function "TOKEN", how to use SSIS conditional operator to handle different conditions. Here are some related articles for your reference.

    TOKEN (SSIS Expression)

    ? : (Conditional) (SSIS Expression)

    Nested Conditional Operators in an SSIS Derived Column

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 14, 2019 2:52 AM
  • Hi Will, thanks for your reply.

    I hoped in a more easy solution such as using a cast operation without using a conditional operator.

    Working with an English SQL Server instance, if I write SELECT cast('May 2019' as date) I can obtain the right date.

    Working with an Italian SQL Server instance, if I write SELECT cast('Maggio 2019') as date) I can obtain the right date.

    Now, I'm working with an English instance and reading a string Italian date with values as "Gennaio 2019", "Febbraio 2019", "Marzo 2019" and so on. In this case the SELECT cast(...) doesn't function. In order to avoid to write a nested conditional operator with 12 levels inside SSIS, first I've written the string date into a SQL table and then I've update another colums by using a CASE … WHEN … END statement. However, using a CASE … WHEN … END statement isn't a more easy solution respect to a cast operation. 

    Friday, November 15, 2019 10:55 AM
  • Hi pscora,

    My SQL Server is US English.

    The following T-SQL worked for me in SSMS:

    SET LANGUAGE Italian;
    SELECT CAST('Gennaio 2019' AS DATE) AS [January]
    	, CAST('Febbraio 2019' AS DATE) AS [February]
    	, CAST('Marzo 2019' AS DATE) AS [March];

    Output:

    January	        February	        March
    2019-01-01	2019-02-01	2019-03-01

    Another version:

    SET LANGUAGE Italian;

    SELECT *, CAST(ItalianMonth AS DATE) AS [RegularDate] FROM (VALUES (1, 'Gennaio 2019'), (2, 'Febbraio 2019'), (3, 'Marzo 2019') ) AS tbl(ID, ItalianMonth);


    Output:
    ID	ItalianMonth	RegularDate
    1	Gennaio 2019	2019-01-01
    2	Febbraio 2019	2019-02-01
    3	Marzo 2019	2019-03-01


    Friday, November 15, 2019 3:08 PM