find @mon and @fri based on date

Question

Post reply

find @mon and @fri based on date

Lowell

SSC Guru

Points: 323518
More actions
December 5, 2008 at 7:46 am

#154990

ok yeah i have a complete solution here.....but it's a case statement; I'm drawing a blank here, but i swear there was an easier way to do this.
basically it is two date parameters auto generated...if today is wednesday, monday was two days ago, and friday is two days int he future.
if today is Sat, monday was 5 days before, and friday was one day before.
the below code works fine, but seems too wordy; i seem to remember a slicker way to do this; anyone have a better solution?:
[font="Courier New"]
SET DECLARE @mon DATETIME,
        @fri DATETIME
--set @mon = '12/01/2008 00:00:00.000'
--set @fri = '12/05/2008 23:59:59.999'
--get day of week of today? 1=monday, 5 = friday
SELECT DAY(GETDATE())
SELECT CONVERT(VARCHAR,GETDATE() - 4,101)
SELECT DATEADD(d, 0 ,CONVERT(VARCHAR,GETDATE(),101))
SET @mon = CASE
             WHEN DAY(GETDATE()) = 1 THEN DATEADD(d, 0 ,CONVERT(VARCHAR,GETDATE(),101)) --mm/dd/yyyy no time
             WHEN DAY(GETDATE()) = 2 THEN DATEADD(d, 0 ,CONVERT(VARCHAR,GETDATE() - 1,101)) --mm/dd/yyyy no time
             WHEN DAY(GETDATE()) = 3 THEN DATEADD(d, 0 ,CONVERT(VARCHAR,GETDATE() - 2,101)) --mm/dd/yyyy no time
             WHEN DAY(GETDATE()) = 4 THEN DATEADD(d, 0 ,CONVERT(VARCHAR,GETDATE() - 3,101)) --mm/dd/yyyy no time
             WHEN DAY(GETDATE()) = 5 THEN DATEADD(d, 0 ,CONVERT(VARCHAR,GETDATE() - 4,101)) --mm/dd/yyyy no time
             WHEN DAY(GETDATE()) = 6 THEN DATEADD(d, 0 ,CONVERT(VARCHAR,GETDATE() - 5,101)) --mm/dd/yyyy no time
             WHEN DAY(GETDATE()) = 7 THEN DATEADD(d, 0 ,CONVERT(VARCHAR,GETDATE() - 6,101)) --mm/dd/yyyy no time
           END
SELECT @mon
SET @fri = DATEADD(dd,5,@mon)
SET @fri = DATEADD (ms,-3,@fri) --12/06/2008 - 1 millisecond? trying for 12/05/2008 23:59:59.999'
SELECT @fri[/font]
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Just had to do one of these this morning actually.

SET DATEFIRST 1

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) Monday,

DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)+4 Friday

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Garadin One Orange Chip Points: 29613 More actions · Answer 2

Just saw the end part about wanting the EOD. This EOD friday seems a bit sloppy and could likely be improved upon.

[font="Courier New"]SET DATEFIRST 1

SELECT

DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) Monday,

DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)+4 Friday,

DATEADD(ms,-2,DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)+ 5 ) EODFriday[/font]

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

For an exhaustive discussion on a similar topic (Finding the next Sunday, regardless of datefirst setting) take a look here:

http://www.sqlservercentral.com/Forums/Topic606669-338-1.aspx

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Lowell SSC Guru Points: 323518 More actions · Answer 4

you know, I've used the same technique to get the date with the time at midnight.... SELECT DATEADD(d, DATEDIFF(d,0,GETDATE()), 0) a million times. I guess i need the upcoming weekend, because i couldn't infer how to get the first day of the week/month/year etc fromt he same formula.

thank you!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 5

Lowell:

I use the function below (created in our global db):

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create function [dbo].[fGetDayInWeek]( @date smalldatetime, @dayOfWeek tinyint )

returns smalldatetime

as begin

return dateadd( day, @dayOfWeek - datepart( weekday, @date ), @date )end

go

grant exec on dbo.fGetDayInWeek to public

go

usage:

select getdate(),

global.dbo.fGetDayInWeek( getdate(), 2 ) as monday,

global.dbo.fGetDayInWeek( getdate(), 6 ) as friday