Data Range - 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

General SQL Server Forums

New to SQL Server Programming

Data Range

Author

Topic

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-04-30 : 07:22:18

Hi,

I need help with dates for my weekly report. Week starts on Sunday and ends on Saturday. In my view I have list of dates, how can I make a filter where dates should be within the week range. Example if I have, April 6, April 11, April 14, April 15. Week 1 should have April 6 and April 11 since week date is from April 6 to 12. Then April 14 and April 15 is for the next week. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-30 : 07:27:58

Where DateField>=@WeekStartDate AND DateField <= @WeekEndDate

Where @WeekStartDate & @WeekEndDate are your parameters representing start & end dates of the week.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-30 : 07:28:52

Or make yourself comfortable with the F_TABLE_DATE function present here at SQLTeam.

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

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-04-30 : 07:35:48

I tried it and i think it works. But it doesn't have results. Is there a problem? Here's my SQL formula:

SELECT SesDate
FROM dbo.bo2t
WHERE (SesDate >= 03 / 16 / 2008) AND (SesDate <= 03 / 22 / 2008)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-30 : 07:37:59

DO your table have enough data for this daterange?

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-04-30 : 07:38:46

Yes I have list of Dates for Column SesDate.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-30 : 07:40:36

And is SesDate a datetime field?

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-04-30 : 07:44:53

I got it. Here's the correct formula that worked: SELECT SesDate
FROM dbo.bo2t
WHERE (SesDate >= '03 /16/2008) AND (SesDate <= 03/22/2008')

However, I need 4 results of date range for the whole month of April. Is that possible? Example for April, I need list of dates for April 6 - 12, April 13 - 19, April 20 - 26.

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-04-30 : 08:07:51

Yes Peso :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-30 : 08:10:20

See how easy this is when using function F_TABLE_DATE?

SELECT		MIN(START_OF_WEEK_STARTING_SUN_DATE) AS FromDate,
		MAX(END_OF_WEEK_STARTING_SUN_DATE) AS ToDate
FROM		F_TABLE_DATE('20080401', '20080430') AS f
GROUP BY	WEEK_STARTING_SUN_SEQ_NO,
		YEAR_MONTH
HAVING		COUNT(*) = 7

Output is

FromDate	ToDate
2008-04-06	2008-04-12
2008-04-13	2008-04-19
2008-04-20	2008-04-26

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-30 : 08:14:08

Use the function Peso suggested

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-30 : 08:16:56

Here is the rest of the weeks for this decade

SELECT		YEAR_MONTH,
		MIN(START_OF_WEEK_STARTING_SUN_DATE) AS FromDate,
		MAX(END_OF_WEEK_STARTING_SUN_DATE) AS ToDate
FROM		F_TABLE_DATE('20080101', '20091231') AS f
GROUP BY	WEEK_STARTING_SUN_SEQ_NO,
		YEAR_MONTH
HAVING		COUNT(*) = 7
ORDER BY	YEAR_MONTH

List is here

