0

In Snowflake I have the date format in following

Thu Nov 12 00:00:00 UTC 2020

If I want to make it as follows:

MM-DD-YYYY HH24:MI:SS

Can this be achieved. I have tried to_date function doesn't seems to work

https://docs.snowflake.com/en/sql-reference/functions/to_date.html

any suggestions

Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
Arun Sunderraj
  • 37
  • 4

1 Answers1

0

I assume that this data is held in a column with a TIMESTAMP-type datatype? If that is the case then it is stored as a number and how it is displayed is controlled by *_OUTPUT_FORMAT parameters.

If you want to display a date/time in anything other than the default format then just use TO_CHAR with the appropriate format e.g.

select col3
, to_char(col3, 'MM-DD-YYYY HH24:MI:SS')
from casttb;

gives:

COL3                        TO_CHAR(COL3, 'MM-DD-YYYY HH24:MI:SS')
2017-01-23 20:34:52.000     01-23-2017 20:34:52
2016-10-05 00:15:53.000     10-05-2016 00:15:53
2017-01-23 08:26:27.000     01-23-2017 08:26:27
2016-10-05 10:16:47.000     10-05-2016 10:16:47

If your date is held as text then first you would need to cast it to a date/time (TO_DATE) and then to text with the required format

NickW
  • 8,430
  • 2
  • 6
  • 19