SQL Server SQL to PostgreSQL SQL

Question

I need to convert the following SQL server code to PostgreSQL code. Any help would be appreciated.

SQL Server SQL:

CAST(DATEADD(ww,DATEDIFF(ww,0,trans_date),-1)as date) as week

Including your PostgreSQL attempt will greatly help those you are asking for help from help you; we can't correct your attempt if we don't know what it was. — Thom A
– Thom A ♦, Commented Aug 12, 2019 at 17:47

user330315 · Accepted Answer · 2019-08-12 19:24:42Z

1

I think what that code does is to "round" the value of trans_date to the beginning of the week. In Postgres you can do that using the date_trunc() function:

date_trunc('week', trans_date)

Note that this always returns a timestamp, if you need a real date value, cast the result:

date_trunc('week', trans_date)::date

If it should be the day before the beginning of the week, just subtract one day from the result:

 date_trunc('week', trans_date)::date  - 1

answered Aug 12, 2019 at 19:24

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Mohammad Jahanzeb Over a year ago

Thank you so much for your answer! This worked perfectly! I actually needed that last one so just wanted to add "INTERVAL" which was missing from the code. date_trunc('week', trans_date)::date - INTERVAL '1 day'

Collectives™ on Stack Overflow

SQL Server SQL to PostgreSQL SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related