Find how many week day crossed in a given date

Question

Post reply

Find how many week day crossed in a given date

Rock

Old Hand

Points: 367
More actions
December 4, 2019 at 7:18 am
Go to Answer
#3704151
Team
I have a scenario in SQL Query. If I Give a day, The output should say which day and how many days crossed in a given month
For Ex
1. If I give 04th Dec 2019 as input > It should say, It is WED and this date is first wed
2. If the Input is 17th Dec 2019 > The output is Tue, Its 3rd Tuesday in this month. (3rd Dec - first tue, 10th Dec is 2nd Tue and 17 Dec is 3rd Wed in the month of December)
3. It applies for all the dates given.
Please suggest some solution.
Regards
Janu
- This topic was modified 4 years, 11 months ago by Rock.
- This topic was modified 4 years, 11 months ago by Rock.

DesNorton

SSC-Insane

Points: 24386

December 4, 2019 at 8:10 am

Answer

#3704165

You can turn the code into a function

CREATE FUNCTION dbo.itvfGetDays(@TheDate date)
RETURNS TABLE WITH SCHEMABINDING AS 
RETURN SELECT TheDay  = CASE DATEDIFF(dd, 0, @TheDate) %7
                          WHEN 0 THEN 'Monday'
                          WHEN 1 THEN 'Tuesday'
                          WHEN 2 THEN 'Wednesday'
                          WHEN 3 THEN 'Thursday'
                          WHEN 4 THEN 'Friday'
                          WHEN 5 THEN 'Saturday'
                          WHEN 6 THEN 'Sunday'
                        END
            , NumDays = DAY(@TheDate) /7 + CASE WHEN DAY(@TheDate) %7 = 0 THEN 0 ELSE 1 END
GO

It can be used to check a single date

DECLARE @TheDate date = '2019-12-17';

SELECT @TheDate AS TheDate, d.TheDay, d.NumDays
FROM dbo.itvfGetDays(@TheDate) AS d;

Or to check against a table

CREATE TABLE #SampleData (TheDate date);

INSERT INTO #SampleData ( TheDate )
VALUES ('2019-12-01'), ('2019-12-02'), ('2019-12-03'), ('2019-12-04'), ('2019-12-05'), ('2019-12-06'), ('2019-12-07')
     , ('2019-12-08'), ('2019-12-09'), ('2019-12-10'), ('2019-12-11'), ('2019-12-12'), ('2019-12-13'), ('2019-12-14')
     , ('2019-12-15'), ('2019-12-16'), ('2019-12-17'), ('2019-12-18'), ('2019-12-19'), ('2019-12-20'), ('2019-12-21')
     , ('2019-12-22'), ('2019-12-23'), ('2019-12-24'), ('2019-12-25'), ('2019-12-26'), ('2019-12-27'), ('2019-12-28')
     , ('2019-12-29'), ('2019-12-30'), ('2019-12-31');


SELECT src.TheDate, d.TheDay, d.NumDays
FROM #SampleData AS src
CROSS APPLY dbo.itvfGetDays(src.TheDate) AS d;

How to post data/code on a forum to get the best help.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24386 More actions · Answer 1

Something like this

DECLARE @TheDate date = '2019-12-17';

SELECT TheDay = CASE DATEDIFF(dd, 0, @TheDate) %7
                  WHEN 0 THEN 'Monday'
                  WHEN 1 THEN 'Tuesday'
                  WHEN 2 THEN 'Wednesday'
                  WHEN 3 THEN 'Thursday'
                  WHEN 4 THEN 'Friday'
                  WHEN 5 THEN 'Saturday'
                  WHEN 6 THEN 'Sunday'
                END
  , NumDays = DAY(@TheDate) /7 + CASE WHEN DAY(@TheDate) %7 = 0 THEN 0 ELSE 1 END

This reply was modified 4 years, 11 months ago by DesNorton. Reason: Fixed the offset for Day1

How to post data/code on a forum to get the best help.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

if you look at recursive CTEs you can build a list of dates within your boundaries

then use SELECT DATEPART (DAY,GETDATE()) and exclude the Saturday and sunday values then count...

i iknow i haven't given you the full code, but I hope it's a start

