| Author |
 Topic  |
|
elwoos
Flowing Fount of Yak Knowledge
United Kingdom
2039 Posts |
 Posted - 05/11/2007 : 04:00:56
|
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. |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 05/11/2007 : 05:10:16
|
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 |
|
|
|
elwoos
Flowing Fount of Yak Knowledge
United Kingdom
2039 Posts |
Posted - 05/14/2007 : 03:07:20
|
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. |
|
|
|
mrtanvirali
Starting Member
Pakistan
41 Posts |
Posted - 03/05/2008 : 08:13:02
|
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
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 03/05/2008 : 08:33:34
|
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" |
|
|
|
mrtanvirali
Starting Member
Pakistan
41 Posts |
Posted - 03/06/2008 : 00:28:56
|
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
|
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 03/06/2008 : 00:39:16
|
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 |
|
|
|
mrtanvirali
Starting Member
Pakistan
41 Posts |
Posted - 03/06/2008 : 01:42:18
|
| thanx for reply, all of you |
|
|
|
sukirti
Starting Member
1 Posts |
Posted - 02/18/2010 : 02:07:37
|
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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 02/18/2010 : 04:40:41
|
Which language is this?
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
angry_pirate
Starting Member
USA
1 Posts |
Posted - 03/30/2010 : 15:55:01
|
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? |
|
|
|
jbooker
Starting Member
2 Posts |
Posted - 11/03/2010 : 10:05:19
|
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) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/03/2010 : 13:11:47
|
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" |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 11/03/2010 : 13:53:39
|
"For every problem there is a solution which is simple, clean and wrong."
Henry Louis Mencken
CODO ERGO SUM |
|
|
|
jbooker
Starting Member
2 Posts |
Posted - 11/03/2010 : 20:20:27
|
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 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 11/04/2010 : 00:02:40
|
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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
|
|
Zoumaho
Starting Member
Ecuador
5 Posts |
Posted - 01/24/2011 : 14:15:31
|
-- @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. |
|
|
|
Zoumaho
Starting Member
Ecuador
5 Posts |
Posted - 01/24/2011 : 14:16:35
|
-- @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. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/24/2011 : 15:26:04
|
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" |
|
|
|
Topic |
|
|
|
Start of Week Function
