In PostgreSQL, the make_date() function allows you to create a date from its separate year, month and day fields.

Each date part is provided as an integer, and the result is returned as a date.

Syntax

The function has the following syntax:

make_date(year int, month int, day int)

Where year, month, and day are integers representing the year, month, and day of the date.

Example

Here’s a basic example to demonstrate.

SELECT make_date(2020, 07, 25);

Result:

2020-07-25

And we can verify that it returns a data type of date with the following query.

SELECT pg_typeof(make_date(2020, 07, 25));

Result:

date

The pg_typeof() function returns the data type of its argument, and so I passed make_date() as the argument..

Out of Range

If any of the arguments are out of the range of the possible values for its date part, you’ll get an “out of range” error.

SELECT make_date(2020, 17, 25);

Result:

ERROR: date field value out of range: 2020-17-25

Here it is again with a month of 00:

SELECT make_date(2020, 00, 25);

Result:

date field value out of range: 2020-00-25

Passing Strings as Arguments

The Postgres documentation states that the arguments must be integers, but passing strings seems to work (probably because they’re implicitly converted to integers), as long as each argument is within its proper range.

SELECT make_date('2020', '07', '25');

Result:

2020-07-25

Again we can use pg_type() to check that the result is in fact a date data type.

SELECT pg_typeof(make_date('2020', '07', '25'));

Result:

date

However, you still need to make sure each argument would be valid once converted to an integer, otherwise you’ll get an error such as the following.

SELECT make_date(2020, 'July', 25);

Result:

ERROR: invalid input syntax for type integer: "July"
LINE 1: SELECT make_date(2020, 'July', 25);