week didplay problem - 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 2005 Forums

Transact-SQL (2005)

week didplay problem

Author

Topic

raghu_grdr
Starting Member

17 Posts

Posted - 2009-08-09 : 11:14:19

I AM WRITING THE FOLLOWING FUNCTION

CREATE FUNCTION [dbo].[Smart_Get_WeekOfMonth_Range]
(
@F_DATE NVARCHAR(240)
)
RETURNS NVARCHAR(240)

BEGIN
DECLARE
@theDate DATETIME,
@TMP_OUTPUT AS NVARCHAR(240)

SET @theDate = @F_DATE

;WITH Yak(firstMonth, lastMonth, w)
AS (
SELECT m.firstMonth,
m.lastMonth,
DATEADD(DAY, DATEDIFF(DAY, -53686, m.firstMonth) / 7 * 7, d.k - 53686) AS w
FROM (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @theDate), 0) AS firstMonth,
DATEADD(MONTH, DATEDIFF(MONTH, -1, @theDate), -1) AS lastMonth
) AS m
CROSS JOIN (
SELECT 0 UNION ALL
SELECT 7 UNION ALL
SELECT 14 UNION ALL
SELECT 21 UNION ALL
SELECT 28 UNION ALL
SELECT 35
) AS d(k)
)

SELECT

@TMP_OUTPUT = 'Week '+ cast((datepart(day , weekFrom)/7) +1 as nvarchar(10))+ ', ' + left(convert (nvarchar(100), weekFrom, 0), 6) + ' to ' + cast(datepart(day , weekTo) as nvarchar(10))

FROM (
SELECT CASE
WHEN w <= firstMonth THEN firstMonth
ELSE w
END AS weekFrom,
CASE
WHEN lastMonth <= DATEADD(DAY, 6, w) THEN lastMonth
ELSE DATEADD(DAY, 6, w)
END AS weekTo
FROM Yak
) AS d
WHERE weekFrom < weekTo
AND DATEADD(DAY, DATEDIFF(DAY, -53690, @theDate), -53690) BETWEEN weekFrom AND weekTo

RETURN @TMP_OUTPUT

END

This Function works fine for Auguest Month .

But if i test with September month . I am getting the wrong week .

Input Date

Returns

Should Have been

'07/01/2009 00:00:00'

Week 1, Jul 1 to 2

CORRECT

‘07/03/2009 00:00:00’

Week 1, Jul 3 to 9

Week 2, Jul 3 to 9

'07/11/2009 00:00:00'

Week 2, Jul 10 to 16

Week 3, Jul 10 to 16

'07/30/2009 00:00:00'

Week 4, Jul 24 to 30

Week 5, Jul 24 to 30

'07/31/2009 00:00:00'

NULL

Week 6, Jul 31 to 31

what is the problem in the function ?
raghu

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-09 : 11:58:16

How come "September" gives results in July?

Instead of posting a new question all the time, maybe you should just continue the other topics you started, so there can be some continuity??
And I already gave you a solution, which works!

DECLARE	@theDate DATETIME

SET		@theDate = '20090914'

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

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

raghu_grdr
Starting Member

17 Posts

Posted - 2009-08-09 : 13:02:16

ur query gives output like

2009-07-03 00:00:00.000 2009-07-09 00:00:00.000

i need the output like

Week 2, Jul 3 to 9 .

For Example :

Input : '07/01/2009 00:00:00'
Output : Week 1, Jul 1 to 2

Input : '07/03/2009 00:00:00’
Output : Week 2, Jul 3 to 9

Input : '07/11/2009 00:00:00'
Output : Week 3, Jul 10 to 16

Input : '07/30/2009 00:00:00'
Output : Week 5, Jul 24 to 30

Input : '07/31/2009 00:00:00'
Output : Week 6, Jul 31 to 31

If i give above INPUT in the function . I need the output as shown above .

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-09 : 15:27:00

Is formatting the only problem?

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

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

raghu_grdr
Starting Member

17 Posts

