May 30, 2012 at 1:27 am
Dear all,
Please can anybody help me with a stored procedure which returns all dates falling on saturday for the given year.
Thanks in advance,
Kind regards,
Ram
May 30, 2012 at 1:31 am
You could use a calendar table for this. First two links from a search on "calendar table" using the search box on this page shown below.
http://www.sqlservercentral.com/articles/70482/
http://www.sqlservercentral.com/scripts/Date/68389/
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 30, 2012 at 1:15 pm
A calendar table is definitely the best way to do this. You can do it with a numbers/tally table or cte as well. Something like this:
WITH N0
AS (
SELECT
0 AS const
UNION ALL
SELECT
0
),
N1
AS (
SELECT
0 AS const
FROM
N0 AS A,
N0 AS B
),
N2
AS (
SELECT
0 AS const
FROM
N1 AS A,
N1 AS B
),
N3
AS (
SELECT
0 AS const
FROM
N2 AS A,
N2 AS B
),
N4
AS (
SELECT
0 AS const
FROM
N3 AS A,
N3 AS B
),
numbers
AS (
SELECT TOP 367
ROW_NUMBER() OVER (ORDER BY const) - 1 AS n
FROM
N4
)
SELECT
DATEADD(DAY, n, '20120101'),
DATENAME(weekday, DATEADD(DAY, n, '20120101')) AS theDate
FROM
numbers
WHERE
DATENAME(weekday, DATEADD(DAY, n, '20120101')) = 'Saturday'
I have similar script in this blog post, http://wiseman-wiseguy.blogspot.com/2008/12/simple-but-effective-code-example.html as well
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 30, 2012 at 1:49 pm
although this solution will require a table scan, since the function on the column Name makes this non-SARG-able, this will work too:
select * from YourTable where DATENAME(dw,TheDate) = 'Saturday'
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply