SQL scalar value - 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

SQL Server Administration (2005)

SQL scalar value

Author

Topic

sumitdhruv
Starting Member

1 Post

Posted - 2009-08-09 : 07:43:44

SQL Scalar-valued function

Objective:
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

Function Name: Smart_Get_WeekOfMonth_Range

Example:
Input Date Return String 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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-09 : 08:06:25

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 : 10:17:41

[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:55

[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