ERROR: date/time field value out of range: "2020-06-0700:00:00"

Question

I am trying to run the Select query(linked server) from SSMS to get the data from PostgreSQL on AWS cloud. My query is running fine in SSMS but as soon as I enter the following line of code

and c.created_date >=concat(to_char(CURRENT_DATE - interval '7 day', 'yyyy-mm-dd'),' 00:00:00'):: timestamp

and c.created_date <= concat(to_char(CURRENT_DATE - interval '1 day','yyyy-mm-dd') ,' 23:59:59') ::timestamp

it starts giving me ERROR: date/time field value out of range: "2020-06-0700:00:00";

created_date field in my PostgreSQL is timestamp without timezone

Which datatype should I chose which is compatible with SQL Server?

Answer 1

You are overcomplicating things. concat() and to_char() are not needed and the root cause of the problem.

The start of the day 7 days back can be obtained using: current_date - 7. As that doesn't have a time part, a comparison with a timestamp column will automatically assume 00:00:00.

If you want to compare everything up to yesterday midnight, simply use < current_date.

So your expression can be simplified to:

and created_date >= current_date - 7
and created_date < current_date

Note the < instead of <= in the second condition.

If you want to be explicit about the time, you could use

and created_date >= (current_date - 7) + time '00:00:00'
and created_date < current_date + time '00:00:00' 

But that's not needed and is absolutely identical to the first condition.

---

created_date < current_date is roughly equivalent to created_date <= (current_date - 1) + time '23:59:59' (which is your initial intention).

However, it's only roughly equivalent because your attempt isn't actually correct as it misses the time between 23:59:59.000000 and 23:59:59.999999, that's why it's better to use < with the next day's start.