Snowflake date function to use in ADF

Vrushabh Malbari 0 Reputation points
2024-04-22T10:46:03.6233333+00:00

Hi All,

We are trying to use below snowflake queries but facing issues.

Source table field datatype is varchar and target table date datatype.

DD/MM/YYYY (Source format) to MM/DD/YYYY (Target format we want)

Can anyone tell me which function I should use to get the date in the proper format?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,599 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Amira Bedhiafi 15,676 Reputation points
    2024-04-22T14:26:16.6333333+00:00

    You can use :

    • TO_DATE() - Converts a varchar to a date.
    • TO_CHAR() - Converts a date back to varchar in a specific format, if needed.

    Use the TO_DATE() function with the specific format of the input string. Since your source format is DD/MM/YYYY, you'll convert it like this:

    
TO_DATE(source_column, 'DD/MM/YYYY')

    This converts the varchar source_column into a date type.

    If you need the output as a varchar in the MM/DD/YYYY format, you can reformat it using TO_CHAR():

    
TO_CHAR(TO_DATE(source_column, 'DD/MM/YYYY'), 'MM/DD/YYYY')

    This will return the date as a varchar in the desired format.

    If your target table has a date field, you don't need to convert the date back to varchar. Simply insert the result of the TO_DATE() function:

    
INSERT INTO target_table (date_column)

SELECT TO_DATE(source_column, 'DD/MM/YYYY')

FROM source_table;
    • You can use these SQL functions within a Copy Activity in your pipeline if you are transforming data inline during the copy process from one table to another.
    • Alternatively, you can use a Data Flow Activity where you can visually transform data. Use the Derived Column transformation to apply these functions to the date fields.

    You can also set up a derived column transformation:

    • Derived Column Name: formatted_date
    • Expression: to_date(source_column, 'DD/MM/YYYY')
  2. Harishga 3,505 Reputation points Microsoft Vendor
    2024-04-23T10:59:56.63+00:00

    Hi @Vrushabh Malbari
    Welcome to Microsoft Q&A platform and thanks for posting your question here.

    To convert the date format from DD/MM/YYYY to MM/DD/YYYY, you can use the TO_DATE and DATE_FORMAT functions in Snowflake. Here's an example query that should work for you:

     SELECT DATE_FORMAT(TO_DATE(source_column, 'DD/MM/YYYY'), 'MM/DD/YYYY') as target_column FROM source_table; 
 

    This query will first convert the source_column from varchar to date format using the TO_DATE function, and then use the DATE_FORMAT function to convert it to the desired format of MM/DD/YYYY. Replace "source_column" and "source_table" with the appropriate column and table names in your case.

    You can find more information about the TO_DATE and DATE_FORMAT functions in the Snowflake documentation:
    https://docs.snowflake.com/en/sql-reference/functions/to_date
    https://docs.snowflake.com/en/sql-reference/functions/date_part

    I hope this information helps you. Let me know if you have any further questions or concerns.

    0 comments