YEAR_MONTH	FromDate	ToDate
200801	2008-01-06 00:00:00.000	2008-01-12 00:00:00.000
200801	2008-01-13 00:00:00.000	2008-01-19 00:00:00.000
200801	2008-01-20 00:00:00.000	2008-01-26 00:00:00.000
200802	2008-02-03 00:00:00.000	2008-02-09 00:00:00.000
200802	2008-02-10 00:00:00.000	2008-02-16 00:00:00.000
200802	2008-02-17 00:00:00.000	2008-02-23 00:00:00.000
200803	2008-03-02 00:00:00.000	2008-03-08 00:00:00.000
200803	2008-03-09 00:00:00.000	2008-03-15 00:00:00.000
200803	2008-03-16 00:00:00.000	2008-03-22 00:00:00.000
200803	2008-03-23 00:00:00.000	2008-03-29 00:00:00.000
200804	2008-04-06 00:00:00.000	2008-04-12 00:00:00.000
200804	2008-04-13 00:00:00.000	2008-04-19 00:00:00.000
200804	2008-04-20 00:00:00.000	2008-04-26 00:00:00.000
200805	2008-05-04 00:00:00.000	2008-05-10 00:00:00.000
200805	2008-05-11 00:00:00.000	2008-05-17 00:00:00.000
200805	2008-05-18 00:00:00.000	2008-05-24 00:00:00.000
200805	2008-05-25 00:00:00.000	2008-05-31 00:00:00.000
200806	2008-06-01 00:00:00.000	2008-06-07 00:00:00.000
200806	2008-06-08 00:00:00.000	2008-06-14 00:00:00.000
200806	2008-06-15 00:00:00.000	2008-06-21 00:00:00.000
200806	2008-06-22 00:00:00.000	2008-06-28 00:00:00.000
200807	2008-07-06 00:00:00.000	2008-07-12 00:00:00.000
200807	2008-07-13 00:00:00.000	2008-07-19 00:00:00.000
200807	2008-07-20 00:00:00.000	2008-07-26 00:00:00.000
200808	2008-08-03 00:00:00.000	2008-08-09 00:00:00.000
200808	2008-08-10 00:00:00.000	2008-08-16 00:00:00.000
200808	2008-08-17 00:00:00.000	2008-08-23 00:00:00.000
200808	2008-08-24 00:00:00.000	2008-08-30 00:00:00.000
200809	2008-09-07 00:00:00.000	2008-09-13 00:00:00.000
200809	2008-09-14 00:00:00.000	2008-09-20 00:00:00.000
200809	2008-09-21 00:00:00.000	2008-09-27 00:00:00.000
200810	2008-10-05 00:00:00.000	2008-10-11 00:00:00.000
200810	2008-10-12 00:00:00.000	2008-10-18 00:00:00.000
200810	2008-10-19 00:00:00.000	2008-10-25 00:00:00.000
200811	2008-11-02 00:00:00.000	2008-11-08 00:00:00.000
200811	2008-11-09 00:00:00.000	2008-11-15 00:00:00.000
200811	2008-11-16 00:00:00.000	2008-11-22 00:00:00.000
200811	2008-11-23 00:00:00.000	2008-11-29 00:00:00.000
200812	2008-12-07 00:00:00.000	2008-12-13 00:00:00.000
200812	2008-12-14 00:00:00.000	2008-12-20 00:00:00.000
200812	2008-12-21 00:00:00.000	2008-12-27 00:00:00.000
200901	2009-01-04 00:00:00.000	2009-01-10 00:00:00.000
200901	2009-01-11 00:00:00.000	2009-01-17 00:00:00.000
200901	2009-01-18 00:00:00.000	2009-01-24 00:00:00.000
200901	2009-01-25 00:00:00.000	2009-01-31 00:00:00.000
200902	2009-02-01 00:00:00.000	2009-02-07 00:00:00.000
200902	2009-02-08 00:00:00.000	2009-02-14 00:00:00.000
200902	2009-02-15 00:00:00.000	2009-02-21 00:00:00.000
200902	2009-02-22 00:00:00.000	2009-02-28 00:00:00.000
200903	2009-03-01 00:00:00.000	2009-03-07 00:00:00.000
200903	2009-03-08 00:00:00.000	2009-03-14 00:00:00.000
200903	2009-03-15 00:00:00.000	2009-03-21 00:00:00.000
200903	2009-03-22 00:00:00.000	2009-03-28 00:00:00.000
200904	2009-04-05 00:00:00.000	2009-04-11 00:00:00.000
200904	2009-04-12 00:00:00.000	2009-04-18 00:00:00.000
200904	2009-04-19 00:00:00.000	2009-04-25 00:00:00.000
200905	2009-05-03 00:00:00.000	2009-05-09 00:00:00.000
200905	2009-05-10 00:00:00.000	2009-05-16 00:00:00.000
200905	2009-05-17 00:00:00.000	2009-05-23 00:00:00.000
200905	2009-05-24 00:00:00.000	2009-05-30 00:00:00.000
200906	2009-06-07 00:00:00.000	2009-06-13 00:00:00.000
200906	2009-06-14 00:00:00.000	2009-06-20 00:00:00.000
200906	2009-06-21 00:00:00.000	2009-06-27 00:00:00.000
200907	2009-07-05 00:00:00.000	2009-07-11 00:00:00.000
200907	2009-07-12 00:00:00.000	2009-07-18 00:00:00.000
200907	2009-07-19 00:00:00.000	2009-07-25 00:00:00.000
200908	2009-08-02 00:00:00.000	2009-08-08 00:00:00.000
200908	2009-08-09 00:00:00.000	2009-08-15 00:00:00.000
200908	2009-08-16 00:00:00.000	2009-08-22 00:00:00.000
200908	2009-08-23 00:00:00.000	2009-08-29 00:00:00.000
200909	2009-09-06 00:00:00.000	2009-09-12 00:00:00.000
200909	2009-09-13 00:00:00.000	2009-09-19 00:00:00.000
200909	2009-09-20 00:00:00.000	2009-09-26 00:00:00.000
200910	2009-10-04 00:00:00.000	2009-10-10 00:00:00.000
200910	2009-10-11 00:00:00.000	2009-10-17 00:00:00.000
200910	2009-10-18 00:00:00.000	2009-10-24 00:00:00.000
200910	2009-10-25 00:00:00.000	2009-10-31 00:00:00.000
200911	2009-11-01 00:00:00.000	2009-11-07 00:00:00.000
200911	2009-11-08 00:00:00.000	2009-11-14 00:00:00.000
200911	2009-11-15 00:00:00.000	2009-11-21 00:00:00.000
200911	2009-11-22 00:00:00.000	2009-11-28 00:00:00.000
200912	2009-12-06 00:00:00.000	2009-12-12 00:00:00.000
200912	2009-12-13 00:00:00.000	2009-12-19 00:00:00.000
200912	2009-12-20 00:00:00.000	2009-12-26 00:00:00.000

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

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-04-30 : 08:21:40

Is this correct?

SELECT MIN(START_OF_WEEK_STARTING_SUN_DATE) AS FromDate,
MAX(END_OF_WEEK_STARTING_SUN_DATE) AS ToDate
FROM dbo.TechPhonesCalib.F_TABLE_DATE('20080301', '20080331') AS f
GROUP BY WEEK_STARTING_SUN_SEQ_NO,
YEAR_MONTH
HAVING COUNT(*) = 7

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-30 : 09:02:00

F_TABLE_DATE is a function, not a table.
You have to copy the code from SQLTeam and run it on your server.

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

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-04-30 : 09:43:04

what should be my correct formula to work?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources