Menu
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 FROM 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
705
The statement DATEDIFF(dd,@fromdate,@todate) + 1 gives the number of dates between the two dates. The statement DATEDIFF(wk,@fromdate,@todate) gives the number of weeks between dates and * 2 gives us the weekend (Saturday and Sunday) count. The next two statements excludes the day if it's a Saturday or Sunday.
SELECT DATE_ADD(NOW(),INTERVAL IF(WEEKDAY(NOW())>=5, (6-WEEKDAY(NOW())), (5-WEEKDAY(NOW()))) DAY);
Use the DATENAME() function and specify the datepart as weekday . select ID, Name, Salary, Date from dbo. yourTable where datename(weekday, Date) in ('Saturday', 'Sunday');
The DATEPART() function returns a specified part of a date. This function returns the result as an integer value.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
