SELECT all dates within a range

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)

SELECT all dates within a range

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-11-05 : 07:33:58

[code]
DateFrom DateTo
1/1/2008 1/1/2008
7/2/2008 10/2/2008
[/code]

Is it possible to output all the dates from this table, for example this should output

1/1/2008
7/2/2008
8/2/2008
9/2/2008
10/2/2008

Can I do that with SQL?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-05 : 07:40:10

yes. use CROSS APPLY your table to F_TABLE_DATE

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-05 : 07:42:19

Yes, with the new CROSS APPLY function you can.

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

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-11-05 : 08:04:21

Hi,

Thanks for this. I have no experience with functions, i've managed to *install* the f_table_date function onto my database.

How do I call the function and apply to to my own table?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-05 : 08:07:47

[code]DECLARE @Sample TABLE (DateFrom DATETIME, DateTo DATETIME)

SET DATEFORMAT DMY

INSERT @Sample
SELECT '1/1/2008', '1/1/2008' UNION ALL
SELECT '7/2/2008', '10/2/2008'

SELECT d.DATE
FROM @Sample
CROSS APPLY F_TABLE_DATE(DateFrom, DateTo) AS d[/code]

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-05 : 08:08:20

[code]DECLARE @sample TABLE
(
DateFrom datetime,
DateTo datetime
)

SET dateformat dmy
INSERT INTO @sample (DateFrom, DateTo)
SELECT '1/1/2008', '1/1/2008' UNION ALL
SELECT '7/2/2008', '10/2/2008'

SELECT d.DATE
FROM @sample s CROSS apply F_TABLE_DATE(DateFrom, DateTo) d
ORDER BY d.DATE

/*
DATE
------------------------------------------------------
2008-01-01 00:00:00.000
2008-02-07 00:00:00.000
2008-02-08 00:00:00.000
2008-02-09 00:00:00.000
2008-02-10 00:00:00.000

(5 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-11-05 : 08:15:22

THANKS !!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-05 : 08:22:38

read about CROSS APPLY in BOL

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources