February 11, 2019 at 12:42 pm
I need the the start date if the week given a date and which day starts a week. Here is an example:
Current Date: 02/07/2019
Week Starts: Tuesday (2)
Week Starts Date: 02/05/2019
My goal is to have a T-SQL statement that I can pass the Current Date and Week Starts parameters in and it returns Week Starts Date.
Thanks in advance for the help!
February 11, 2019 at 1:08 pm
SQL Espo - Monday, February 11, 2019 12:42 PMI need the the start date if the week given a date and which day starts a week. Here is an example:Current Date: 02/07/2019
Week Starts: Tuesday (2)
Week Starts Date: 02/05/2019My goal is to have a T-SQL statement that I can pass the Current Date and Week Starts parameters in and it returns Week Starts Date.
Thanks in advance for the help!
Huh? Weeks usually start on a Sunday. 2/7/2019 was a Thursday, so the date that the week started on was 2/3/2017
Are you really looking for the 7 day period that a date falls into, given the data and the first day of the 7 day period?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 11, 2019 at 1:14 pm
No. I am working on a system where the client wants a group of users to choose when their week starts....I cant good into super detail due to and NDA. For example, a user may want his week to start on a Tuesday and another might want it to begin on Thursday. The week itself would still run for 7 days. From my example
Current Date: 02/07/2019
Week Starts: Tuesday (2)
Week Starts Date: 02/05/2019
Week End Date: 02/12/2019
February 11, 2019 at 1:17 pm
SQL Espo - Monday, February 11, 2019 1:14 PMNo. I am working on a system where the client wants a group of users to choose when their week starts....I cant good into super detail due to and NDA. For example, a user may want his week to start on a Tuesday and another might want it to begin on Thursday. The week itself would still run for 7 days. From my exampleCurrent Date: 02/07/2019
Week Starts: Tuesday (2)
Week Starts Date: 02/05/2019
Week End Date: 02/12/2019
This is throwing me.
Week Starts: Tuesday (2)
What does the (2) represent? This is not consistent with the SQL built in date time functions.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 11, 2019 at 1:25 pm
Michael L John - Monday, February 11, 2019 1:17 PMSQL Espo - Monday, February 11, 2019 1:14 PMNo. I am working on a system where the client wants a group of users to choose when their week starts....I cant good into super detail due to and NDA. For example, a user may want his week to start on a Tuesday and another might want it to begin on Thursday. The week itself would still run for 7 days. From my exampleCurrent Date: 02/07/2019
Week Starts: Tuesday (2)
Week Starts Date: 02/05/2019
Week End Date: 02/12/2019This is throwing me.
Week Starts: Tuesday (2)What does the (2) represent? This is not consistent with the SQL built in date time functions.
February 11, 2019 at 1:38 pm
You could do something like this:
DECLARE @Week_Start AS Int
DECLARE @Current_Date AS Date
DECLARE @Week_Start_Date AS Date
DECLARE @Day_Diff Int
SET @Week_Start = 3
SET @Current_Date = GetDate()
SET @Day_Diff = DatePart(WeekDay, @Current_Date) - @Week_Start
If @Day_Diff < 0 SET @Day_Diff = @Day_Diff + 7
SET @Week_Start_Date = DateAdd(day, -@Day_Diff, @Current_Date)
SELECT
@Week_Start AS Week_Start,
@Current_Date AS [Current_Date],
DatePart(WeekDay, @Current_Date) AS Current_WeekDay,
@Day_Diff AS Day_Diff,
@Week_Start_Date AS Week_Start_Date
That determines the weekday number for the given date, determines the difference between that and the weekday start number specified, Adjusts by a week if the diff is negative, then adds the result back to the original date to get the week start date.
February 11, 2019 at 1:42 pm
Here are the formulas to get the values you want. It's basically adding weeks to known dates. I'm using days instead of weeks to be able to add or substract days in the same operation. The -1 is to adjust the date as shown on the second query, and the +6 is simply -1+7.
DECLARE @CurrentDate DATE = '20190207',
@WeekStarts INT = 2;
SELECT DATEADD( DD, DATEDIFF(DD, @WeekStarts, @CurrentDate)/7*7-1, @WeekStarts),
DATEADD( DD, DATEDIFF(DD, @WeekStarts, @CurrentDate)/7*7+6, @WeekStarts);
SELECT WeekStarts,
CONVERT( datetime, WeekStarts) AS IntegerDate,
DATENAME(DW, WeekStarts) AS ActualDay,
DATENAME(DW, WeekStarts-1) AS AdjustedDay
FROM (VALUES(1), (2), (3), (4), (5), (6), (7))x(WeekStarts);
February 11, 2019 at 1:43 pm
SQL Espo - Monday, February 11, 2019 1:25 PMMichael L John - Monday, February 11, 2019 1:17 PMSQL Espo - Monday, February 11, 2019 1:14 PMNo. I am working on a system where the client wants a group of users to choose when their week starts....I cant good into super detail due to and NDA. For example, a user may want his week to start on a Tuesday and another might want it to begin on Thursday. The week itself would still run for 7 days. From my exampleCurrent Date: 02/07/2019
Week Starts: Tuesday (2)
Week Starts Date: 02/05/2019
Week End Date: 02/12/2019This is throwing me.
Week Starts: Tuesday (2)What does the (2) represent? This is not consistent with the SQL built in date time functions.
This may get you started, although I think I would create a date calendar table.
DECLARE @CurrentDate Datetime = '02/07/2019'
DECLARE @WeekStarts smallint = 2
DECLARE @DaysBack smallint
IF @WeekStarts NOT IN (1, 2, 3, 4, 5, 6, 7) Begin
PRINT 'Error' --RAISEERROR
End
Else Begin
SET DATEFIRST @WeekStarts
SET @DaysBack = (SELECT DATEPART(weekday, @CurrentDate) * -1) + 1
SELECT DATEADD(day, @DaysBack, @CurrentDate)
End
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply