Get months in Snowflake without having the timezone

user_17229509 0
Viewed 280

I have a table with a column that contains a timestamp with the format YYYY-MM-DD HH:MM:SS .

I need to get only the month. I am well aware that MONTHNAME exists, but it seems to me that it only works if the timestamp includes the timezone.

In the current situation, I'm trying to obtain it using the following:

SELECT
 nameofthecolum, date_part (month, nameofthecolum),
  
FROM
 tablename

And I get the error:

ERROR: column "month" does not exist Position: 32

snowflake-cloud-data-platform sql
2 Answers

you can extract month number like that

SELECT
 EXTRACT(MONTH FROM nameofthecolum)
  
FROM
 tablename

to get month name in snowflake

SELECT to_varchar(nameofthecolum, 'MMMM')
FROM
 tablename;

for PostgreSQL or Oracle

SELECT to_char(nameofthecolum, 'month')
FROM
 tablename;

for MySQL users

SELECT DATE_FORMAT(nameofthecolum, '%M')
FROM
 tablename;
user_171283500

You can use the MONTHNAME() function with any temporal data type (TIMESTAMP_NTZ, TIMESTAMP_LTZ, TIMESTAMP_TZ, and DATE) and any timestamp format compliant to your TIMESTAMP_INPUT_FORMAT parameter. There's no restriction about the existence of a time zone.

Example 1: MONTHNAME()

create or replace table t1 (ts timestamp_ntz) as
select '2021-01-01 00:00:00';

select monthname(ts) from t1;
-- Jan

select monthname(ts::timestamp_ltz) from t1;
-- Jan

select monthname(ts::timestamp_tz) from t1;
-- Jan

select monthname(ts::date) from t1;
-- Jan

If you want to extract the month number instead of the month name, you can use DATE_PART() as you already tried.

Example 2: DATE_PART()

create or replace table t1 (ts timestamp_ntz) as
select '2021-01-01 00:00:00';

select date_part(month, ts) from t1;
-- 1

select date_part(month, ts::timestamp_ltz) from t1;
-- 1

select date_part(month, ts::timestamp_tz) from t1;
-- 1

select date_part(month, ts::date) from t1;
-- 1

Also, regarding the error you encountered:

ERROR: column "month" does not exist Position: 32

This error message format is different from Snowflake's error format. This is more like PostgreSQL.

If you are using any application or intelligent client that supports multiple database products and can switch the connection target easily, I'd recommend you to check whether the application is actually connected to Snowflake or not.

user_170209110
Related