Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Date from DayName, week start & End dates

Author  Topic 

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-08 : 03:01:26
[code]Dayname WeekStartDate WekkEndDate
Thursday 2013-08-05 2013-08-11 [/code]

For the above data I want Today's date. I can able do this by using CTE...
Condition: I need solution with single statement...



--
Chandu

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-08 : 04:08:28
Do you mean something like this?
CREATE TABLE #tmp(Dayname varchar(32),WeekstartDate DATE, weekEndDate DATE);
INSERT INTO #tmp VALUES ('Thursday','20130805','20130811');
INSERT INTO #tmp VALUES ('Saturday','20130805','20130811');

;WITH CTE AS
(
SELECT 0 AS n
UNION ALL
SELECT n+1 FROM CTE WHERE n < 6
)

SELECT
t.*,DATEADD(DAY,n,WeekstartDate) As ComputedDate
FROM
CTE c
INNER JOIN #TMP t ON
DATEADD(DAY,c.N,t.WeekstartDate) <= t.weekEndDate
WHERE
DATENAME(WEEKDAY,DATEADD(DAY,c.n,t.WeekstartDate)) = t.Dayname;

DROP TABLE #tmp;
Couple of things to note:

a) This is sensitive to language setting. So if you had Donnerstag instead of Thursday, this will fail miserably.
b) You don't need the CTE if you have a numbers table or calendar table in your database.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-08 : 04:56:48
I don't have calendar table...
Yes I too did as above query... Just I would like to have simple solution if possible.
Some one can suggest me other solutions...
However thank you so much for spending precious time

--
Chandu
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-08 : 07:28:57
Try this
SELECT	DATENAME(WEEKDAY, GETDATE()) AS DayName,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000101') AS WeekStartDate,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000107') AS WeekEndDate;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

LinksUp
Starting Member

1 Post

Posted - 2013-08-08 : 19:32:48
SwePeso,

Here is a variation of your solution that should work:


SELECT DATENAME(WEEKDAY, GETDATE()) AS DayName,
DATEADD(dd, 1 - DATEPART(dw, GETDATE()), GETDATE()) AS WeekStartDate,
DATEADD(dd, 7 - DATEPART(dw, GETDATE()), GETDATE()) AS WeekEndDate;
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-08 : 20:48:18
[CODE]

CREATE TABLE #tmp1([Dayname] varchar(32),WeekstartDate DATE, weekEndDate DATE);
INSERT INTO #tmp1 VALUES ('Thursday','20130805','20130811');
INSERT INTO #tmp1 VALUES ('Monday','20130812','20130818');


SELECT DATEADD(dd, number, WeekstartDate) FROM #Tmp1, spt_values
where type = 'p' and (number between 0 and 6) and
DATENAME(WEEKDAY, DATEADD(dd, number, WeekstartDate)) = [Dayname] ;


DROP TABLE #tmp1;
[/CODE]
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-09 : 01:55:53
quote:
Originally posted by LinksUp

SwePeso,

Here is a variation of your solution that should work:


SELECT DATENAME(WEEKDAY, GETDATE()) AS DayName,
DATEADD(dd, 1 - DATEPART(dw, GETDATE()), GETDATE()) AS WeekStartDate,
DATEADD(dd, 7 - DATEPART(dw, GETDATE()), GETDATE()) AS WeekEndDate;



It doesn't work for me, but that is probably because we have a different SET DATEFIRST setting.
And besides that little problem, you also return the time portion which OP wrote nothing about.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-09 : 02:06:54
Here is a simple repro of the problem with using WEEK in SQL Server.
SET DATEFIRST 1;

SELECT DATEADD(dd, 1 - DATEPART(dw, GETDATE()), GETDATE()) AS [WeekStartDate by LinksUp],
DATEADD(dd, 7 - DATEPART(dw, GETDATE()), GETDATE()) AS [WeekEndDate by LinksUp],
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000101') AS [WeekStartDate by SwePeso],
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000107') AS [WeekEndDate by SwePeso];

SET DATEFIRST 2;

SELECT DATEADD(dd, 1 - DATEPART(dw, GETDATE()), GETDATE()) AS [WeekStartDate by LinksUp],
DATEADD(dd, 7 - DATEPART(dw, GETDATE()), GETDATE()) AS [WeekEndDate by LinksUp],
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000101') AS [WeekStartDate by SwePeso],
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000107') AS [WeekEndDate by SwePeso];

SET DATEFIRST 3;

SELECT DATEADD(dd, 1 - DATEPART(dw, GETDATE()), GETDATE()) AS [WeekStartDate by LinksUp],
DATEADD(dd, 7 - DATEPART(dw, GETDATE()), GETDATE()) AS [WeekEndDate by LinksUp],
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000101') AS [WeekStartDate by SwePeso],
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000107') AS [WeekEndDate by SwePeso];

SET DATEFIRST 4;

SELECT DATEADD(dd, 1 - DATEPART(dw, GETDATE()), GETDATE()) AS [WeekStartDate by LinksUp],
DATEADD(dd, 7 - DATEPART(dw, GETDATE()), GETDATE()) AS [WeekEndDate by LinksUp],
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000101') AS [WeekStartDate by SwePeso],
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000107') AS [WeekEndDate by SwePeso];

SET DATEFIRST 5;

SELECT DATEADD(dd, 1 - DATEPART(dw, GETDATE()), GETDATE()) AS [WeekStartDate by LinksUp],
DATEADD(dd, 7 - DATEPART(dw, GETDATE()), GETDATE()) AS [WeekEndDate by LinksUp],
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000101') AS [WeekStartDate by SwePeso],
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000107') AS [WeekEndDate by SwePeso];

SET DATEFIRST 6;

SELECT DATEADD(dd, 1 - DATEPART(dw, GETDATE()), GETDATE()) AS [WeekStartDate by LinksUp],
DATEADD(dd, 7 - DATEPART(dw, GETDATE()), GETDATE()) AS [WeekEndDate by LinksUp],
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000101') AS [WeekStartDate by SwePeso],
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000107') AS [WeekEndDate by SwePeso];

SET DATEFIRST 7;

SELECT DATEADD(dd, 1 - DATEPART(dw, GETDATE()), GETDATE()) AS [WeekStartDate by LinksUp],
DATEADD(dd, 7 - DATEPART(dw, GETDATE()), GETDATE()) AS [WeekEndDate by LinksUp],
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000101') AS [WeekStartDate by SwePeso],
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000107') AS [WeekEndDate by SwePeso];



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-09 : 08:31:39
quote:
Originally posted by SwePeso

Try this
SELECT	DATENAME(WEEKDAY, GETDATE()) AS DayName,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000101') AS WeekStartDate,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000107') AS WeekEndDate;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

I got the impression from the original post that the problem he was trying to solve is the opposite - i.e., given an arbitrary seven day period defined by WeekStartDate and WeekEndDate, and a Dayname, find the date corresponding to that Dayname.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-09 : 08:52:12
[code]DECLARE @Sample TABLE
(
DayName VARCHAR(10) NOT NULL,
WeekStartDate DATE NOT NULL,
WeekEndDate DATE NOT NULL
);

INSERT @Sample
(
DayName,
WeekStartDate,
WeekEndDate
)
VALUES ('Thursday', '20130805', '20130811');

-- SwePeso
SELECT DayName,
WeekStartDate,
WeekEndDate,
CASE DayName
WHEN 'Monday' THEN DATEADD(DAY, 0, WeekStartDate)
WHEN 'Tuesday' THEN DATEADD(DAY, 1, WeekStartDate)
WHEN 'Wednesday' THEN DATEADD(DAY, 2, WeekStartDate)
WHEN 'Thursday' THEN DATEADD(DAY, 3, WeekStartDate)
WHEN 'Friday' THEN DATEADD(DAY, 4, WeekStartDate)
WHEN 'Saturday' THEN DATEADD(DAY, 5, WeekStartDate)
WHEN 'Sunday' THEN DATEADD(DAY, 6, WeekStartDate)
ELSE NULL
END AS WantedDate
FROM @Sample;[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-12 : 00:27:59
quote:
Originally posted by SwePeso

DECLARE	@Sample TABLE
(
DayName VARCHAR(10) NOT NULL,
WeekStartDate DATE NOT NULL,
WeekEndDate DATE NOT NULL
);

INSERT @Sample
(
DayName,
WeekStartDate,
WeekEndDate
)
VALUES ('Thursday', '20130805', '20130811');

-- SwePeso
SELECT DayName,
WeekStartDate,
WeekEndDate,
CASE DayName
WHEN 'Monday' THEN DATEADD(DAY, 0, WeekStartDate)
WHEN 'Tuesday' THEN DATEADD(DAY, 1, WeekStartDate)
WHEN 'Wednesday' THEN DATEADD(DAY, 2, WeekStartDate)
WHEN 'Thursday' THEN DATEADD(DAY, 3, WeekStartDate)
WHEN 'Friday' THEN DATEADD(DAY, 4, WeekStartDate)
WHEN 'Saturday' THEN DATEADD(DAY, 5, WeekStartDate)
WHEN 'Sunday' THEN DATEADD(DAY, 6, WeekStartDate)
ELSE NULL
END AS WantedDate
FROM @Sample;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA


This solution is looking good. thank you

--
Chandu
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-12 : 00:30:47
quote:
Originally posted by James K

quote:
Originally posted by SwePeso

Try this
SELECT	DATENAME(WEEKDAY, GETDATE()) AS DayName,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000101') AS WeekStartDate,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000107') AS WeekEndDate;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

I got the impression from the original post that the problem she was trying to solve is the opposite - i.e., given an arbitrary seven day period defined by WeekStartDate and WeekEndDate, and a Dayname, find the date corresponding to that Dayname.


Hi James,
I'm late to reply. thank you for revealing exact requirement to them....

--
Chandu
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-12 : 09:22:21
Sorry about the missing "S", Chandu! I wasn't thinking
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-12 : 10:03:14

Hey what about me Chandu, I answered your question with the shortest solution first

quote:
Originally posted by bandi

quote:
Originally posted by James K

quote:
Originally posted by SwePeso

Try this
SELECT	DATENAME(WEEKDAY, GETDATE()) AS DayName,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000101') AS WeekStartDate,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000107') AS WeekEndDate;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

I got the impression from the original post that the problem she was trying to solve is the opposite - i.e., given an arbitrary seven day period defined by WeekStartDate and WeekEndDate, and a Dayname, find the date corresponding to that Dayname.


Hi James,
I'm late to reply. thank you for revealing exact requirement to them....

--
Chandu

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-12 : 10:26:18
Sorry MuMu, you used a table, even though it is a built in table in SQL Server. She did not want to use a table. So no cigar this time for you

PS: At least, that is what I think.
Go to Top of Page
   

- Advertisement -