December 31, 2003 at 6:59 am
Hopefully someone will ne able to know what I'm after.
I would like to be able to write a SQL stored proc that (when a month/year is entered) returns the 4 or 5 weeks (by day) in that month. (i.e. if they enter in 11, 2003 it would return:
11/1/03
11/2/03 - 11/8/03
11/9/03 - 11/15/03
11/16/03 - 11/22/03
11/23/03 - 11/29/03
11/30
Does anyone know of a way or a script that I can get a hold of to do this??
Thanks for all the help
December 31, 2003 at 7:51 am
Perhaps something like this?
CREATE PROC p_weeks @mo tinyint, @yr smallint AS
SET NOCOUNT ON
SELECT CONVERT(char(8),MIN(CAST(dt AS datetime)),1) + ' - ' + CONVERT(char(8),MAX(cast(dt AS datetime)),1)
FROM
(SELECT CAST(@yr AS varchar(4)) + RIGHT('0' + CAST(@mo AS varchar(2)),2) + RIGHT('0' + CAST(Number AS varchar(2)),2) Dt
FROM master..spt_values
WHERE Type = 'P'
AND ISDATE(CAST(@yr AS varchar(4)) + RIGHT('0' + CAST(@mo AS varchar(2)),2) + RIGHT('0' + CAST(Number AS varchar(2)),2)) = 1) d
GROUP BY DATEPART(wk,CAST(dt AS datetime))
You could use CASE if you don't like the single date range formats.
--Jonathan
--Jonathan
December 31, 2003 at 10:02 am
Jonathan
I am looking at your SP and trying to understand it. Is there any documentation on spt_values? I can't seem to find it in BOL.
December 31, 2003 at 11:21 am
Jonathan,
Thanks works great
Shane
December 31, 2003 at 1:34 pm
quote:
JonathanI am looking at your SP and trying to understand it. Is there any documentation on spt_values? I can't seem to find it in BOL.
It's an undocumented "helper" table used by Microsoft in their system stored procedures. I just used it as a numbers table; one could instead easily create a numbers table with 31 rows containing just the integers from 1 to 31. The idea is to form a derived table (given the table alias "d" here) containing all the dates in the month.
--Jonathan
--Jonathan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply