how many days between two date? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

how many days between two date?

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-02-09 : 07:35:38

i want to know how many Saturday day between :
9/2/2009-1/3/2009

i want to get

how can i do it?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-02-09 : 07:37:18

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-09 : 08:17:46

select max(rowno) as count,date from
(select row_number() over( partition by datename(dw,dateadd(d,v.number,'2/9/2009')) order by dateadd(d,v.number,'2/9/2009')) as rowno,datename(dw,dateadd(d,v.number,'2/9/2009')) as date from
master..spt_values v where v.type = 'p' and v.number >0 and v.number <= datediff(d,'2/9/2009','3/1/2009' )+1) a
where date = 'saturday'
group by date

Jai Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-09 : 08:24:50

[code]DECLARE @From DATETIME,
@To DATETIME

SELECT @From = '20090209',
@To = '20090301'

;WITH Yak (theDate, isSaturday)
AS (
SELECT @From,
CASE WHEN DATENAME(WEEKDAY, @From) = 'Saturday' THEN 1 ELSE 0 END

UNION ALL

SELECT 1 + theDate,
CASE WHEN DATENAME(WEEKDAY, 1 + theDate) = 'Saturday' THEN 1 ELSE 0 END
FROM Yak
WHERE theDate <= @To
)

SELECT SUM(isSaturday)
FROM Yak[/code]

E 12°55'05.63"
N 56°04'39.26"

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-02-09 : 08:53:18

it is simple :

SELECT DateDiff(ww,'9/2/2009','1/3/2009')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-09 : 09:01:54

And so wrong. DATEDIFF(WEEK, ..., ...) is dependant on SET DATEFIRST setting.
And also working with "slots".

Test this

SET DATEFORMAT DMY
SELECT DateDiff(ww,'9/2/2009','1/3/2009') -- three saturdays, reported as three saturdays
SELECT DateDiff(ww,'8/2/2009','14/2/2009') -- one saturday, but reported as zero saturdays

And read about DATEDIFF here http://www.sqlteam.com/article/datediff-function-demystified

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources