SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Start of Week Function
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 05/11/2007 :  04:00:56  Show Profile  Reply with Quote
quote:
I don't think your code does what you hoped.


Thanks for your thoughts Michael they are greatly appreciated. Fortunately for me, in the particular application this is used in it makes no difference whether the Monday or the Sunday is calculated which is probably why it hasn't been noticed.

Having said that, for the sake of completeness, and in case anything changes in future I would like to fix it. So I have zeroed the time element, so I now have

DECLARE @DT datetime

SET @DT = dateadd(dd,datediff(dd,0,'20070430 13:0:0'),0) -- datepart only

select	dateadd(d,-1*cast(@DT as int)%7,@DT)


Which I think gives the correct results for me. i.e. if I run it on a monday I want that date, otherwise I want the previous mondays date. So if I run it on 30 April 2007 I want the result - 30 April 2007, if I run it on 1 May 2007 I also want 30 April 2007

You may be asking why I am doing this when Michael has produced a very good function. The simple answer is that I am using SQL server 7 and so cannot use UDF's.

Any Further comments would be greatly appreciated

thanks

steve

-----------

Don't worry head. The computer will do all the thinking from now on.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 05/11/2007 :  05:10:16  Show Profile  Reply with Quote
quote:
Originally posted by elwoos
...The simple answer is that I am using SQL server 7 and so cannot use UDF's...


You might have missed the code below from my original post where I posted an inline code solution for calculating the start of week for each day of the week. For your example, the code would be:
declare @dt datetime
set @DT = '20070430 13:0:0'
select Monday = dateadd(dd,(datediff(dd,-53690,@DT)/7)*7,-53690)



From my original post:
Edited 2005/4/9:

I thought I would also post an alternate way of doing the Start of Week instead of using the F_START_OF_WEEK function. These queries demo doing the Start of Week inline in a query, and use a similar algorithm to find the start of week, but the start day of week is hard coded.

I posted two versions. The first version is simpler, but it has a minor flaw that returns a false result if the start of week would be before 1753/1/1. For the vast majority of applications this would not be a problem. In the second, the algorithm is modified slightly to cause it to overflow if you pick a date that would result in a start of week before 1753/1/1. Note that the F_START_OF_WEEK function returns a NULL in this situation.

The demo queries use the F_TABLE_NUMBER_RANGE that is posted in another thread in order to generate dates to demonstrated the results:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685




--  First demo query for Start of Week
--  Returns bad result if the start of week would be before 1753/1/1
select
	DATE,
	Sun = dateadd(dd,(datediff(dd,-53684,a.DATE)/7)*7,-53684),
	Mon = dateadd(dd,(datediff(dd,-53690,a.DATE)/7)*7,-53690),
	Tue = dateadd(dd,(datediff(dd,-53689,a.DATE)/7)*7,-53689),
	Wed = dateadd(dd,(datediff(dd,-53688,a.DATE)/7)*7,-53688),
	Thu = dateadd(dd,(datediff(dd,-53687,a.DATE)/7)*7,-53687),
	Fri = dateadd(dd,(datediff(dd,-53686,a.DATE)/7)*7,-53686),
	Sat = dateadd(dd,(datediff(dd,-53685,a.DATE)/7)*7,-53685)
from
	(
	select
		DATE = convert(datetime,number)
	from
		F_TABLE_NUMBER_RANGE(36524,40000)
	) a

--  Second demo query for Start of Week
--  Modified to cause an error instead of returning a bad date
--  if the start of week would be before 1753/1/1
select
	DATE,
	Sun = dateadd(dd,((datediff(dd,-53684,a.DATE+7)/7)*7)-7,-53684),
	Mon = dateadd(dd,((datediff(dd,-53690,a.DATE+7)/7)*7)-7,-53690),
	Tue = dateadd(dd,((datediff(dd,-53689,a.DATE+7)/7)*7)-7,-53689),
	Wed = dateadd(dd,((datediff(dd,-53688,a.DATE+7)/7)*7)-7,-53688),
	Thu = dateadd(dd,((datediff(dd,-53687,a.DATE+7)/7)*7)-7,-53687),
	Fri = dateadd(dd,((datediff(dd,-53686,a.DATE+7)/7)*7)-7,-53686),
	Sat = dateadd(dd,((datediff(dd,-53685,a.DATE+7)/7)*7)-7,-53685)
from
	(
	select
		DATE = convert(datetime,number)
	from
		F_TABLE_NUMBER_RANGE(36524,40000)
	) a





CODO ERGO SUM
Go to Top of Page

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 05/14/2007 :  03:07:20  Show Profile  Reply with Quote
Thanks again Michael. I do recall looking at your code some time ago and thinking how useful it is now that you mention it. I suspect that yours is much more efficient though I am only dealing in a small dataset (i.e. a few hundred records) for this particular app so I think I will do some testing.

cheers

steve

-----------

Don't worry head. The computer will do all the thinking from now on.
Go to Top of Page

mrtanvirali
Starting Member

Pakistan
41 Posts

Posted - 03/05/2008 :  08:13:02  Show Profile  Reply with Quote
Hi,
i am very new to this forum, i just want to know the break time between two clockedIn times

e.g.
ClockIn Time ClockOut Time LogDate UserId
2008-02-25 10:05:13.000 2008-02-25 18:14:50.000 2008-02-25 36
2008-02-25 18:14:52.000 2008-02-25 18:15:07.000 2008-02-25 36

2008-02-21 09:54:25.000 2008-02-21 18:25:00.000 2008-02-21 36
2008-02-21 10:28:05.000 2008-02-21 17:00:00.000 2008-02-21 36

how can i calculate the break time between two clockedIn times and clockedOut time within the same day

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 03/05/2008 :  08:33:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Is this a specific question about this particular piece of SQL code?
If not, please do not post in SCRIPT LIBRARY.

Please post in appropriate forum.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

mrtanvirali
Starting Member

Pakistan
41 Posts

Posted - 03/06/2008 :  00:28:56  Show Profile  Reply with Quote

I've checked this forum and find very suitable things here so i am posting here, actually I am working on Time Trax application that manage the working and break times of employees, the folowing are the dummy clockin and clockout times, when employee clocked in his / her working time starts and when he / she clocked out then the break time starts

ClockIn Time --------------- ClockOut Time
2008-02-20 10:05:13.000, 2008-02-20 18:14:50.000

ClockIn Time --------------- ClockOut Time
2008-02-21 09:54:25.000, 2008-02-21 18:25:00.000
2008-02-21 10:28:05.000, 2008-02-21 17:00:00.000

then how would I calculate the break time between two clockedIn times and clockedOut time within the same day

Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 03/06/2008 :  00:39:16  Show Profile  Reply with Quote
Your post has nothing to do with the Start of Week function.

You should post your question on one of the TSQL or Development forums if you actually want to get an answer to your question.



CODO ERGO SUM
Go to Top of Page

mrtanvirali
Starting Member

Pakistan
41 Posts

Posted - 03/06/2008 :  01:42:18  Show Profile  Reply with Quote
thanx for reply, all of you
Go to Top of Page

sukirti
Starting Member

1 Posts

Posted - 02/18/2010 :  02:07:37  Show Profile  Reply with Quote
hi even i did the same.what i understand is that a user will enter any date & u ahve to find out the first datea & end date of the week...Then what i did is, i took aparameter dtparameter where the user will enter the date & then i created 2 other parameter start_date & end_Date where in i have given the expression in it default value
Start_Date-=Switch(DateTime.Parse(Parameters!dtparam.Value).DayOfWeek.ToString() = "Sunday", DateTime.Parse(Parameters!dtparam.Value)
,DateTime.Parse(Parameters!dtparam.Value).DayOfWeek.ToString() = "Monday", DateTime.Parse(Parameters!dtparam.Value).AddDays (-1)
, DateTime.Parse(Parameters!dtparam.Value).DayOfWeek.ToString() ="Tuesday", DateTime.Parse(Parameters!dtparam.Value).AddDays(-2) ,
DateTime.Parse(Parameters!dtparam.Value).DayOfWeek.ToString() = "Wednesday", DateTime.Parse(Parameters!dtparam.Value).AddDays(-3),
DateTime.Parse(Parameters!dtparam.Value).DayOfWeek.ToString() = "Thursday", DateTime.Parse(Parameters!dtparam.Value).AddDays(-4) ,
DateTime.Parse(Parameters!dtparam.Value).DayOfWeek.ToString() = "Friday", DateTime.Parse(Parameters!dtparam.Value).AddDays(-5) ,
DateTime.Parse(Parameters!dtparam.Value).DayOfWeek.ToString() = "Saturday", DateTime.Parse(Parameters!dtparam.Value).AddDays(-6))




End_Date:=DateTime.Parse (Parameters!Start_Date.Value).AddDays (+6)

sukirti
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 02/18/2010 :  04:40:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Which language is this?



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

angry_pirate
Starting Member

USA
1 Posts

Posted - 03/30/2010 :  15:55:01  Show Profile  Reply with Quote
Is there a way to get the F_START_OF_WEEK function to not break over year boundaries? For instance, if I am using it to set a monday as the start of the week, I'm good, except when the week contains 01/01. In that case, I want two short weeks (one in the previous year and one in the current year) so that I don't put transactions into the wrong year.

Example... Monday is my start date. This year (2010) I want transactions on 1/1, 1/2, and 1/3 to appear in 2010, but this function puts them on 12/28/2009. (As I would expect.) The date format in source can be YYYYMMDD as integer or datetime.

Any thoughts gurus?
Go to Top of Page

jbooker
Starting Member

2 Posts

Posted - 11/03/2010 :  10:05:19  Show Profile  Reply with Quote
This is a little simpler:

First day of week:
SELECT DATEADD(wk, DATEDIFF(wk, @@DATEFIRST - 1, '3/14/2005'), @@DATEFIRST -1)

Last Day of week:
SELECT DATEADD(wk, DATEDIFF(wk, @@DATEFIRST - 2, '3/14/2005'), @@DATEFIRST - 2)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/03/2010 :  13:11:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
jbooker, your code do not give consistent result!
See this very simple repro...
set dateformat mdy

set datefirst 1
SELECT DATEADD(wk, DATEDIFF(wk, @@DATEFIRST - 1, '11/3/2010'), @@DATEFIRST -1),
	DATEADD(wk, DATEDIFF(wk, @@DATEFIRST - 2, '11/3/2010'), @@DATEFIRST - 2)
set datefirst 2
SELECT DATEADD(wk, DATEDIFF(wk, @@DATEFIRST - 1, '11/3/2010'), @@DATEFIRST -1),
	DATEADD(wk, DATEDIFF(wk, @@DATEFIRST - 2, '11/3/2010'), @@DATEFIRST - 2)
set datefirst 3
SELECT DATEADD(wk, DATEDIFF(wk, @@DATEFIRST - 1, '11/3/2010'), @@DATEFIRST -1),
	DATEADD(wk, DATEDIFF(wk, @@DATEFIRST - 2, '11/3/2010'), @@DATEFIRST - 2)
set datefirst 4
SELECT DATEADD(wk, DATEDIFF(wk, @@DATEFIRST - 1, '11/3/2010'), @@DATEFIRST -1),
	DATEADD(wk, DATEDIFF(wk, @@DATEFIRST - 2, '11/3/2010'), @@DATEFIRST - 2)
set datefirst 5
SELECT DATEADD(wk, DATEDIFF(wk, @@DATEFIRST - 1, '11/3/2010'), @@DATEFIRST -1),
	DATEADD(wk, DATEDIFF(wk, @@DATEFIRST - 2, '11/3/2010'), @@DATEFIRST - 2)
set datefirst 6
SELECT DATEADD(wk, DATEDIFF(wk, @@DATEFIRST - 1, '11/3/2010'), @@DATEFIRST -1),
	DATEADD(wk, DATEDIFF(wk, @@DATEFIRST - 2, '11/3/2010'), @@DATEFIRST - 2)
set datefirst 7
SELECT DATEADD(wk, DATEDIFF(wk, @@DATEFIRST - 1, '11/3/2010'), @@DATEFIRST -1),
	DATEADD(wk, DATEDIFF(wk, @@DATEFIRST - 2, '11/3/2010'), @@DATEFIRST - 2)



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/03/2010 :  13:53:39  Show Profile  Reply with Quote
"For every problem there is a solution which is simple, clean and wrong."
Henry Louis Mencken



CODO ERGO SUM
Go to Top of Page

jbooker
Starting Member

2 Posts

Posted - 11/03/2010 :  20:20:27  Show Profile  Reply with Quote
You guys are so right, of course. I know better than to skim your tread then post untested code. I'm sorry...No excuse.

Anyway, I checked out your function and it's great, not to mention Mr. Jones' other extensive work with dates. Thanks!

Any pointers on how to return StartOfWeek from Week and Year input?

TIA,
Josh
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/04/2010 :  00:02:40  Show Profile  Reply with Quote
quote:
Originally posted by jbooker

You guys are so right, of course. I know better than to skim your tread then post untested code. I'm sorry...No excuse.

Anyway, I checked out your function and it's great, not to mention Mr. Jones' other extensive work with dates. Thanks!

Any pointers on how to return StartOfWeek from Week and Year input?

TIA,
Josh



The problem with going from Year and Week, is that you have to start with a good definition of Week (which I assume you mean week of year).

For example, there is the ISO definition of week of year, which can actually start in the prior calendar year because it starts on the Monday on or before January 4, so the first week of the year could actually start on December 28 of the prior year.

There are many other possible ways to define weeks for different organizations. Many retail organizations start the fiscal year with a nominal start date of the first of February, meaning that it actually starts on the Sunday on or before February 4.

You would be better off posting a question about this in a new topic on one of the TSQL forums, since this is getting a little off topic, and provide your Year and Week definition there.


If by chance you are using the ISO week, the following date table has the ISO week, so you could just do a lookup against that to find the min date for a particular ISO week. Other start of weeks are also available there, so it might work for other needs also.
Date Table Function F_TABLE_DATE:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519









CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/04/2010 :  01:30:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Try the first link here http://weblogs.sqlteam.com/peterl/search.aspx?q=week
http://weblogs.sqlteam.com/peterl/archive/2009/12/01/How-to-get-a-date-from-Year-week-and-weekday.aspx


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Zoumaho
Starting Member

Ecuador
5 Posts

Posted - 01/24/2011 :  14:15:31  Show Profile  Reply with Quote
-- @date = date to calculate
-- Mon = 1, Tue = 2, Wed = 3, Thu = 4, Fri = 5, Sat = 6, Sun = 7
CREATE FUNCTION [dbo].[dayOfWeek](@date DATETIME)
RETURNS smallint
AS
BEGIN
Declare @day as smallint
SET @day = datepart(dw,@date)-(8-@@DATEFIRST)
if @day < 1
return 7+@day
return @day
END

We need to exercise not only the body or mind, but every aspect of our lives and more if it needs improvement.
Go to Top of Page

Zoumaho
Starting Member

Ecuador
5 Posts

Posted - 01/24/2011 :  14:16:35  Show Profile  Reply with Quote
-- @date = date to calculate
-- @weekStartDay = week start day (1..7)
-- Mon = 1, Tue = 2, Wed = 3, Thu = 4, Fri = 5, Sat = 6, Sun = 7
ALTER FUNCTION [dbo].[firstDayOfWeek](@date DATETIME,@weekStartDay smallint = 1)
RETURNS Datetime
AS
BEGIN
Declare @diff smallint
SET @diff = dbo.dayOfWeek(@date)-@weekStartDay
if @diff<0
SET @diff = dbo.dayOfWeek(@date)-(@weekStartDay-7)
return dateadd(day, -@diff, @date)
END

We need to exercise not only the body or mind, but every aspect of our lives and more if it needs improvement.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 01/24/2011 :  15:26:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Why do people tend to make things harder and more difficult than needed?
SELECT	DATEADD(DAY, DATEDIFF(DAY, @WeekStartDay - 1, @Date) / 7 * 7, @WeekStartDay - 1)



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.28 seconds. Powered By: Snitz Forums 2000