Snowflake date function to use in ADF

Question

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?

Answer 1

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')

Answer 2

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.

Answer 3

Hello @ADF_Coder

To convert a date from the format DD/MM/YYYY to MM/DD/YYYY in Snowflake, you can use the TO_DATE function.

Here's an example query:

SELECT TO_DATE('31/12/2021', 'DD/MM/YYYY') AS converted_date;

This will return the date in the format MM/DD/YYYY as follows:

CONVERTED_DATE 12/31/2021

You can replace the string 31/12/2021 with the name of your source table field to convert the date values in that column.

And you can then use this converted date value to insert into your target table with the date datatype.

---

I hope that this response has addressed your query and helped you overcome your challenges. If so, please mark this response as Answered. This will not only acknowledge our efforts, but also assist other community members who may be looking for similar solutions.