1

I am looking to insert any timestamp and to return a column that returns a date of Sunday ( start of the week). I reviewed the answer in the thread Start the first day of the week as Sunday in SQL but keep getting errors.

My SQL is below. "received_day" is my unique timestamp that I would look to convert to Sunday. Any guidance appreciated.

SELECT received_day
, distributor_id
, warehouse_id
, sum(quantity_unpacked) as qty
, Case 
When distributor_id IN ('FRTMU','I20TQ','P294D','AA05E','WB3JB') THEN 'CHICAGO' 
ELSE 'WRONG' END AS GEO

FROM TBL NAME
WHERE received_day between TO_DATE('20201227', 'YYYYMMDD') and TO_DATE ('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD')
GROUP BY received_day, distributor_id, warehouse_id, supplier_order_type_id
Limit 100

I have also a DateFirst approach with no luck:

SET DATEFIRST 7,

SELECT cast (received_day AS datetime2) AS SelectDate
,DATEPART (dw,received_day) As DayofWeek
,received_day
, distributor_id
, warehouse_id
jarlh
  • 42,561
  • 8
  • 45
  • 63
Alec Newbie
  • 11
  • 3
  • 4
    please tag your DBMS first of all – Barbaros Özhan Apr 21 '21 at 18:51
  • Please edit your question to include the error message. – Sam M Apr 21 '21 at 19:09
  • 1
    Please hover your mouse over the [tag:sql] you used, and read the description of that tag. It asks you to also include a tag for the specific DBMS you're using, because functionality and syntax differs between them. Please [edit] your post to include that tag. While you're making that edit, you can also include the complete, exact error message that you're getting. It's on the screen right in front of you, but we can't see that screen from here. – Ken White Apr 21 '21 at 19:15
  • That linked question was about sql server. Is that what you are using? – Hans Kesting Apr 21 '21 at 19:41
  • 1
    Hello. I was unaware how to identify the specific DBMS my company uses. I will need to go back and learn that for future posts, so the tag is accurate. Sry. – Alec Newbie Apr 21 '21 at 19:42
  • The error that was received is : Invalid operation: type "datetime2" does not exist. I will need to educate myself on the datefirst function (have never used before just found online) more before I post again, so to not waste anyone's time. Thx – Alec Newbie Apr 21 '21 at 19:45
  • Yes am using a SQL server, the link I provided in the question was from another thread I was reading through on this forum. Though that approach didnt work. I will gather more data on my DBMS and circle back. Thanks – Alec Newbie Apr 21 '21 at 21:33
  • The DBMS I was using was : PostgreSQL. And what ended up working is "date_trunc('week',received_day+1)-1" – Alec Newbie Apr 29 '21 at 22:17

0 Answers0