0

I want to compare two columns Assigned_Date and Expected_Delivery_Day in my table and check if day has passed.

This is my table:

enter image description here

I tried to get the dayofweek for each column and compare the weight with IF, but im not sure how.

This is what i tried:

SELECT dayofweek(`Assigned_Date`) FROM `drvapp_routes`;

SELECT CASE 
WHEN `Expected_Delivery_Day` = 'Sunday' THEN 0
WHEN `Expected_Delivery_Day` = 'Monday' THEN 1
WHEN `Expected_Delivery_Day` = 'Tuesday' THEN 2
WHEN `Expected_Delivery_Day` = 'Wednesday' THEN 3
WHEN `Expected_Delivery_Day` = 'Thursday' THEN 4
WHEN `Expected_Delivery_Day` = 'Friday' THEN 5
WHEN `Expected_Delivery_Day` = 'Saturday' THEN 6
ELSE NULL END AS EDD
from `drvapp_routes`;

Is this the right way? How do i compare two day names and see if Assigned_Date has passed the Expected_Delivery_Day ?

Webdeveloper_Jelle
  • 2,868
  • 4
  • 29
  • 55
Thowzif
  • 21
  • 5
  • What format are both fields? if both are dates then normal comparison operators will work. – psykx Oct 05 '22 at 14:27
  • If Expected_delivery_day is just a Day Name Then how are you expecting this date comparison to work, please be a little more specific – RiggsFolly Oct 05 '22 at 14:46
  • Do you want the comparision inside the query result or do you handle that inside your code? – Webdeveloper_Jelle Oct 05 '22 at 14:47
  • I feel like this logic is going to complicated. You care about the next occuring day of the week from the `assigned_date` I assume. So you may need to do some date math. Otherwise if you ship on a Thursday and expect delivery on Monday then 4 > 1, but if you ship on Monday and expect delivery on Thursday then 4 < 1.. like.. how would you rectify that the weekday has passed? Other than that, your first step with this logic is sound (converting string weekdays to number). – JNevill Oct 05 '22 at 14:52
  • Try [dbfiddle](https://dbfiddle.uk/eFFnU3uM). – wchiquito Oct 05 '22 at 20:48
  • @wchiquito thank you very much, it is perfect and what i needed exactly! – Thowzif Oct 06 '22 at 15:18
  • @JNevill i completely agree. We are changing it to a timestamp. – Thowzif Oct 06 '22 at 15:19
  • @Jbadminton one query to rule them all :) – Thowzif Oct 06 '22 at 15:20
  • @RiggsFolly correct, it was bad coding. But it is what it is and i tried with the method above. – Thowzif Oct 06 '22 at 15:21
  • @psykx Assigned_Date is string and the other is date. – Thowzif Oct 06 '22 at 15:22
  • @wchiquito please do not leave this page in an abandoned state by resolving the question via comment. Please transfer your advice to an answer and explain it so that Thowzif can accept the answer and officially mark this page as resolved. – mickmackusa Nov 19 '22 at 03:28

0 Answers0