Is there a function in Vertica is equivalent to DATENAME in SQL

Xiukun_Chang

In MS SQL, I can get month name using SELECT DATENAME(month,'2007-10-30 12:15:32.1234567 +05:10'); to return October. Do we have an equivalent function in Vertica? Thanks. Sean

colin_loghin

I would wrap a SQL function around a case statement supported by the date_part function : CREATE OR REPLACE FUNCTION datename1(part varchar, dt Date) RETURN varchar AS BEGIN RETURN CASE date_part(part ,dt) WHEN 1 then 'Jan' when 2 then 'Feb' else 'Dec' end ; END; select datename1('month','2012-02-22'); datename1 ----------- Feb (1 row)

Xiukun_Chang

That is what I did in a similar way, but just wondering if there is an existing Vertica function that I am not aware of. Thanks.

Kiryl_Mauryshch

There is no function that would extract Month as a word. Similar to DATE_PART, there is an EXTRACT function which would return the number. SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 2