September 12, 2006 at 5:21 am
Hi Guys,
I am trying to write a query which returns all the records within a calendar week of a specified date. I can query out the records in a rolling week no problem but am finding it more difficult to do this with a calendar weeks..
For example if my table is:
Table:Reservations
[Meeting Start] datetime
[Meeting End] datetime
[MeetingTitle] nvarchar(100)
and my test date is 12/09/2006
I want to return all the records in the same calendar week as 12/09/2006
Can anyone tell me how I should go about this?
Any help is as always greatly appreciated.
Cheers,
Chris
September 12, 2006 at 6:06 am
how about :
select [Meeting Start]
,[Meeting End]
,[MeetingTitle]
from Reservations
where [Meeting Start] between dateadd(dd, (-datepart(dw,@yourdt) + 1), @yourdt)
and dateadd(dd, ((8)-datepart(dw,@yourdt) - 1), @yourdt)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 12, 2006 at 6:47 am
Thanks thats exactly what I need... 🙂
Chris
September 12, 2006 at 8:36 am
Just an FYI, keep in mind about what SQL considers a week. You can do that by SELECT @@DATEFIRST. Default is Sunday so if your week starts on Monday then you have to set the datefirst attribute for your connection and reset it back to way it was.
Thanks
Sreejith
September 13, 2006 at 3:22 am
Sreejith had a very good pointer!
I would add that your DSS table Dates would serve you well (yet again!)
September 13, 2006 at 3:33 am
maybe you can consider using this function :
use master
if exists (SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE=N'FUNCTION'
AND ROUTINE_SCHEMA=N'dbo'
AND ROUTINE_NAME = N'fn_ALZDBA_convert_date2WeekRange' )
drop function [dbo].[fn_ALZDBA_convert_date2WeekRange]
GO
CREATE FUNCTION dbo.fn_ALZDBA_convert_date2WeekRange (@RefDate datetime, @FirstLast char(1) = 'F')
RETURNS datetime
AS
BEGIN
-- 04 januari determines week 1 !
-- Our first day of the week is Monday !
DECLARE @return_date as datetime
declare @Firstdate datetime
declare @WrkDate datetime
declare @refDays int
select @WrkDate = cast(convert(char(10), @RefDate, 121) as datetime)
select @refDays = (datepart(dw,@WrkDate) - 1) * (-1)
select @Firstdate = dateadd( dd, @refDays, @WrkDate)
if @@DATEFIRST = 7 -- if datefirst =sunday (default) add 1 day
begin
select @Firstdate = dateadd(dd, 1,@Firstdate)
end
if @RefDate < @Firstdate
begin
select @Firstdate = dateadd(dd, (-7) ,@Firstdate)
end
if @FirstLast = 'F'
begin
set @return_date = @Firstdate
end
else
begin
select @return_date = dateadd(ms,-2,dateadd(dd,7,@Firstdate))
end
RETURN (@return_date)
END
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 13, 2006 at 6:27 am
Thanks for all your help guys... Thanks to your help I have built a solution which I am currently testing .. I'll give you a shout if i run into any more problems.
Thanks again.
Chris
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply