0

There is a cast function that is being used in a Teradata procedure as

cast(<col_name> AS DATE FORMAT 'MM/DD/YY'

It is giving me output as DATE datatype of format 'MM/DD/YY'. I am trying to use similar function in SNOWFLAKE so that it would give me same output. I tried something like this

TO_CHAR(TO_DATE(<col_name>,'MM/DD/YY')

It is giving me output in that format as well, but whenever I am running the Snowflake procedure, different result is getting inserted in the table. This is because my code is giving me result in varchar datatype while the column is of Date datatype. Any idea on how to do this conversion would be highly appreciated.

ADITYA NARAYAN
  • 17
  • 4

2 Answers2

0

Snowflake has TRY_DO_DATE, that supports format:

SELECT col_name, TRY_TO_DATE(col_name, 'MM/DD/YY') AS col_name_casted
FROM tab
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • Hi, I did that also, now it is giving ke output like this 0022-06-28. It should be as 06/28/22. And it should be in date data type . Is it possible in SNOWFLAKE? – ADITYA NARAYAN Aug 02 '22 at 03:07
  • @ADITYANARAYAN 1. Date data type is date data type. 2.representation of datevisualzation) is different case and it depends on tool you are using. If you are using SnowsightUI then setting DATE_OUTPUT_FORMAT (account/session) parameter could be a solution, though each user could override it with its own preference. – Lukasz Szozda Aug 02 '22 at 07:02
0

It appears your data is already stored as a date column. There is a session parameter you can set for input and output date formats so if you have a standard representation of dates you can use that natively in your processing:

alter session set DATE_INPUT_FORMAT='MM/DD/YY';
alter session set DATE_OUTPUT_FORMAT='MM/DD/YY';

select <col_name> from table; -- Now will show as MM/DD/YY for date columns

If you don't wish to do that, then to format it you need to convert to char, format, and then cast to date:

select to_char(col_name,'MM/DD/YY'); -- converts date to char format
select to_date(to_char(col_name,'MM/DD/YY'),'MM/DD/YY'); -- effectively is a no-op
Jim Demitriou
  • 593
  • 4
  • 8
  • I'll add that Lukasz's recommendation to use TRY_TO_DATE() is a good one if you're using questionable dates ... if will return NULL if the casting to a date fails because of either an unknown format or illegal value for a date. – Jim Demitriou Aug 01 '22 at 18:51
  • can you please explain the alter session part and how I can use it in my procedure which is written in python? – ADITYA NARAYAN Aug 02 '22 at 03:20
  • It appears that the session parameter is not recognized when using Python. https://github.com/snowflakedb/snowflake-connector-python/issues/1069#issuecomment-1142426656 – Jim Demitriou Aug 02 '22 at 15:11
  • Bottom line, if you want to *display* date in a certain format, you need to apply date formatting using the to_char(,) function. You can use the value for date processing (including insert / update / delete of date type columns in queries, or in functions that use date data types. The internal representation of date in Snowflake is independent of how it is displayed. – Jim Demitriou Aug 02 '22 at 15:15