SQL . the Sp or Function Should Calculate the Next Date for Friday

sql server 2008 find next friday or last day of month

I'm not an expert in SQL server, but this should get you close. Put this in a SQL Function:

DECLARE @date DATETIME = '10/03/2013';

SELECT  MIN(Date) AS NextFridayOrEoMonth
FROM    ( SELECT    DATEADD(DAY, ( CASE DATEPART(DW, @date)
                                     WHEN 7 THEN 6
                                     ELSE 6 - DATEPART(DW, @date)
                                   END ), @date) AS Date
          UNION
          SELECT    DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, @date) + 1, 0)) AS Date
        ) AS dates;

EDIT: Actually, here it is as a function. Good luck!

CREATE FUNCTION dbo.NextFridayOrEoMonth ( @date DATETIME )
RETURNS DATETIME
    WITH SCHEMABINDING,
         RETURNS NULL ON NULL INPUT
AS 
    BEGIN

        DECLARE @result DATETIME;

        SELECT  @result = MIN(Date)
        FROM    ( SELECT    DATEADD(DAY, ( CASE DATEPART(DW, @date)
                                             WHEN 7 THEN 6
                                             ELSE 6 - DATEPART(DW, @date)
                                           END ), @date) AS Date
                  UNION
                  SELECT    DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, @date) + 1, 0)) AS Date
                ) AS dates;

        RETURN @result;
    END
GO  

SELECT  dbo.NextFridayOrEoMonth('10/3/2013') AS NextFridayOrEoMonth; -- 2013-10-04
SELECT  dbo.NextFridayOrEoMonth('10/5/2013') AS NextFridayOrEoMonth; -- 2013-10-11
SELECT  dbo.NextFridayOrEoMonth('10/14/2013') AS NextFridayOrEoMonth; -- 2013-10-18
SELECT  dbo.NextFridayOrEoMonth('10/25/2013') AS NextFridayOrEoMonth; -- 2013-10-25
SELECT  dbo.NextFridayOrEoMonth('10/26/2013') AS NextFridayOrEoMonth; -- 2013-10-31
SELECT  dbo.NextFridayOrEoMonth('10/29/2013') AS NextFridayOrEoMonth; -- 2013-10-31
GO

Note: Code reviews / comments appreciated.

SQL Server - get next Friday in DATETIME

I've updated this answer to a similar question which takes @@DATEFIRST into consideration:

EDIT/CORRECTION:

SELECT DATEADD(DAY, 13 - (@@DATEFIRST + (DATEPART(WEEKDAY,GETDATE()) %7)), GETDATE())

Find next certain weekday with mysql stored procedure

Use DAYOFWEEK(now())
In fact you naad a CASE inside CASE.

depending on the day of week number add appropriate amount of days to now()

SELECT
    CASE case_number
    WHEN 1 THEN CASE DAYOFWEEK(now())
                 WHEN 0 THEN DATE_ADD(CURDATE(), INTERVAL x DAY)
                 WHEN 1 THEN DATE_ADD(CURDATE(), INTERVAL x DAY)
                 WHEN 2 THEN DATE_ADD(CURDATE(), INTERVAL x DAY)
    .....
                END
    WHEN 2 THEN ''
    WHEN 3 THEN ''
    WHEN 4 THEN ''
    ELSE 'unknown'
    END
FROM customer

Find the next occurance of a day of the week in SQL

Here's some example SQL that I came up with. 3 iterations so you can follow how I got to the end. The 3rd iteration should be something you can incorporate into a WHERE clause by substituting your column names for the variables.

Setup:

DECLARE @Startdate DATETIME,@currentdate datetime
SET @Startdate = '1-26-2012' 
SET @Currentdate = '1-23-2012'

--This section just normalizes it so you can use 7 as the interval
--The offset depends on your current setting for DATEFIRST, U.S. English default is 7, Sunday.
-- see http://msdn.microsoft.com/en-us/library/ms187766.aspx 
DECLARE @StartDateWorkingDayOfWeek int,@CurrentDateWorkingDayOfWeek int
SELECT @StartDateWorkingDayOfWeek =(DATEPART(weekday,@Startdate)-2)
SELECT @CurrentDateWorkingDayOfWeek=(DATEPART(weekday,@Currentdate)-2)

Iteration #1

--Iteration 1 
IF @StartDateWorkingDayOfWeek < @CurrentDateWorkingDayOfWeek
SELECT DATEADD(DAY,DATEDIFF(DAY,0,@Currentdate)/7*7 + 7,@StartDateWorkingDayOfWeek)
else
SELECT DATEADD(DAY,DATEDIFF(DAY,0,@Currentdate)/7*7 + 0,@StartDateWorkingDayOfWeek)

Iteration #2

--Iteration 2
SELECT DATEADD(DAY,DATEDIFF(DAY,0,@Currentdate)/7*7 + 

CASE WHEN @StartDateWorkingDayOfWeek < @CurrentDateWorkingDayOfWeek
    then 7
    ELSE 0
    end

    ,@StartDateWorkingDayOfWeek)

Iteration #3

--iteration 3
SELECT DATEADD(DAY,DATEDIFF(DAY,0,@Currentdate)/7*7 + 

CASE WHEN (DATEPART(weekday,@Startdate)-2) < (DATEPART(weekday,@Currentdate)-2)
    then 7
    ELSE 0
    end

    ,(DATEPART(weekday,@Startdate)-2))

Hat tip to this article:
http://www.sqlmag.com/article/tsql3/datetime-calculations-part-3

how To get date greater then or equal to today from day name (Sunday,Monday,etc) in mysql select query

I Have Tryed More Times And Then i Got Finally My Answer as

SELECT * , IF( (
STR_TO_DATE( CONCAT( YEAR( NOW( ) ) , WEEK( NOW( ) , 1 ) , weekday ) , '%X%V %W' ) >= CURDATE( ) ) , STR_TO_DATE( CONCAT( YEAR( NOW( ) ) , WEEK( NOW( ) , 1 ) , weekday ) , '%X%V %W' ) , (
DATE_ADD( STR_TO_DATE( CONCAT( YEAR( NOW( ) ) , WEEK( NOW( ) , 1 ) , weekday ) , '%X%V %W' ) , INTERVAL 7
DAY )
)
) AS edate
FROM tbl_event

the output of this query is And please note today date is 14-07-2016.
Query Result

This code work fine, it is tested code.
Thanks.

SQL Server: calculate variable dates in stored procedure

It is possible to calculate it, here are 3 examples

declare @month int = 6

select 
    -- first Monday in @month of current year
    dateadd(d, datediff(d, 0, dateadd(m, @month-1, 
      dateadd(yy, datediff(yy, 0, getdate()), 6)))/7*7, 0),
    -- last Monday in @month of current year
    dateadd(d, datediff(d, 0, dateadd(m, @month, 
      dateadd(yy, datediff(yy, 0, getdate()), 6)))/7*7, -7),
    -- third Friday in @month of current year
    dateadd(d, datediff(d, 0, dateadd(m, @month-1, 
      dateadd(yy, datediff(yy, 0, getdate()), 3)))/7*7, 18)

---

Related Topics