SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Date from DayName, week start & End dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 08/08/2013 :  03:01:26  Show Profile  Reply with Quote
Dayname     WeekStartDate  WekkEndDate
Thursday    2013-08-05      2013-08-11


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
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 08/08/2013 :  04:08:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 08/08/2013 :  04:56:48  Show Profile  Reply with Quote
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

Sweden
30282 Posts

Posted - 08/08/2013 :  07:28:57  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
1 Posts

Posted - 08/08/2013 :  19:32:48  Show Profile  Reply with Quote
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

547 Posts

Posted - 08/08/2013 :  20:48:18  Show Profile  Reply with Quote


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;
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 08/09/2013 :  01:55:53  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
30282 Posts

Posted - 08/09/2013 :  02:06:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 08/09/2013 :  08:31:39  Show Profile  Reply with Quote
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

Sweden
30282 Posts

Posted - 08/09/2013 :  08:52:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 08/12/2013 :  00:27:59  Show Profile  Reply with Quote
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

Edited by - bandi on 08/12/2013 00:28:21
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 08/12/2013 :  00:30:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3761 Posts

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

MuMu88
Aged Yak Warrior

547 Posts

Posted - 08/12/2013 :  10:03:14  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 08/12/2013 :  10:26:18  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000