report 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

SQL Server Development (2000)

report dates

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-20 : 08:04:58

Jeff writes "I am writing a report that is based off of date values. The end user wants to be able to choose any date they wish, but in doing so the report needs to reflect the entire week that the date falls in monday - sunday. Is there a way to do this in sql?

Thanks

Jeff"

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-20 : 09:50:54

[code]where [DataDate] >= dateadd(week, datediff(week, 0, [UserDate]), 0)
and [DataDate] >= dateadd(week, datediff(week, 0, [UserDate])+1, 0) [/code]
You will need to be sure that your database server is set to consider Monday as the first day of each week.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-20 : 09:53:45

quote:
Originally posted by blindman

where [DataDate] >= dateadd(week, datediff(week, 0, [UserDate]), 0)
and [DataDate] <= dateadd(week, datediff(week, 0, [UserDate])+1, 0)

You will need to be sure that your database server is set to consider Monday as the first day of each week.

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-10-20 : 10:02:27

Here's a link to a function that you can use that returns the first day of the week given any input date:

http://www.sql-server-helper.com/functions/get-first-day-of-week.aspx

To use this in your query:

SELECT * FROM YourTable
WHERE YourDate >= [dbo].[ufn_GetFirstDayOfWeek] ( <User Selected Date> ) AND
YourDate < [dbo].[ufn_GetFirstDayOfWeek] ( <User Selected Date> ) + 7

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-20 : 17:08:26

You can use the function in this topic, which will allow you to select any day of week you want as the start of the week.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

It is also easy to compute it directly. This code show how to do it for any day of the week you want as the start of the week.


select
	DATE,
	Week_Starting_Sun =
		dateadd(dd,(datediff(dd,-53684,a.DATE)/7)*7,-53684),
	Week_Starting_Mon =
		dateadd(dd,(datediff(dd,-53690,a.DATE)/7)*7,-53690),
	Week_Starting_Tue =
		dateadd(dd,(datediff(dd,-53689,a.DATE)/7)*7,-53689),
	Week_Starting_Wed =
		dateadd(dd,(datediff(dd,-53688,a.DATE)/7)*7,-53688),
	Week_Starting_Thu =
		dateadd(dd,(datediff(dd,-53687,a.DATE)/7)*7,-53687),
	Week_Starting_Fri =
		dateadd(dd,(datediff(dd,-53686,a.DATE)/7)*7,-53686),
	Week_Starting_Sat =
		dateadd(dd,(datediff(dd,-53685,a.DATE)/7)*7,-53685)
from
	( select DATE = getdate() ) a

Ths advantage of these is that they return the same result, no matter what the setting of SET DATEFIRST is. The function in the previous post, [dbo].[ufn_GetFirstDayOfWeek], will return different values for the same input date, depending on the setting of DATEFIRST.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources