开发者

get date of all saturdays in a given year - sql server

I need to get a list of all Saturday dates in a given year.

I've seen an oracle post that goes against a table that had "fiscal calendar table" but I haven't been able to succeed in converting it nor do I have a table that contains a set of dates I want to investigate.

SELECT DATE DATES,TO_CHAR(DATE,'DAY') DAYS FR开发者_StackOverflow社区OM FISCAL_CALENDAR
WHERE DATE_YEAR = 2009 AND
DATE BETWEEN TRUNC(SYSDATE,'YEAR') AND
ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),12) -1 AND
TRIM(TO_CHAR(DATE,'DAY')) = 'SUNDAY'

was the Oracle (it was for Sunday and 2009 eg)

Much thanks. -Tom


for the year 2010 you can do this

declare @d datetime
select @d = '20100101'  --'20090101'  if you want 2009 etc etc

select dateadd(dd,number,@d) from master..spt_values
where type = 'p'
and year(dateadd(dd,number,@d))=year(@d)
and DATEPART(dw,dateadd(dd,number,@d)) = 7


declare @d datetime select @d = '20100101' --'20090101' if you want 2009 etc etc

select dateadd(dd,number,@d) from master..spt_values where type = 'p' and datename(weekday, (dateadd(dd, number, @d))) in ('Saturday', 'Tuesday' /* Specify Day Name */)


@SQLMenace this is to show every other Saturday, hope it helps someone, as I am trying to create a shift calendar using SQL. 3 days on, 2 days off and 2 days on, starting on the first Saturday of the year.

declare @d datetime
select @d = '20210101'  --'20090101'  if you want 2009 etc etc

select dateadd(dd,number,@d) as "Day" from master..spt_values
where type = 'p'
and year(dateadd(dd,number,@d))=year(@d)
and DATEPART(dw,dateadd(dd,number,@d)) = 7 and DATEPART(WK,DATEADD(dd,number,@d)) % 2 = 0; 
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