[RESOLVED] getting from date of week to date of week

[sapator]

Hello.
I'm trying to get a date of week to date of week in SQL.

ON vb it can be done like this:

  Dim dt As DateTime = DateTime.Now.AddHours(-6).[Date]
        Dim delta As Integer = DayOfWeek.Thursday - dt.DayOfWeek
        If delta > 0 Then
            delta -= 6
        End If
        Dim dtFrom As DateTime = dt.AddDays(delta)
        Dim dtTo As DateTime = dtFrom.AddDays(6)

So I'm trying to get a Thursday starting date and a + days plus on end date.
So on end date I can just add +6 on the starting date. But how can I get the Thursday day of the current week?
Thanks.

[GaryMazzone]

Dirty but works

Dim dtDate As Date = Now
        If dtDate.DayOfWeek = DayOfWeek.Thursday Then
            MsgBox("ZGot")
        Else
            MsgBox("Not")
        End If
        Do Until dtDate.DayOfWeek = DayOfWeek.Thursday
            dtDate = dtDate.AddDays(1)
        Loop
        MsgBox(dtDate.DayOfWeek)

[techgnome]

Except that's not in SQL....

I'm trying to get a date of week to date of week in SQL.

I'm not sure I'm 100% clear on what the question is...
Odds are though, it's going to involve DatePart, DateAdd and a loop of some kind.

-tg

[GaryMazzone]

In SQL like this

DECLARE @dtDate AS date = GETDATE(),
        @DName VARCHAR(20)
    
SET @DName = DATENAME(WEEKDAY,@dtDate)
 
while @DName != 'Thursday'
    BEGIN
        PRINT @dtDate
        SET @dtDate = DATEADD (d,1,@dtDate)
        SET @DName = DATENAME(WEEKDAY,@dtDate)
        PRINT @dtDate
    END
 PRINT Datename(weekday,@dtDate)

[szlamany]

No loop needed - simple formula - put it in a scalar UDF (obviously put the DATEPART value into a vbl and use it in the case statement...)

Case When DatePart(dw, @TestDate) between 5 and 7
			Then DateAdd(dd, 5 - DatePart(dw, @TestDate), @TestDate) 
			Else DateAdd(dd, -(2 + DatePart(dw, @TestDate)), @TestDate) End

Here it is being tested.

Print DatePart(dw, '2016-03-24') -- Thursday (5)
Print DatePart(dw, '2016-03-25') -- Friday (6)
Print DatePart(dw, '2016-03-26') -- Saturday (7)
Print DatePart(dw, '2016-03-27') -- Sunday (1)
Print DatePart(dw, '2016-03-28') -- Monday (2)
Print DatePart(dw, '2016-03-29') -- Tuesday (3)
Print DatePart(dw, '2016-03-30') -- Wednesday (4)
Print DatePart(dw, '2016-03-31') -- Thursday (5)

Declare @TestDate datetime
Set @TestDate='2016-03-24'

While @TestDate<'2016-04-10'
Begin
	Print Convert(varchar(10),@TestDate,101)
	+' '+Convert(varchar(10),Case When DatePart(dw, @TestDate) between 5 and 7
			Then DateAdd(dd, 5 - DatePart(dw, @TestDate), @TestDate) 
			Else DateAdd(dd, -(2 + DatePart(dw, @TestDate)), @TestDate) End,101)
	Set @TestDate=DateAdd(dd,1,@TestDate)
End

And this is what the output results look like - two columns of dates - the date of the week and the THURDAY associated with that date of the week

5
6
7
1
2
3
4
5
03/24/2016 03/24/2016
03/25/2016 03/24/2016
03/26/2016 03/24/2016
03/27/2016 03/24/2016
03/28/2016 03/24/2016
03/29/2016 03/24/2016
03/30/2016 03/24/2016
03/31/2016 03/31/2016
04/01/2016 03/31/2016
04/02/2016 03/31/2016
04/03/2016 03/31/2016
04/04/2016 03/31/2016
04/05/2016 03/31/2016
04/06/2016 03/31/2016
04/07/2016 04/07/2016
04/08/2016 04/07/2016
04/09/2016 04/07/2016

[sapator]

Hmmm.
This seems a little more complicated.
I don't know if the initial post was enlightening.
I'm actually trying to get the Thursday of the current week and add 6 so i can get Thursday to Wednesday.

[sapator]

Ok.Sorry. Ignore.
(damn).
What I'm trying is to get the Thursday BEFORE the current Thursday comes along.
So for example today we have Wednesday. I don'[t want to get the Thursday of the this week, so, not tomorrow.
What I would have to get today is 03/24 to 03/30 . Tomorrow the correct get would actually by (after 23:59 Wednesday), 03/31 to 04/06 .
Thanks.
I'm thinking of vb-ing that as I can't mix anything on SQL.Don't know maybe I am missing something...

[sapator]

I've simulated the vb function so I'm doing something like the below.
Is this vial?

