Search code examples
sqlvertica

Convert date (yyyy-mm-dd) to MM-YYYY in SQL

I would like to convert a date in yyyy-mm-dd (Date format, length 10) to mm-yy format.

I have so far tried "right", "Left" and "convert" functions to no avail.

What's the exact syntax I need to use?

SELECT
LEFT (day,7) as 'YYYYMM'
FROM bi_core.fact_campaign_device_stats_daily
WHERE DAY = '2019-07-01'

SQL Error [3457] [42883]: [Vertica][VJDBC](3457) ERROR: Function LEFT(date, int) does not exist, or permission is denied for LEFT(date, int)
Solution

  • In Vertical, use to_char():

    SELECT to_char(day, 'MMYY') as mmyy
FROM bi_core.fact_campaign_device_stats_daily
WHERE DAY = '2019-07-01'