How to create query with format date DD-MM-YYYY

Question

I have query database like this:

SELECT * FROM TABLE WHERE start_date = '01-10-2016' //DD-MM-YYYY

But above code error and actually in database date format is YYYY-MM-DD.
So how to create query with format date DD-MM-YYYY?

Answer 1

May be you can use to_date function to convert the above format into the standard format.

For instance,

SELECT to_date('01-10-2016', 'DD-MM-YYYY');
----------
2016-10-01
1 row

https://www.techonthenet.com/postgresql/functions/to_date.php

Answer 2

A proper date column (data type date !) has no format. Then it's enough to get your data input for a date column right, use to_date() for non-standard input format like @Shiva posted. Or better yet, always provide date literals in ISO 8601 format 'YYYY-MM-DD' to begin with, which works with any locale setting.

If you are running a broken design with dates stored as text, then combine to_date() and to_char() to transform any valid date format into any other text format:

SELECT * FROM tbl
WHERE  start_date = to_char(to_date('01-10-2016', 'DD-MM-YYYY'), 'YYYY-MM-DD');