Declare @dt as datetime
Declare @dtFrom as datetime
Declare @dtTo as datetime
--set @dt = CONVERT(DATETIME, getdate(), 102)
set @dt = DateAdd(Day, Datediff(Day,0, GetDate()), 0)
Declare @delta as int
set @delta = 5 -  DatePart(dw, GETDATE()) 
--select @delta
IF @delta > 0 
Begin
set @delta = @delta - 7
End
--select @dt
	set @dtFrom = DATEADD(day,@delta,@dt)
	set @dtTo = DATEADD(day,6,@dtFrom)
--	select @dtFrom

[szlamany]

What I would have to get today is 03/24 to 03/30 . Tomorrow the correct get would actually by (after 23:59 Wednesday), 03/31 to 04/06 .

Did you not see my two columns of dates - where I showed 03/24 to 03/30 with 03/24 showing next to each of the dates in that week?

It's exactly what you asked for - and done quite often in financial systems (I've got a client with an every-TUESDAY selectmen meeting where they sign off on this weeks invoice warrants.

[sapator]

Hi.
Yes , show it but i got confused on the between part so I just borrowed some stuff from your function but created one more readable to me.
I'm testing it right now but I just want a feedback if someone sees and error - issue , I might be missing.
Thanks.

[szlamany]

It's just that single formula in the CASE statement.

The other code was a loop to test it in a query window - that might have looked like crap but not part of what you would use in production.

Just the CASE statement - in a UDF - would make sense to me.

You are doing heavy lifting adding days to a date (repeatedly) when the NUMBER of the date in the week was the only key you needed to adjust your date.

[szlamany]

Here it is:

Create Function GiveMeThursday_F(@SomeDate datetime)
Returns datetime
as
Begin
	Declare @dw int
	Set @dw=DatePart(dw,@SomeDate)
	Return Case When @dw between 5 and 7
			Then DateAdd(dd, 5 - @dw, @SomeDate) 
			Else DateAdd(dd, -(2 + @dw), @SomeDate) End
End
Select dbo.GiveMeThursday_F('03-30-2016')
Select dbo.GiveMeThursday_F('03-31-2016')

and that returns

-----------------------
2016-03-24 00:00:00.000

(1 row(s) affected)


-----------------------
2016-03-31 00:00:00.000

(1 row(s) affected)

[sapator]

OK.
I misunderstood.
So in my case this?
Quoting:

Case When DatePart(dw, @TestDate) between 5 and 7
			Then DateAdd(dd, 5 - DatePart(dw, @TestDate), @TestDate) 
			Else DateAdd(dd, -(2 + DatePart(dw, @TestDate)), @TestDate) End

------

declare @dtFrom datetime
declare @dtTo datetime
set  @dtFrom = DateAdd(Day, Datediff(Day,0, GetDate()), 0)

select Case When DatePart(dw, @dtFrom) between 5 and 7
			Then DateAdd(dd, 5 - DatePart(dw, @dtFrom), @dtFrom) 
			Else DateAdd(dd, -(2 + DatePart(dw, @dtFrom)), @dtFrom) 
end

set @dtTo = DATEADD(day,7,@dtFrom)
select @dtTo

But there is an issue here:
I'm needing the zero time values....
so

set  @dtFrom = DateAdd(Day, Datediff(Day,0, GetDate()), 0)

The problem here is that if i run it with getdate(), it's ok.
If however I add a day ( getdate()+1 ) or so, then from date is wrong.

[szlamany]

You always need to do a CONVERT(varchar(10),GetDate(),101) to get rid of time before doing any + or - logic.

[sapator]

OK but that will still bring wrong results.

declare @dtFrom datetime
declare @dtTo datetime
set  @dtFrom = CONVERT(varchar(10),GetDate()+1,101) 

select Case When DatePart(dw, @dtFrom) between 5 and 7
			Then DateAdd(dd, 5 - DatePart(dw, @dtFrom), @dtFrom) 
			Else DateAdd(dd, -(2 + DatePart(dw, @dtFrom)), @dtFrom) 
end

set @dtTo = DATEADD(day,7,@dtFrom)
select @dtTo

I'm expecting 04-06 and I get 04-07

[szlamany]

You are adding 1 in the CONVERT?

Then you are adding 7?

You are doing the math - make something 6.

Did you not see my two line UDF?

[sapator]

OK, so this?

declare @dtFrom datetime
declare @dtTo datetime
set  @dtFrom = CONVERT(varchar(10),GetDate()+10,101) 

set  @dtFrom =  Case When DatePart(dw, @dtFrom) between 5 and 7
			Then DateAdd(dd, 5 - DatePart(dw, @dtFrom), @dtFrom) 
			Else DateAdd(dd, -(2 + DatePart(dw, @dtFrom)), @dtFrom) 
end

set @dtTo = DATEADD(day,6,@dtFrom)
select @dtFrom
select @dtTo

getdate does not matter, I'm just simulating a date in the future

[szlamany]

That is giving you a Thursday to Wednesday run of dates.

Is that what you want??

[sapator]

Yep.