Posted - 2009-08-09 : 15:44:30

s . Thank u

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-09 : 15:56:18

Thank you.
Now go mark the other answer on the other forum as answered.

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

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-10 : 00:28:56

Hi, Try this also


CREATE TABLE #Calendar(Calendar_Date DATETIME, Year_Num smallint,Week_Num smallint)

DECLARE @dtDate datetime
SET @dtDate = '1/1/2009'
WHILE (@dtDate <= '1/31/2009')
BEGIN
INSERT INTO #Calendar
(Calendar_Date,Year_Num,Week_Num )
SELECT @dtdate,
Year(@dtDate),datepart(wk,@dtdate)
SET @dtDate = DATEADD(dd,1,@dtDate)
END

SELECT MIN(CONVERT(VARCHAR(32),Calendar_Date,101))+' - '+MAX(CONVERT(VARCHAR(32),Calendar_Date,101)) AS 'DATES', Year_Num, Week_Num FROM #Calendar
GROUP BY Year_Num, Week_Num

raghu_grdr
Starting Member

17 Posts

Posted - 2009-08-10 : 04:07:52

i have a following function :
--------------------------------------

ALTER FUNCTION [dbo].[Smart_Get_WeekOfMonth_Range]
(
@F_DATE NVARCHAR(240)

)
RETURNS NVARCHAR(240)
BEGIN
DECLARE
@theDate DATETIME,
@TMP_OUTPUT AS NVARCHAR(240)

SET @theDate = @F_DATE

SELECT @TMP_OUTPUT = '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

RETURN @TMP_OUTPUT

---------------------------It is working fine .

I need Following Enhancements in the function:

1: Add 0 to the dates that are One Digit (smaller then 10)

The result that I get currently is in Following Format:

Week 2, Aug 7 to 13

I need the same to be in this Format:

Week 2, Aug 07 to 13

2: Introduce parameters for all parts. This will help to get the result in flexible format

Example: Week 2, Aug 07 to 13

Week

2

‘, ‘

Aug

07

‘ to ‘

13

Prefix

Week Number

Separator 1

Month Name

Start Date

Separator 2

End Date

Add these parameters in the function:

Prefix: We can pass any text like ‘Week: ’ (Default will be ‘Week ‘)

Return_Week_Number: 1 = True, 0 = False (Default will be True)

Separator 1: We can pass any text like ‘ - ’ (Default will be ‘, ‘)

Return_Month_Name: 1 = True, 0 = False (Default will be True)

Return_Start_Date: 1 = True, 0 = False (Default will be True)

Separator 2: We can pass any text like ‘ - ’ (Default will be ‘ to ‘)

Return_End_Date: 1 = True, 0 = False (Default will be True)

Can you please tell me how to include these things in this function ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-10 : 04:18:24

Even more formatting? I have a suggestion for you...
Please read Books Online. It is an EXCELLENT source of information!

DECLARE	@theDate DATETIME

SET		@theDate = '20090703'

SELECT	'Week ' + CAST(1 + (DATEDIFF(DAY, -53686, @theDate) - DATEDIFF(DAY, -53686, firstMonth) / 7 * 7 ) / 7 AS VARCHAR(1)) + ', ' +
	CONVERT(CHAR(6), CASE
			WHEN w < firstMonth THEN firstMonth
			ELSE w
		END, 107) + ' to ' + 
	CONVERT(CHAR(2), CASE
			WHEN q > lastMonth THEN lastMonth
			ELSE q
		END, 106)
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

Now go mark all my other suggestion as ANSWER for these topics
It's nice if you acknowledge the help you get instead of just continue to leach.

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/b3419e58-807f-44d4-b5fc-d68dff5effd1
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c997b0d6-d97c-40c9-a70f-0fac9dffc66a
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/2043b400-b389-4776-a16c-3af46cdb5e44
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c25e1e54-9f65-4010-97e3-ac287c56325f

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

raghu_grdr
Starting Member

17 Posts

Posted - 2009-08-10 : 04:46:03

THANKS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources