# Find how many week day crossed in a given date

• Rock

Old Hand

Points: 359

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.

Regards

Janu

• This topic was modified 9 months, 3 weeks ago by  Rock.
• This topic was modified 9 months, 2 weeks ago by  Rock.
• DesNorton

SSC-Insane

Points: 23277

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 9 months, 3 weeks ago by  DesNorton. Reason: Fixed the offset for Day1
• DesNorton

SSC-Insane

Points: 23277

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 ENDGO`

It can be used to check a single date

`DECLARE @TheDate date = '2019-12-17';SELECT @TheDate AS TheDate, d.TheDay, d.NumDaysFROM 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.NumDaysFROM #SampleData AS srcCROSS APPLY dbo.itvfGetDays(src.TheDate) AS d;`

• MVDBA (Mike Vessey)

SSC-Insane

Points: 21757

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: 76739

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

• schleep

SSChampion

Points: 12551

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

• This reply was modified 9 months, 2 weeks ago by  schleep. Reason: Edited to correct formula per Jeff's observation below. (Thanks Jeff)
• Jonathan AC Roberts

SSCoach

Points: 17334

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;">

1. If I give 04th Dec 2019 as input > It should say, It is Tuesday and this date is first tuesday
2. 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)
3. It applies for all the dates given.
<li style="list-style-type: none;">

Regards

Janu

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

Ten Centuries

Points: 1076

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 datetimeasbegin	declare @d datetime;	select @d=cast(convert(varchar, @year) + '-' + convert(varchar, @month) + '-' + convert(varchar, @day) as datetime)	return  @d endgodeclare  @dt				datetime='2019-12-17';declare  @input_year		int=datepart(year, @dt),  @input_month		int=datepart(month, @dt);declare  @test_month		date=dbo.fn_datefromparts(@input_year,@input_month,1)declare  @days_in_month	int=(select datepart(day, (select dateadd(day,-1,dateadd(month,1,@test_month)))));withmon_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_sevfrom  mon_cte;`

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

• Jeff Moden

SSC Guru

Points: 997112

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".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

• Jeff Moden

SSC Guru

Points: 997112

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;">

1. If I give 04th Dec 2019 as input > It should say, It is Tuesday and this date is first tuesday
2. 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)
3. It applies for all the dates given.

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".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

• Jeff Moden

SSC Guru

Points: 997112

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".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

• Jeff Moden

SSC Guru

Points: 997112

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;">

1. If I give 04th Dec 2019 as input > It should say, It is Tuesday and this date is first tuesday
2. 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)
3. It applies for all the dates given.
<li style="list-style-type: none;">
<li style="list-style-type: none;">

Regards

Janu

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".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

• jcelko212 32090

SSCrazy Eights

Points: 9028

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,

...);

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:

• Jeff Moden

SSC Guru

Points: 997112

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".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"