SQL Server : function to display previous Friday's date if @date is not a Friday

Question

I am a newbie to advanced SQL, I need to write a SVF in SQL Server to return previous Friday's date if the parameter date I entered is not a Friday.

For example, today is Wednesday, 7/1/2015, this function should return previous Friday 6/26/2015. Suppose today is Friday 7/3/2015, then the function will return 7/3/2015 (no change).

Ask for help if you can edit my following code:

CREATE FUNCTION fnDateCheck (@date datetime)
RETURNS @FridayDate date
AS
BEGIN 
    DECLARE @FridayDate

    if
        RETURN  

    else if 
        RETURN 

END
GO

I found this reference post, quite similar to what I am asking but it's not a function: Get last Friday's Date unless today is Friday using T-SQL

declare @date datetime;
set @date = '2012-08-10'

SELECT 
    CASE WHEN datepart(weekday, @date) > 5 
           THEN DATEADD(DAY, +4, DATEADD(WEEK, DATEDIFF(WEEK, 0, @date), 0)) 
         ELSE DATEADD(DAY, -3, DATEADD(WEEK, DATEDIFF(WEEK, 0, @date), 0))  
    END

Thanks so much!!

M.Ali · Answer 1 · 2015-07-01T23:48:25.733

CREATE FUNCTION fnDateCheck (@date datetime)
RETURNS datetime
AS
BEGIN 
   DECLARE @FridayDate DATETIME;

IF (DATEPART(WEEKDAY, @date ) <> 6)
   BEGIN
        SELECT @FridayDate = case when datepart(weekday, @date) >5 then
        DATEADD(DAY, +4, DATEADD(WEEK, DATEDIFF(WEEK, 0, @date), 0)) 
        else DATEADD(DAY, -3, DATEADD(WEEK, DATEDIFF(WEEK, 0, @date), 0)) end  
   END
ELSE 
   BEGIN
     SET @FridayDate = @date
    END

 RETURN @FridayDate;

END
GO

score 0 · Answer 2 · edited Jul 02 '15 at 00:02

0

Seems something like this would help you might need to reformat and obviously this isn't a whole function, but the part that does the logic.

SELECT case 
    when datepart(dw,getdate()) != 6 then DATEADD(wk,DATEDIFF(wk,7,GETDATE()),4)
    else GETDATE()
end

edited Jul 02 '15 at 00:02

jac

9,666
2
34
63

answered Jul 01 '15 at 23:39

Holmes IV

1,673
2
23
47

jac · Accepted Answer · 2015-07-01T23:56:26.303

0

The script you found works fine. You just need to write it into a function.

CREATE FUNCTION fnDateCheck
(
    @date datetime
)
RETURNS DATETIME
AS
    BEGIN 
        RETURN (SELECT CASE
            WHEN DATEPART(WEEKDAY, @date) > 5 THEN
                DATEADD(DAY, + 4, DATEADD(WEEK, DATEDIFF(WEEK, 0, @date), 0)) 
            ELSE
                DATEADD(DAY, - 3, DATEADD(WEEK, DATEDIFF(WEEK, 0, @date), 0))
        END)
    END
GO

edited Jul 01 '15 at 23:56

answered Jul 01 '15 at 23:50

jac

9,666
2
34
63

I tried your code, I use select fnDateCheck (2015-07-01), it returns 1905-06-30 00:00:00.000, was the date format I entered wrong? – azCats Jul 02 '15 at 00:04
@JerryLi Try, `fnDateCheck ('2015-07-01')` Notice the single quote (tick) marks. – jac Jul 02 '15 at 00:06
Hi, I tried M.Ali 's code below, it works good too. Could you briefly tell me what is the difference between your code and his? I guess my homework tonight is to understand both syntax and logics from both of your codes. – azCats Jul 02 '15 at 00:23
@JerryLi M.Ali's script adds an `IF` statement to skip the date manipulations and just return the entered date if the entered date is Friday. If you really want to understand imagine you have called the function with a date and walk through the script working out which lines are executed and what values are returned by the function. Once you imagine what the logic path through the function is you will better understand what is being done. – jac Jul 02 '15 at 03:05

SQL Server : function to display previous Friday's date if @date is not a Friday

3 Answers3