I'm trying to cast a CHARACTER VARYING column to a DATE but I need a date format like this : DD/MM/YYYY. I use the following SQL query :
ALTER TABLE test
ALTER COLUMN date TYPE DATE using to_date(date, 'DD/MM/YYYY');
The result is a date like this : YYYY-MM-DD.
How can I get the DD/MM/YYYYformat ?
Thanks a lot in advance !
Thomas
A DATE column does not have a format. You cannot specify a format for it.
You can use DateStyle to control how PostgreSQL emits dates, but it's global and a bit limited.
Instead, you should use to_char to format the date when you query it, or format it in the client application. Like:
SELECT to_char("date", 'DD/MM/YYYY') FROM mytable;
e.g.
regress=> SELECT to_char(DATE '2014-04-01', 'DD/MM/YYYY');
to_char
------------
01/04/2014
(1 row)
to_date does is make a date from a string. The date doesn't have a format internally, it's just a number.
select to_char(DATE my_column, 'DD/MM/YYYY') from my_table order by (DATE my_column) desc?
https://www.postgresql.org/docs/8.4/functions-formatting.html
SELECT to_char(date_field, 'DD/MM/YYYY')
FROM table
The documentation says
The output format of the date/time types can be set to one of the four styles ISO 8601, SQL (Ingres), traditional POSTGRES (Unix date format), or German. The default is the ISO format.
So this particular format can be controlled with postgres date time output, eg:
t=# select now();
now
-------------------------------
2017-11-29 09:15:25.348342+00
(1 row)
t=# set datestyle to DMY, SQL;
SET
t=# select now();
now
-------------------------------
29/11/2017 09:15:31.28477 UTC
(1 row)
t=# select now()::date;
now
------------
29/11/2017
(1 row)
Mind that as @Craig mentioned in his answer, changing datestyle will also (and in first turn) change the way postgres parses date.
select now()::date and it returns a date on one server and a timestamp on another. They're both running postgres 11.12. I suspect that someone may have messed with the date casting or type functions on the server that returns timestamp but i have no idea how or where. Any help investigating would be appreciated.alter system set edb_redwood_date=off; select pg_reload_conf();In case you need to convert the returned date of a select statement to a specific format you may use the following:
select to_char(DATE (*date_you_want_to_select*)::date, 'DD/MM/YYYY') as "Formated Date"
Depends on which type you require as output, but here are 2 quick examples based on an intervals:
SELECT (now() - interval '15 DAY')::date AS order_date -> 2021-07-29SELECT to_char(now() - interval '15 DAY', 'YYYY-MM-DD') -> 2021-07-29Let's say your date column is order_date:
SELECT (
RIGHT(order_date, 4)
|| '-'
|| SUBSTRING(order_date, 4, 2)
|| '-'
|| LEFT(order_date, 2)
)::DATE
FROM test
OR
SELECT CAST(
RIGHT(order_date, 4)
|| '-'
|| SUBSTRING(order_date, 4, 2)
|| '-'
|| LEFT(order_date, 2)
AS DATE )
FROM test
Be mindful about the case sensitiveness:
to_date(date, 'dd/MM/yyyy')
