CODEWITHSUNDEEP
postgresql
Tom
Tom

Reputation: 6342

What's the difference for the three ways to convert string to date

I have three ways to convert a string to date:

1. select date '2021-08-24'
2. select '2021-08-24'::date
3. select to_date('2021-08-24')

I would ask what's the difference between them and which one is preferred, thanks!

Upvotes: 0

Views: 152

Answers (1)

user330315
user330315

Reputation:

The first is a ANSI SQL compliant date literal which should work on any database that supports the ANSI SQL standard (for date or timestamp literals). I prefer this format because it is portable - at least across the DBMS products I use.

The second is Postgres specific way to cast a string value to a date.

The third is invalid in Postgres. Although Postgres supports a function named to_date() it requires at least two arguments: the string to be converted and a format mask that specifies how the input string is structured. to_date('2021-08-24', 'yyyy-mm-dd') would be valid. It is a non-standard function though (but e.g. Oracle also supports it)

Upvotes: 1

Related Questions