How to set a date conditionally in SQL

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul - 30 Sep 2008 22:23 GMT

Hi I have a stored procedure that inserted data into a database and also
inserts a due date. I need the query to set the due date as follows;
If the 5th day of the month falls on a saturday I want to set the due date
to the friday prior (4th), if the 5th falls on a sunday I want to set the due
date on the following monday (6th). Thanks.

Signature

Paul G
Software engineer.

Reply to this Message

Eric Isaacs - 30 Sep 2008 22:42 GMT

Use the DATEPART function to determine the weekday...

DECLARE @DueDate DATETIME
SET @DueDate = '2/4/2006' --use your current logic to determine the
correct due date.

SELECT @DueDate = CASE
WHEN DATEPART(weekday, @DueDate) = 7 THEN
DATEADD(DAY, -1, @DueDate)
WHEN DATEPART(weekday, @DueDate) = 1 THEN
DATEADD(DAY, 1, @DueDate)
ELSE
@DueDate
END

SELECT @DueDate

- Eric Isaacs

Reply to this Message

Plamen Ratchev - 30 Sep 2008 22:50 GMT

You can use a CASE expressions and the date/time functions to calculate
the due date (note this calculation is dependent on SET DATEFIRST):

SELECT CASE DATEPART(dw,
DATEADD(DAY, 4,
DATEADD(MONTH,
DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)))
WHEN 1 -- Sunday
THEN DATEADD(DAY, 5,
DATEADD(MONTH,
DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0))
WHEN 7 -- Saturday
THEN
DATEADD(DAY, 3,
DATEADD(MONTH,
DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0))
ELSE DATEADD(DAY, 4,
DATEADD(MONTH,
DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0))
END AS due_date;

Signature

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message