Formatting dates in PostgreSQL

Question

I have a field which its format is date with time as: YYYY-MM-DD HH-MM-SS for example: 2000-08-12 00:00:00 I want to get just the date part and change its format to DD/MMM/YYYY for example the expected result of the previous example will be: 12/Aug/2000

The field definition is: Ddate timestamp without time zone DEFAULT now()

I read the whole page of Date/Time Functions and Operators and other sources as well but I couldn't find any information that is helpful.

Is it stored as a date or as a varchar?

Mureinik
– Mureinik

2015-06-17 07:17:38 +00:00
Commented Jun 17, 2015 at 7:17 — Mureinik
– Mureinik, Commented Jun 17, 2015 at 7:17
it's timestamp see edit

John
– John

2015-06-17 07:20:28 +00:00
Commented Jun 17, 2015 at 7:20 — John
– John, Commented Jun 17, 2015 at 7:20
Try Data Type Formatting Functions instead.

Álvaro González
– Álvaro González

2015-06-17 07:21:34 +00:00
Commented Jun 17, 2015 at 7:21 — Álvaro González
– Álvaro González, Commented Jun 17, 2015 at 7:21

Mureinik · Accepted Answer · 2015-06-17 07:23:28Z

10

You can use the to_char function to format your column:

SELECT TO_CHAR(ddatte, 'dd/Mon/yyyy') FROM mytable

answered Jun 17, 2015 at 7:23

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jurhas · Accepted Answer · 2015-06-17 07:18:39Z

4

try with:

to_char(your_Field, 'dd/mm/yyyy')

answered Jun 17, 2015 at 7:18

jurhas

6631 gold badge4 silver badges12 bronze badges

2 Comments

John Over a year ago

Yes that is what I did initially but it's not in the desired format. please note the month issue.

jurhas Over a year ago

Minutes are called "MI" in the template patthers, not "mm" look at this site: postgresql.org/docs/8.1/static/functions-formatting.html

Collectives™ on Stack Overflow

Formatting dates in PostgreSQL

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related