MVDBA

drew.allen SSC Guru Points: 76985 More actions · Answer 3

MVDBA (Mike Vessey) wrote:

if you look at recursive CTEs you can build a list of dates within your boundaries
then use SELECT DATEPART (DAY,GETDATE()) and exclude the Saturday and sunday values then count...
i iknow i haven't given you the full code, but I hope it's a start

A recursive CTE is a horrible way to build a list of dates. You're much better off using a tally table.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

schleep SSChampion Points: 12585 More actions · Answer 4

DECLARE @dDate DATE = '11 Dec 2019'
SELECT DATENAME(DW, @dDate), ((DAY(@dDate)-1) / 7) + 1

This reply was modified 4 years, 11 months ago by schleep. Reason: Edited to correct formula per Jeff's observation below. (Thanks Jeff)

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 5

Rock wrote:

Team
I have a scenario in SQL Query. If I Give a day, The output should say which day and how many days crossed in a given month
For Ex
<li style="list-style-type: none;">
If I give 04th Dec 2019 as input > It should say, It is Tuesday and this date is first tuesday
If the Input is 17th Dec 2019 > The output is Wednesday, Its 3rd Tuesday in this month. (3rd Dec - first tue, 10th Dec is 2nd Tue and 17 Dec is 3rd Wed in the month of December)
It applies for all the dates given.
<li style="list-style-type: none;">
Please suggest some solution.
Regards
Janu

I'm confused by your question:

4th December was a Wednesday but you want it to say Tuesday?

17th December is going to be a Tuesday but you want it to say Wednesday?

Steve Collins SSCrazy Points: 2157 More actions · Answer 6

To see if DesNorton's solution worked (it does) and to see if the interval could be created in Sql 2012 I tried with this sql although I don't have any way of knowing if it actually works in Sql 2012. It does work in Azure Sql compatibility level 140. It doesn't use any newer date functions and I copied (from the internet) a function that returns the date from parts in 2012. As long as the sys.objects table has more then 31 rows

create function dbo.fn_datefromparts(@year int, @month int, @day int)
returns datetime
as
begin
declare @d datetime;

select @d=cast(convert(varchar, @year) + '-' + convert(varchar, @month) + '-' + convert(varchar, @day) as datetime)
return  @d 
end
go

declare
  @dtdatetime='2019-12-17';
declare
  @input_yearint=datepart(year, @dt),
  @input_monthint=datepart(month, @dt);
declare
  @test_monthdate=dbo.fn_datefromparts(@input_year,@input_month,1)
declare
  @days_in_monthint=(select datepart(day, (select dateadd(day,-1,dateadd(month,1,@test_month)))));

with
mon_cte(dt) as (
select top(@days_in_month)
  dateadd(day, (row_number() over (order by (select null)))-1, @test_month)
from
  sys.objects)
select 
  dt,
  day(dt)/7 div_sev,
  day(dt)%7 mod_sev
from
  mon_cte;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

drew.allen wrote:

MVDBA (Mike Vessey) wrote:
if you look at recursive CTEs you can build a list of dates within your boundaries
then use SELECT DATEPART (DAY,GETDATE()) and exclude the Saturday and sunday values then count...
i iknow i haven't given you the full code, but I hope it's a start
A recursive CTE is a horrible way to build a list of dates. You're much better off using a tally table.
Drew

Mike,

I agree with Drew and will state it a slightly different way... never use rCTEs (Recursive CTEs) to do anything that provides a series of numbers... in other words, never use them to count. Please see the following article for why. As Drew says, they're "horrible"!

https://www.sqlservercentral.com/articles/hidden-rbar-counting-with-recursive-ctes

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 8

Rock wrote:

Team
I have a scenario in SQL Query. If I Give a day, The output should say which day and how many days crossed in a given month
For Ex
<li style="list-style-type: none;">
If I give 04th Dec 2019 as input > It should say, It is Tuesday and this date is first tuesday
If the Input is 17th Dec 2019 > The output is Wednesday, Its 3rd Tuesday in this month. (3rd Dec - first tue, 10th Dec is 2nd Tue and 17 Dec is 3rd Wed in the month of December)
It applies for all the dates given.
Please suggest some solution.
Regards
Janu

Here's my take on it...

First, here are the two test dates from the original post but in the form of a test table...

--===== Create and populate the test table.
 CREATE TABLE #TestTable
        (SomeDT DATETIME)
;
 INSERT INTO #TestTable
        (SomeDT)
 VALUES  (' 4 Dec 2019')
        ,('17 Dec 2019')
;

And here's my solution to the problem...

--===== My take on the solution
 SELECT  SomeDT
        ,DoW  = DATENAME(dw,SomeDT)
        ,DoW# = (DAY(SomeDT)-1)/7+1
   FROM #TestTable
;

The DoW column (DoW = Day of Week) is simple to come by. SQL Server has the DATENAME function to do it and it auto-magically adjusts to the current language if it's supported. A lot of people simply don't know this function exists.

For the DoW# column (Day of Week number for the month), it's also easy. Just do a proper "modulus" (we're using the quotient of the base division rather than the remainder) on the DAY of the month. A "proper" modulus works on a zero base so we get the day of the month, subtract 1 from that, divide that by 7 to come up with the grouping for each day of the month (and it's all integer math so no decimals involved), and then add 1 back to that.

To be honest, the formula for both columns is easy enough to remember, I wouldn't waste the time to write a function for either column.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 9

schleep wrote:

DECLARE @dDate DATE = '11 Dec 2019'
SELECT DATENAME(DW, @dDate), (DAY(@dDate) / 7) + 1

Try the date of '7 Dec 2019' and see that it comes up as the 2nd Saturday, which is impossible because it's only the 7th day. You need to subtract 1 from the day to form a proper modulus, which is zero based. Day is unit based.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 10

Jonathan AC Roberts wrote:

Rock wrote:
Team
I have a scenario in SQL Query. If I Give a day, The output should say which day and how many days crossed in a given month
For Ex

<li style="list-style-type: none;">
<li style="list-style-type: none;">
If I give 04th Dec 2019 as input > It should say, It is Tuesday and this date is first tuesday
If the Input is 17th Dec 2019 > The output is Wednesday, Its 3rd Tuesday in this month. (3rd Dec - first tue, 10th Dec is 2nd Tue and 17 Dec is 3rd Wed in the month of December)
It applies for all the dates given.
<li style="list-style-type: none;">
<li style="list-style-type: none;">
Please suggest some solution.
Regards
Janu
I'm confused by your question:
4th December was a Wednesday but you want it to say Tuesday?
17th December is going to be a Tuesday but you want it to say Wednesday?

Heh... it is confusing and I think he may have just been using the wrong calendar somewhere along the line or had a physical calendar that maybe started on a Monday and then things went to hell from there. So, like a lot of the others, I may have made a mistake by assuming I knew what the OP really wanted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 11

In your case, I would use the ISO-8601 week-date format. You can downoad such a calendar from the internet. The format is yyyyW[0-5][0-9]-[1-7] where the week is 01-53, depending on the year and day of the week is (1= Monday, 7= Sunday).

Build a calendar table with one column for the calendar data and other columns to show whatever your business needs in the way of temporal information. Do not try to calculate holidays in SQL -- Easter alone requires too much math and there are two of them (Orthodox and Catholic).

The Ordinal business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.

CREATE TABLE Calendar

(cal_date DATE NOT NULL PRIMARY KEY,

ordinal_business_nbr INTEGER NOT NULL,

...);

INSERT INTO Calendar

VALUES ('2007-04-05', 42);

('2007-04-06', 43); -- Good Friday

('2007-04-07', 43);

('2007-04-08', 43); -- Easter Sunday

('2007-04-09', 44);

('2007-04-10', 45); -- Tuesday, back to work

To compute the business days from '2007-04-05', Thursday before Easter in 2007 to '2007-04-10', the next Tuesday:

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 12

jcelko212 32090 wrote:

In your case, I would use the ISO-8601 week-date format. You can downoad such a calendar from the internet. The format is yyyyW[0-5][0-9]-[1-7] where the week is 01-53, depending on the year and day of the week is (1= Monday, 7= Sunday).

I don't see how that will provide any benefit to solving the OP's posted requirements. Please explain why you think it will.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)