Author |
Topic |
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-08-08 : 03:01:26
|
[code]Dayname WeekStartDate WekkEndDateThursday 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 ComputedDateFROM CTE c INNER JOIN #TMP t ON DATEADD(DAY,c.N,t.WeekstartDate) <= t.weekEndDateWHERE 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. |
|
|
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 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-08 : 07:28:57
|
Try thisSELECT 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 |
|
|
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; |
|
|
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] |
|
|
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 |
|
|
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 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-09 : 08:31:39
|
quote: Originally posted by SwePeso Try thisSELECT 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. |
|
|
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');-- SwePesoSELECT 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 WantedDateFROM @Sample;[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
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');-- SwePesoSELECT 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 WantedDateFROM @Sample; Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
This solution is looking good. thank you--Chandu |
|
|
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 thisSELECT 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 |
|
|
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 |
|
|
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 thisSELECT 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
|
|
|
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. |
|
|
|
|
|