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

Printable View

Mar 29th, 2016, 09:44 AM
sapator

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

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

ON vb it can be done like this:

Code:

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.

Re: getting from date of week to date of week

Dirty but works

VB Code:

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)

Mar 29th, 2016, 10:25 AM
techgnome

Re: getting from date of week to date of week

Except that's not in SQL....

Quote:

Originally Posted by TheOP

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

Re: getting from date of week to date of week

In SQL like this

SQL Code:

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)

Mar 29th, 2016, 12:17 PM
szlamany

Re: getting from date of week to date of week

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...)

Code:

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.

Code:

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

Code:

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
Mar 30th, 2016, 02:42 AM
sapator

Re: getting from date of week to date of week

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.
Mar 30th, 2016, 03:05 AM
sapator

Re: getting from date of week to date of week

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...
Mar 30th, 2016, 04:23 AM
sapator

Re: getting from date of week to date of week

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

Code:

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
Mar 30th, 2016, 05:00 AM
szlamany

Re: getting from date of week to date of week

Quote:

Originally Posted by sapator

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.
Mar 30th, 2016, 05:39 AM
sapator

Re: getting from date of week to date of week

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.
Mar 30th, 2016, 05:41 AM
szlamany

Re: getting from date of week to date of week

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.
Mar 30th, 2016, 05:46 AM
szlamany

Re: getting from date of week to date of week

Here it is:

Code:

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

Code:

----------------------- 2016-03-24 00:00:00.000 (1 row(s) affected) ----------------------- 2016-03-31 00:00:00.000 (1 row(s) affected)
Mar 30th, 2016, 06:08 AM
sapator

Re: getting from date of week to date of week

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

Code:

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

------

Code:

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

Code:

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.
Mar 30th, 2016, 06:10 AM
szlamany

Re: getting from date of week to date of week

You always need to do a CONVERT(varchar(10),GetDate(),101) to get rid of time before doing any + or - logic.
Mar 30th, 2016, 06:17 AM
sapator

Re: getting from date of week to date of week

OK but that will still bring wrong results.

Code:

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
Mar 30th, 2016, 06:51 AM
szlamany

Re: getting from date of week to date of week

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?
Mar 30th, 2016, 07:09 AM
sapator

Re: getting from date of week to date of week

OK, so this?

Code:

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
Mar 30th, 2016, 07:27 AM
szlamany

Re: getting from date of week to date of week

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

Is that what you want??
Mar 30th, 2016, 09:33 AM
sapator

Re: getting from date of week to date of week

Yep.