Skip to main content
Database Administrators

Return to Question

added 12 characters in body
Source Link
user1822
user1822

How one can make POSTGRESPostgres treat a little stupid about the date which actually does not occur on the Gregorian calendar?

SELCET date, value  
FROM table  
WHERE date BETWEEN 2022-01-01 AND 2022-02-31

You know that the query works fine when use the first day of the next month:

SELCET date, value FROM table WHERE date BETWEEN 2022-01-01 AND 2022-03-01

My question is how we can handle such a situation on the database side instead of the App side. I mean we can prevent such values from getting into the database inside our App. However, it will be handier if we can fix them inside queries. For example, I seek a procedure that converts invalid dates to the maximum acceptable date: 2022-02-31 --> 2022-02-28

How one can make POSTGRES treat a little stupid about the date which actually does not occur on the Gregorian calendar?

SELCET date, value FROM table WHERE date BETWEEN 2022-01-01 AND 2022-02-31

You know that the query works fine when use the first day of the next month:

SELCET date, value FROM table WHERE date BETWEEN 2022-01-01 AND 2022-03-01

My question is how we can handle such a situation on the database side instead of the App side. I mean we can prevent such values from getting into the database inside our App. However, it will be handier if we can fix them inside queries. For example, I seek a procedure that converts invalid dates to the maximum acceptable date: 2022-02-31 --> 2022-02-28

How one can make Postgres treat a little stupid about the date which actually does not occur on the Gregorian calendar?

SELCET date, value  
FROM table  
WHERE date BETWEEN 2022-01-01 AND 2022-02-31

You know that the query works fine when use the first day of the next month:

SELCET date, value FROM table WHERE date BETWEEN 2022-01-01 AND 2022-03-01

My question is how we can handle such a situation on the database side instead of the App side. I mean we can prevent such values from getting into the database inside our App. However, it will be handier if we can fix them inside queries. For example, I seek a procedure that converts invalid dates to the maximum acceptable date: 2022-02-31 --> 2022-02-28

Source Link
sci9
sci9
  • 101
  • 1
  • 4

Postgres: How to automatically fix date/time field value out of range inside the query

How one can make POSTGRES treat a little stupid about the date which actually does not occur on the Gregorian calendar?

SELCET date, value FROM table WHERE date BETWEEN 2022-01-01 AND 2022-02-31

You know that the query works fine when use the first day of the next month:

SELCET date, value FROM table WHERE date BETWEEN 2022-01-01 AND 2022-03-01

My question is how we can handle such a situation on the database side instead of the App side. I mean we can prevent such values from getting into the database inside our App. However, it will be handier if we can fix them inside queries. For example, I seek a procedure that converts invalid dates to the maximum acceptable date: 2022-02-31 --> 2022-02-28