query problem 2 - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

query problem 2

Author

Topic

raghu_grdr
Starting Member

17 Posts

Posted - 2009-08-09 : 07:28:32

Having passed the Required Date it should return the String Like This: “Week WK Mmm WeekRange”

Week Starts from Friday or the First Date of Month
Week Ends on Thursday or the Last Date of Month

Example:
Input Date Output Comments
01/08/2009 00:00:00 Week 1, Aug 01 to 06 The week will start from Saturday being first day of the month, End on Thursday

09/08/2009 18:02:07 Week 2, Aug 07 to 13 The week will start from Friday, End on Thursday

20/08/2009 00:00:00 Week 3, Aug 14 to 20 The week will start from Friday, and on Thursday

26/08/2009 00:00:00 Week 4, Aug 21 to 27 The week will start from Friday, End on Thursday

31/08/2009 00:00:00 Week 5, Aug 28 to 31 The week will start from Friday, End on Monday being last day of the month

how to write the query for the same ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-09 : 08:06:19

Question (and answer) already posted here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130878

N 56°04'39.26"
E 12°55'05.63"

raghu_grdr
Starting Member

17 Posts

Posted - 2009-08-09 : 08:16:48

I will be passing one input date at a time . So my output will be one at a time .

example .

pass 1/08/2009 00:00:00 as input

i need like this Week 1, Aug 01 to 06

quote:
Originally posted by Peso

Question (and answer) already posted here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130878

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-09 : 08:59:25

Add this single line to the existing WHERE clause

AND DATEADD(DAY, DATEDIFF(DAY, -53690, @theDate), -53690) BETWEEN weekFrom AND weekTo

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-09 : 10:17:54

[code]DECLARE @theDate DATETIME

SET @theDate = GETDATE()

SELECT CASE
WHEN w < firstMonth THEN firstMonth
ELSE w
END AS weekFrom,
CASE
WHEN q > lastMonth THEN lastMonth
ELSE q
END AS weekTo
FROM (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @theDate), 0) AS firstMonth,
DATEADD(DAY, DATEDIFF(DAY, -53686, @theDate) / 7 * 7, -53686) AS w,
DATEADD(MONTH, DATEDIFF(MONTH, -1, @theDate), -1) AS lastMonth,
DATEADD(DAY, DATEDIFF(DAY, -53686, @theDate) / 7 * 7, -53680) AS q
) AS d[/code]

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-09 : 15:31:25

[code]DECLARE @theDate DATETIME

SET @theDate = '20090703'

SELECT 'Week ' + CAST(1 + (DATEDIFF(DAY, -53686, @theDate) - DATEDIFF(DAY, -53686, firstMonth) / 7 * 7 ) / 7 AS VARCHAR(1)) + ', ' +
LEFT(DATENAME(MONTH, @theDate), 3) + ' ' +
DATENAME(DAY, CASE
WHEN w < firstMonth THEN firstMonth
ELSE w
END) + ' to ' +
DATENAME(DAY, CASE
WHEN q > lastMonth THEN lastMonth
ELSE q
END)
FROM (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @theDate), 0) AS firstMonth,
DATEADD(DAY, DATEDIFF(DAY, -53686, @theDate) / 7 * 7, -53686) AS w,
DATEADD(MONTH, DATEDIFF(MONTH, -1, @theDate), -1) AS lastMonth,
DATEADD(DAY, DATEDIFF(DAY, -53686, @theDate) / 7 * 7, -53680) AS q
) AS d[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources