DateDiff based on Weekday 1

[RJL1]

Hello,

I have a script that uses a DateDiff to calculate the dfference between the time and order is created inthe database and the schedule shipdate. This works fine during the weekdays Monday - Thursday but on Friday the date diff between Friday and Monday is 3 days.

DATEDIFF(D,CONVERT (VARCHAR(30),SH.CREATION_DATE_TIME_STAMP,1), SH.SCHEDULED_SHIP_DATE) BETWEEN 0 AND 1

Is there a way to have the datediff look at between 0 and 1 Monday to Thursday and 0 to 3 is the date is Friday?

Thanks in advance
RJL

 

[RiverGuy]

You can use the DATEPART function to get the day of the week, but be careful and read what SET DATEFIRST does before you use this function.

http://msdn.microsoft.com/en-us/library/ms181598.aspx.

Here's a working example to illustrate how you can get the day of week. To utilize it with your code, you can use a CASE statement.

SET DATEFIRST 7

DECLARE @Date DATETIME
SELECT @Date = '20081205'


IF DATEPART(dw, @Date) = 6
BEGIN
	PRINT 'Its Friday'
END
ELSE
BEGIN
	PRINT 'Its not Friday'
END

Here's an example with a where clause

SELECT *
FROM SomeTable
WHERE DATEDIFF(D,CONVERT (VARCHAR(30),SH.CREATION_DATE_TIME_STAMP,1), SH.SCHEDULED_SHIP_DATE) BETWEEN 0 AND
  CASE
    WHEN DATEPART(dw, SomeDateColumn) = 6 THEN  3  --It's Friday
    ELSE  1 --It's not Friday
  END

 

[RJL1]

Worked like a charm. Just what the doctor order.

Thanks