I have a PostgreSQL query:
SELECT "message" FROM "mytable"
WHERE "start_date" <= current_date AND "end_date" >= current_date
The current_date is the date of the system, for example today current system is: 29-08-2019
the type of all the fields are String.
the format of the current_date is "YYYY-DD-MM", and the format of the start_date and end_date are "DD-MM-YYYY"
How can I change the format of current_date to "DD-MM-YYYY" using postgresql or javascript ?
thank you
Use to_char() function for formatting date strings
SELECT to_char(current_date, 'DD-MM-YYYY')
In your case, using it for comparison, it would be much better to format the strings into dates, so there should be a type date comparison instead of a type text comparison (using to_date() function).
One reason: The string comparison with dates formatted like that yours, would yield in errors:
01-02-2019 < 31-01-2019
because the text 31 is greater than the text 01.
WHERE current_date BETWEEN to_date(start_date, 'DD-MM-YYYY') and to_date(end_date, 'DD-MM-YYYY')
You can use any of the methods listed under "Data Type Formatting Functions" section of the docs. For comparisons, either use to_date() or to_timestamp() functions.
There isn't a direct method on the Date class but you use a snippet for this.
const datetime = new Date()
const date = datetime.toISOString().split('T')[0]
const formattedDate = date.split('-').reverse().join('-')
[parts[1], parts[2], parts[0]].join('-').