PostgreSQL date format

How to format date in PostgreSQL. Today you will learn different formatting of date in PostgreSQL. Here we use builtin PostgreSQL function to achieve our goal related to dates.

1- Function

select to_char(current_timestamp, text)
Here type of current_timestamp has date type, if you want to change type of date to text than use above function.

Different Usage

1-Add dash, slash etc between dates

• select to_char(current_timestamp, 'dd mm yyyy')   Output "10 11 2017"
• select to_char(current_timestamp, 'dd-mm-yyyy')   Output "10-11-2017"
• select to_char(current_timestamp, 'dd/mm/yyyy')   Output "10/11/2017"

2- Get only day date from given date "10-11-2017"

• select to_char(current_timestamp, 'dd')           Output "10"

3- Get only month from given date "10-11-2017"

• select to_char(current_timestamp, 'mm')           Output "11"

4- Get only year from given date "10-11-2017"

• select to_char(current_timestamp, 'yyyy')           Output "2017"

Note: You can also use capital "DD","MM" and "YYYY" to get date, month and year separately.

5- Getting last, second last and third last digit of give year "2017"

• select to_char(current_timestamp, 'Y')                Output "7"
• select to_char(current_timestamp, 'YY')                Output "17"
• select to_char(current_timestamp, 'YYY')            Output "017"

6- Getting lower, UPPER and Capitalized case month of a given date "10 11 2017"

• select to_char(current_timestamp, 'month')            Output "november"
• select to_char(current_timestamp, 'Month')            Output "November"
• select to_char(current_timestamp, 'MONTH')            Output "NOVEMBER"

7- Similarly you can get abbreviated lower, UPPER and  Capitalized case month (3 characters in English) of a given date "10 11 2017"

• select to_char(current_timestamp, 'mon')            Output "nov"
• select to_char(current_timestamp, 'Mon')            Output "Nov"
• select to_char(current_timestamp, 'MON')            Output "NOV"

8- Getting lower, UPPER and Capitalized case day name of a given date "10 11 2017"

• select to_char(current_timestamp, 'day')            Output "friday"
• select to_char(current_timestamp, 'Day')            Output "Friday"
• select to_char(current_timestamp, 'DAY')            Output "FRIDAY"

9- Similarly you can get abbreviated lower, UPPER and  Capitalized case day name (3 characters in English) of a given date "10 11 2017"

• select to_char(current_timestamp, 'dy')            Output "fri"
• select to_char(current_timestamp, 'Dy')            Output "Fri"
• select to_char(current_timestamp, 'DY')            Output "FRI"

10- Append 1st, 2nd, 3rd so on in date. (e.g. 01st, 02nd, 03rd)

In some scenario we want to show date format like 1st November 2017, 2nd November 2017 etc. For that purpose we use to_char function and in second parameter we use ddth for lower cace and ddTH for upper cace.

• select to_char(current_timestamp, 'ddth')               Output "09th"
• select to_char(current_timestamp, 'ddTH')               Output "09TH"
• select to_char(current_timestamp, 'ddth MonthYYYY')       Output "09th November 2017"

Now If you want to display date format like this "9th November 2017" instead of "09th November 2017". Notice here we are going to remove "zero" 0 from left side of give date. To do so we use Ltrim() function of postgreSQL.

• select Ltrim(to_char(current_timestamp, 'ddth MonthYYYY'),'0')    Output "9th November 2017"