Days in between Dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Days in between Dates

Author

Topic

thivya
Starting Member

4 Posts

Posted - 2003-07-09 : 10:57:19

Hi

Here is my query. How to find the No.of Mondays in between two dates?
in a simple query.

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-07-09 : 12:10:09

I don't haev time to solve this offhand, but I think this is the basic concept you'll need.

Create a tally table that has every day between your two days. For the month of july you'd haev 31 rows with one column. That colum would be the day part of the date (1-31). Then you would look at that table and see which of the values (do a date add on the begin date with the number that is in the tally table) and see if it's Day of Week Datepart was Monday and count those. See below for a day of Week example.


SELECT DATEPART(dw, '7/6/2003')

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-09 : 12:43:13

doin' the TALLY thing..Oh Yeah...


USE Northwind
GO

CREATE TABLE #myTally (x int)
GO

DECLARE @z int
SELECT @z = 1
SET NOCOUNT ON
WHILE @z < 31
	BEGIN
		INSERT INTO #myTally (x) SELECT @z
		SELECT @z = @z + 1
	END 
SET NOCOUNT OFF
GO

DECLARE @x datetime, @y datetime

SELECT @x = '1/1/2003', @y = '1/31/2003'

-- Looking at my Calendar means the answer is 4 Mondays

SELECT COUNT(*)
  FROM #myTally
 WHERE DatePart(dw,x) = 2
   AND x BETWEEN datepart(d,@x) and datepart(d,@y)

GO

Drop TABLE #myTally
GO

Brett

8-)

thivya
Starting Member

4 Posts

Posted - 2003-07-10 : 03:22:44

Thankx
But with out using temp table & loops can be able to do it in a single query?

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-10 : 04:00:30

Yes - er simple....

It will need datediff, @@datefirst (or a set datefirst) and maybe a couple of dateadds.
And I really hate dates

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-10 : 04:27:00

Think it's

datediff(wk,dateadd(dd,(9 - @@datefirst - datepart(dw,@d1))%7,@d1), dateadd(dd,(9 - @@datefirst - datepart(dw,@d2))%7,@d2))

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-07-10 : 06:35:43

DATEDIFF(wk) isn't affected by @@DATEFIRST: it just counts Saturday-Sunday boundaries.
If 'Mondays in between' means 'number of Sunday-Monday boundaries crossed, it's just:
DATEDIFF(wk, @d1-1, @d2-1)
Effectively, this excludes the starting day and includes the ending day. If you want to incude the starting day, change it to @d1-2. If you want to exclude the ending day, change it to @d2-2.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-10 : 10:22:25

Works like a champ.

Thanks Arnold


DECLARE @d1 datetime, @d2 datetime

SELECT @d1 = '1/7/2003', @d2 = '01/9/2003'

SELECT DATEDIFF(wk, @d1-1, @d2-1)

Brett

8-)

thivya
Starting Member

4 Posts

Posted - 2003-07-11 : 09:14:09

quote:

DATEDIFF(wk) isn't affected by @@DATEFIRST: it just counts Saturday-Sunday boundaries.
If 'Mondays in between' means 'number of Sunday-Monday boundaries crossed, it's just:
DATEDIFF(wk, @d1-1, @d2-1)
Effectively, this excludes the starting day and includes the ending day. If you want to incude the starting day, change it to @d1-2. If you want to exclude the ending day, change it to @d2-2.

[url][/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources