Search code examples
sqlvertica

Is there a way to set a date format to a character when month is numeric?

I have 2 columns, say Year:2010 and Month:3 and want to create a date from these to columns I found the following way:

SELECT TO_DATE(concat('2010',concat('3','01')), 'YYYYMMDD')

It does now work properly as it gives 2012-06-04, but when I type '03' it starts giving the correct answer. How should I change MM part in YYYYMMDDto make the code working correctly?

Solution

  • I might suggest using arithmetic to construct the value, then convert to a string and to a date:

    select to_date( (year * 10000 + month * 100 + 1)::varchar, 'YYYYMMDD')