CODEWITHSUNDEEP
postgresqlpostgresql-9.3
MAK
MAK

Reputation: 7260

PostgreSQL 9.3 : Convert `DD-MM-YYYY` into `YYYY-MM-DD`

I have the following date which is of varchar type to convert into format YYYY-MM-DD in my function.

I am passing v_Date as a parameter to the function and within function want to perfrom the conversion.

Script:

DO
$$
DECLARE v_Date varchar := '16-01-2010 00:00:00';
    v_SQL varchar;
BEGIN
    v_SQL := 'SELECT to_date('''|| v_Date ||''',''YYYY-MM-DD'')';

    RAISE INFO '%',v_SQL;

    EXECUTE v_SQL;
END;
$$

The above script prepare the following script:

INFO:  SELECT to_date('16-01-2010 00:00:00','YYYY-MM-DD')

Which gives me the result:

0021-07-02

But the expected result should be:

2010-01-16

Upvotes: 3

Views: 16864

Answers (1)

user330315
user330315

Reputation:

SELECT to_date('16-01-2010 00:00:00','YYYY-MM-DD')

Your pattern yyyy-mm-dd does not match the input dd-mm-yyyy hh:mi:ss. You need to use dd-mm-yyyy to convert that input to a proper date (you can leave out the additional time part)

SELECT to_date('16-01-2010 00:00:00','dd-mm-yyyy');

The formatted output of a date value is up to your SQL client and (or) the current settings for LC_TIME. To make sure you get the yyyy-mm-dd output you have to format the resulting date using to_char()

SELECT to_char(to_date('16-01-2010 00:00:00','dd-mm-yyyy'), 'yyyy-mm-dd');

Upvotes: 8

Related Questions