ERROR: date/time field value out of range: "2020-06-0700:00:00" 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 &gt;=concat(to_char(CURRENT_DATE - interval '7 day', 'yyyy-mm-dd'),' 00:00:00'):: timestamp

and c.created_date &lt;= 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?
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 &lt; current_date. 

So your expression can be simplified to: 

and created_date &gt;= current_date - 7
and created_date &lt; current_date


Note the &lt; instead of &lt;= in the second condition.

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

and created_date &gt;= (current_date - 7) + time '00:00:00'
and created_date &lt; current_date + time '00:00:00' 


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



created_date &lt; current_date is roughly equivalent to created_date &lt;= (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 &lt; with the next day's start.