CODEWITHSUNDEEP
sqlpostgresqldatecastingnetezza
Phill
Phill

Reputation: 545

PostgreSQL misinterpreting characters as numbers after casting

I have been following the example here to convert a date to month name. However, when I get a shifting window of two months, the second month's name gets fragmented in the interpretation of some letters. 

FISCAL_YEAR 
        || TO_CHAR(TO_TIMESTAMP(to_Char(month(DATE_VAR),'999'), 'MM'), 'Month'
        || TO_CHAR(TO_TIMESTAMP(to_Char(
        case 
            when 
                month(DATE_VAR)+1>12 then 1 
                else month(DATE_VAR)+1 end
            ,'999'), 'MM'), 'Month'))

The outcome would ideally be 2016DecemberJanuary, but it returns something like 2016December1721395anuar1 or 2016May17211181une.

It appears the issue is in the logic that accounts the shift from Dec to Jan. What causes it?

Upvotes: 0

Views: 38

Answers (1)

Abelisto
Abelisto

Reputation: 15614

select to_char(now(),'January'); returns something like 2457729anuar6 because

J - Julian Day (integer days since November 24, 4714 BC at midnight UTC) .
Y - last digit of year

Link.

You need to quote strings in the format string to prevent interpreting it as the placeholders: select to_char(now(),'YY, "January"');

Upvotes: 2

Related Questions