skip to Main Content

I’m having problems with the case statement in PostgreSQL. Here I want to change the output of the month (number) to text, like January, February, etc.

select
    date_part('month', tanggal) bulan,
    case 
         when date_part('month', tanggal)= 1 then 'January'
         when date_part('month', tanggal) = 2 then 'February'
         when date_part('month', tanggal) = 3 then 'March'
         when date_part('month', tanggal) = 4 then 'April'
         when date_part('month', tanggal) = 5 then 'May'
         when date_part('month', tanggal) = 6 then 'June'
         when date_part('month', tanggal) = 7 then 'July'
         when date_part('month', tanggal) = 8 then 'August'
         when date_part('month', tanggal) = 9 then 'September'
         when date_part('month', tanggal) = 10 then 'October'
         when date_part('month', tanggal) = 11 then 'November'
         when date_part('month', tanggal) = 12 then 'December'
         else date_part('month', tanggal)
    end as namabulan

But, what I got was an error like this

ERROR:  invalid input syntax for type double precision: "Januari"
LINE 4:    when date_part('month', tanggal)= 1 then 'Januari'
                                                    ^ 

I was trying with a changed data type in the case statement ‘January’, but I got an error with the statement not matching with double precision.

2

Answers


  1. The literals all have type ‘unknown’, leaving the ELSE to determine the return type of the CASE. But then the literals can’t be interpreted as being of that type.

    Login or Signup to reply.
  2. This will return the name of the month without the CASE statement:

    SELECT
         current_timestamp, to_char(current_timestamp,'Month') MonthName;
    

    see: DBFIDDLE, which also shows how to fix the problem in your CASE statement.

    and documentation of postgresql: full mixed-case month name (blank-padded to 9 chars)

    NOTE: When you do not want 9 characters, you can always use rtrim()

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search