change the data format from ddmmyyyy to dd-mm-yyyy in snowflake

Question

I am working on snowflake query where I am having a column(DATE) having data in form of ddmmyyyy(20211117 00:00:00). I need to convert this data into dd-mm-yyyy(2021-11-17 00:00:00.000 ) format. I used the TO_DATE(DATE,'YYYY-MM-DD') expression but it doesn't work for me. Can somebody help me on this issue

Answer 1

TO_DATE will only return date portion, as below -

select to_date('20211117 00:00:00','yyyymmdd hh24:mi:ss') as date_col;
+------------+
| DATE_COL   |
|------------|
| 2021-11-17 |
+------------+

Use TO_TIMESTAMP to get time portion as well -

select to_timestamp('20211117 00:00:00','yyyymmdd hh24:mi:ss') as datetime_col;
+-------------------------+
| DATETIME_COL            |
|-------------------------|
| 2021-11-17 00:00:00.000 |
+-------------------------+

Refer Date examples

Refer Time Examples