0

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?

4

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.

| improve this answer | |
  • Excellent Explanation – amanullah Jun 14 at 7:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.