obtain data from 2 weekdays?

Question

obtain data from 2 weekdays?

mick burden

SSCarpal Tunnel

Points: 4432
More actions
July 5, 2008 at 6:34 am

#381510

is it possible to write a storeprocedure in a way that when you pass a date in it returns the current week i.e Monday to Friday?
I was thinking about loops or cursors but I couldn't seem to work it out
something like
select * where [date] between @monday and @friday
@monday being mondays date of the current week, I think what I'm after is a way to work out mondays date and make @friday mondays date plus 4

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

This might do what you are asking: It uses the current date to determine the prior Monday. When tested today (Saturday July 5th the results were:

Monday Friday

--------------------------- ------------------------------------

2008-06-30 11:05:08.107 2008-07-04 11:05:08.107

DECLARE @Day AS INT

DECLARE @monday AS DATETIME

DECLARE @friday AS DATETIME

SET @Day = (SELECT DATEPART(day, GETDATE()) )

SET @monday = (SELECT DATEADD ( dd , -@Day, GETDATE()))

SET @friday = (SELECT DATEADD (dd,4,@Monday))

SELECT @monday AS 'Monday', @friday 'AS Friday' -- Only used during testing to verify result.

A caveat be sure the first day of the week is set to be Sunday.

From BOL

Returns the current value of the SET DATEFIRST parameter, which indicates the specified first day of each week: 1 for Monday, 3 for Wednesday, and so on through 7 for Sunday.

This example sets the first day of the week to 5 (Friday)
SET DATEFIRST 5

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

mick burden SSCarpal Tunnel Points: 4432 More actions · Answer 2

mick burden

SSCarpal Tunnel

Points: 4432

July 5, 2008 at 9:30 am

#838508

That's great bitbucket, many thanks

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

FORGET THE SOLUTION PRESENTED WHEN THE DAY OF THE MONTH BECOMES A DOUBLE DIGIT for example July 15th THE T-SQL POSTED ABOVE DOES NOT WORK PROPERLY

I should have test more .. before presenting this as a solution

SORRY

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

Think I have it now:

DECLARE @SomeDate AS DATETIME

DECLARE @Day AS INT

DECLARE @monday AS DATETIME

DECLARE @friday AS DATETIME

SET @SomeDate = 'date in week desired '

SET @Day = (SELECT DATEPART(weekday, @SomeDate)-1)

SET @monday = (SELECT DATEADD ( dd , -(@Day - 1), @SomeDate))

SET @friday = (SELECT DATEADD (dd,4,@Monday))

--SELECT @monday AS 'Monday', @friday 'AS Friday', @Somedate AS 'some date' use only for testing

Please test extensively -

Sorry for my goof in first reply to you viloated my own principle of test, test, then test again and then test some more.

Watched too many fireworks displays last night

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Ooooohhh! Be real careful... unless all of your dates have a midnight time, the following will miss all but the first millisecond of the date carried by @friday...

select * where [date] between @monday and @friday

You should almost never use BETWEEN because of the problem I just mentioned.

If you want to ensure that all of Friday is covered for times, then you must NOT look for Friday... you must look for everything that is less than Saturday... like this...

SELECT *

FROM yourtable

WHERE [Date] >= @monday

AND [Date] < @Saturday

So far as code goes, BitBucket was on the right track... the problem is that his code includes times... because of that, unless the current time is precisely at midnight, the code will miss rows in the Select because of the times.

So, with that in mind and without having to worry about DateFirst and all that, consider this... what day of the week is day zero? And, if you add 5 days to that, what day of the week is that? If you don't know what day zero is, try this and see...

--===== Show the date of day "zero"

SELECT CAST(0 AS DATETIME)

--===== Show the day of the week for day "zero"

SELECT DATENAME(dw,CAST(0 AS DATETIME))

--===== Show the day of the week for 5 days after

-- day "zero"

SELECT DATENAME(dw,CAST(0 AS DATETIME)+5)

You with me so far?

Shifting gears a bit... what is the very beginning of today? We need to know the very beginning because we don't want to miss any times for today... here's one very easy way to remember to do that... convert today to a number of days and then back to a date. It's the second fastest method to do such a thing and super easy to remember... Do notice that we use day zero. We're actually figuring out the number of days since day zero and then converting back to a date...

--===== Show the beginning of today...

SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

Side bar: The first date in SQL Server is '1753-01-01' and many people use that instead of day zero. It also happens to be the same day of the week as day zero.

Ok... we know how to find the first Monday in SQL... we know how to find the first Saturday in SQL (5 days after the first Monday)... we know how to find differences between dates... we know how to convert dates to be midnight... and, we know that we can add and subtract days in dates. Let's put it all together... the following will select everything in some table that has a date that occurred on Monday through Friday of LAST week...

DECLARE @Now DATETIME

SET @Now = '2008-07-06'

DECLARE @Day INT,

@monday DATETIME,

@Saturday DATETIME

SELECT @Day = DATEDIFF(dd,0,@Now)%7,

@monday = DATEADD(dd,DATEDIFF(dd,0,@Now-@Day),0)-7,

@Saturday = DATEADD(dd,DATEDIFF(dd,0,@Now-@Day),0)-7+5

--===== Just for demo...

SELECT @Day,@Monday,@Saturday

--===== Use the week range to select...

SELECT *

FROM sometable

WHERE somedate >= @monday

AND somedate < @Saturday

There're several other ways to do the same thing... and, yes, I know that -7+5 = -2... I'm just going for max clarity and good performance while maintaining accuracy.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

Jeff ran your codea and got the following:

@Day @monday @Saturday

62008-06-23 00:00:00.0002008-06-28 00:00:00.000

Which is the last full week in June, I am wondering if the user really wants the first week of July. If he/she does then he/she has to input the date as a date in the week following the desired weeks data.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

bitbucket (7/5/2008)
Jeff ran your codea and got the following:
@Day @monday @Saturday
62008-06-23 00:00:00.0002008-06-28 00:00:00.000
Which is the last full week in June, I am wondering if the user really wants the first week of July. If he/she does then he/she has to input the date as a date in the week following the desired weeks data.

First week of July isn't over yet... weekdays are, but the first week isn't.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mick burden SSCarpal Tunnel Points: 4432 More actions · Answer 8

Thanks lads for the info, I've actually used the SQL below, the only problem is if I set the date to today, I get last week's data.

DECLARE @Now as DATETIME

SET @Now = '08/07/2008' -- uk format dd/mm/yyyy

DECLARE @Day as INT

DECLARE @monday as DATETIME

DECLARE @Saturday as DATETIME

SELECT @Day = DATEDIFF(dd,0,@Now)%7

set @monday = DATEADD(dd,DATEDIFF(dd,0,@Now-@Day),0)-7

set @Saturday = DATEADD(dd,DATEDIFF(dd,0,@Now-@Day),0)-7+5

select * from [Resource] where [date] >= @monday and [date] < @Saturday order by [date]

GO

I'm using the United Kingdoms datetime format. if I use

set @Now = getdate()

I still get last week, but if I use

set @Now = getdate() +7

then I get the current week

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

mick burden (7/7/2008)
Thanks lads for the info, I've actually used the SQL below, the only problem is if I set the date to today, I get last week's data.
DECLARE @Now as DATETIME
SET @Now = '08/07/2008' -- uk format dd/mm/yyyy
DECLARE @Day as INT
DECLARE @monday as DATETIME
DECLARE @Saturday as DATETIME
SELECT @Day = DATEDIFF(dd,0,@Now)%7
set @monday = DATEADD(dd,DATEDIFF(dd,0,@Now-@Day),0)-7
set @Saturday = DATEADD(dd,DATEDIFF(dd,0,@Now-@Day),0)-7+5
select * from [Resource] where [date] >= @monday and [date] < @Saturday order by [date]
GO
I'm using the United Kingdoms datetime format. if I use
set @Now = getdate()
I still get last week, but if I use
set @Now = getdate() +7
then I get the current week

I don't know why I thought you wanted last week... just delete the two (-7)'s from the formulas for Monday and Friday...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mick burden SSCarpal Tunnel Points: 4432 More actions · Answer 10

Thanks Jeff, I don't know why I didn't spot that myself... and thanks also to bitbucket

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

No problem, Mick... It's easy to miss stuff especially when it's new.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)