locked
Calculate weekday number of a month RRS feed

  • Question

  • Hi SQL-Experts,

    searching the Internet I found some posts to get the week of month, for example

    Declare @myInputDate Date, @myOutputDate Date 
    Set @myInputDate = '04/01/2014'
    Set @myInputDate = DATEADD(MONTH, 12 * DATEPART(Year, @myInputDate) - 22801 + DatePart(Month, @myInputDate), 5 - 1)
    If DATEPART(WeekDay, @myInputDate) In (7, 1) 
    Begin 
    	Set @myOutputDate = DATEADD(Day, (Case DATEPART(WeekDay, @myInputDate) When 7 Then 2 Else 1 End), @myInputDate) 
    End 
    Else
    Begin 
    	Set @myOutputDate = @myInputDate 
    End 
    Select DatePart(WeekDay, @myOutPutDate), @myOutputDate 

    or

    select cast(datename(week,getdate()) as int)-  cast( datename(week,dateadd(dd,1-day(getdate()),getdate())) as int)+1

    But I need the number of Weekday of the month. Today, on April 1st 2014 it will return 1, which is correct because it is the first Tuesday in this month.

    On April 7th 2014 the above will return 2, but I want to get 1 because is the first Monday in this month.

    Does anybody know a smart way to calculate the number of a special weekday in a month, like second monday, first Tuesday, fourth Sunday og April, Maiy .. by a given date.

    It would be great to have a SQL Statement because I cant use (create) as StoredProcedure or Function here.  

    Regards,

    Stefan


    www.sc-orchestrator.eu , Blog sc-orchestrator.eu


    • Edited by Stefan HorzMVP Tuesday, April 1, 2014 12:47 PM typo in topic
    Tuesday, April 1, 2014 12:46 PM

Answers

  • Thanks, perhaps anybody knows or has a T-SQL Statement where I don't need to create procedures or tables ...

     


    www.sc-orchestrator.eu , Blog sc-orchestrator.eu

    yep..you can use this

    --your date value passed
    DECLARE @dt datetime
    SET @dt='20140310'--pass any date here
    
    --your solution
    SELECT (DATEPART(wk,@dt)-DATEPART(wk,DATEADD(mm,DATEDIFF(mm,0,@dt),0))) + CASE WHEN DATEDIFF(dd,0,@dt)%7 >= DATEDIFF(dd,0,DATEADD(mm,DATEDIFF(mm,0,@dt),0))%7 THEN 1 ELSE 0 END AS weekdaynumberformonth


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    Tuesday, April 1, 2014 1:19 PM

All replies

  • This is where Calendar table is the most suitable candidate.

    Please find the below link to generate the calendar table:

    http://social.technet.microsoft.com/wiki/contents/articles/22776.t-sql-calendar-table.aspx

    • Proposed as answer by HimanshuSharma Tuesday, April 1, 2014 12:57 PM
    Tuesday, April 1, 2014 12:52 PM
  • Latheesh NK, I have often proposed your solution as an answer, but this time it seems odd... :)

    Chaos isn’t a pit. Chaos is a ladder. Many who try to climb it fail and never get to try again. The fall breaks them. And some are given a chance to climb, but they refuse. They cling to the realm, or the gods, or love. Illusions. Only the ladder is real. The climb is all there is.

    Tuesday, April 1, 2014 12:58 PM
  • Thanks, perhaps anybody knows or has a T-SQL Statement where I don't need to create procedures or tables ...

     


    www.sc-orchestrator.eu , Blog sc-orchestrator.eu

    Tuesday, April 1, 2014 12:58 PM
  • Thanks, perhaps anybody knows or has a T-SQL Statement where I don't need to create procedures or tables ...

     


    www.sc-orchestrator.eu , Blog sc-orchestrator.eu

    yep..you can use this

    --your date value passed
    DECLARE @dt datetime
    SET @dt='20140310'--pass any date here
    
    --your solution
    SELECT (DATEPART(wk,@dt)-DATEPART(wk,DATEADD(mm,DATEDIFF(mm,0,@dt),0))) + CASE WHEN DATEDIFF(dd,0,@dt)%7 >= DATEDIFF(dd,0,DATEADD(mm,DATEDIFF(mm,0,@dt),0))%7 THEN 1 ELSE 0 END AS weekdaynumberformonth


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    Tuesday, April 1, 2014 1:19 PM
  • I would build a calendar table with a ISO-8601 week-within-year format column in addition to the usual Common Era date. This format is 'yyyyWww-d' where yyyy is the year, W is a separator token, ww is (01-53) week number and d is (1-7) day of the week.

    You input any calendar date, find the week-within-year column and return the dates that match on a LIKE predicate.
    WHERE sale_day LIKE '2012W26-[67]'

    There are several websites with calendars you can cut & paste, but you can start your search with: http://www.calendar-365.com/week-number.html 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, April 1, 2014 1:20 PM
  • Thanks a lot Visakh!

    This works like a charm!


    www.sc-orchestrator.eu , Blog sc-orchestrator.eu

    Tuesday, April 1, 2014 1:28 PM