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

Transact-SQL (2005)

Dates between

Author

Topic

dass05555
Yak Posting Veteran

55 Posts

Posted - 2008-06-09 : 09:15:10

If I give the from date and to date i should get all dates the between from and to.

eg:
from:'01-June-2008'
To:'03-june-2008'

Result:

Date
'01-June-2008'
'02-June-2008'
'03-June-2008'

Thanks in advance..,

cool...,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-09 : 09:19:51

[code]DECLARE @StartDate DATETIME,
@EndDate DATETIME

SELECT @StartDate = '20080601',
@EndDate = '20080630'

;WITH Yak (theDate)
AS (
SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', @startDate), '19000101')

UNION ALL

SELECT DATEADD(DAY, 1, theDate)
FROM Yak
WHERE theDate < @EndDate
)

SELECT theDate
FROM Yak[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-09 : 13:54:59

Also

DECLARE	@StartDate DATETIME,
	@EndDate DATETIME

SELECT	@StartDate = '20080601',
	@EndDate = '20080630'

SELECT DATEADD(dd,number,@StartDate)
FROM master..spt_values
WHERE type='p'
AND DATEADD(dd,number,@StartDate)<= @EndDate

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-09 : 15:38:31

quote:

Originally posted by visakh16

Also

DECLARE	@StartDate DATETIME,
	@EndDate DATETIME

SELECT	@StartDate = '20080601',
	@EndDate = '20080630'

SELECT DATEADD(dd,number,@StartDate)
FROM master..spt_values
WHERE type='p'
AND DATEADD(dd,number,@StartDate)<= @EndDate

Note that filtering type 'p' would return only 256 numbers so it may not work if the date span is more than that. You should have a number table or use as I used here

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

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-09 : 15:41:48

quote:
Originally posted by dass05555

If I give the from date and to date i should get all dates the between from and to.

eg:
from:'01-June-2008'
To:'03-june-2008'

Result:

Date
'01-June-2008'
'02-June-2008'
'03-June-2008'

Thanks in advance..,

cool...,

You should use universal format YYYYMMDD to work with all date settings. If your Server's language is not English, you would get error

Madhivanan

Failing to plan is Planning to fail

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-06-09 : 15:55:55

quote:

Originally posted by Peso

DECLARE	@StartDate DATETIME,
	@EndDate DATETIME

SELECT	@StartDate = '20080601',
	@EndDate = '20080630'

;WITH Yak (theDate)
AS (
	SELECT	DATEADD(DAY, DATEDIFF(DAY, '19000101', @startDate), '19000101')

	UNION ALL

	SELECT	DATEADD(DAY, 1, theDate)
	FROM	Yak
	WHERE	theDate < @EndDate
)

SELECT	theDate
FROM	Yak

E 12°55'05.25"
N 56°04'39.16"

it fails when enddate - startdate > 100

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-09 : 15:58:42

quote:
Originally posted by rohitkumar

quote:
Originally posted by Peso

DECLARE @StartDate DATETIME,
@EndDate DATETIME

SELECT @StartDate = '20080601',
@EndDate = '20080630'

;WITH Yak (theDate)
AS (
SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', @startDate), '19000101')

UNION ALL

SELECT DATEADD(DAY, 1, theDate)
FROM Yak
WHERE theDate < @EndDate
)

SELECT theDate
FROM Yak

E 12°55'05.25"
N 56°04'39.16"

it fails when enddate - startdate > 100

Use

SELECT theDate
FROM Yak
OPTION (MAXRECURSION 0)

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-06-09 : 16:58:59

You could use the date table function on the link below.

Date Table Function F_TABLE_DATE:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

select
	a.[DATE]
from
	F_TABLE_DATE('01-June-2008','03-June-2008') a
order by
	a.[DATE]

CODO ERGO SUM

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-06-09 : 23:13:35

I second the F_Table_Date

It's is one of the most useful general functions out there.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 00:16:30

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

Also

DECLARE @StartDate DATETIME,
@EndDate DATETIME

SELECT @StartDate = '20080601',
@EndDate = '20080630'

SELECT DATEADD(dd,number,@StartDate)
FROM master..spt_values
WHERE type='p'
AND DATEADD(dd,number,@StartDate)<= @EndDate

Note that filtering type 'p' would return only 256 numbers so it may not work if the date span is more than that. You should have a number table or use as I used here

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

Madhivanan

Failing to plan is Planning to fail

Sorry i didnt check it in sql 2000. Note that in sql 2005 & 2008
select count(*) from master..spt_values where type='p' returns 2048 records with number values from 0 to 2047.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources