SQL Server Performance Forum – Threads Archive
Typical Query
I want a QUERY for finding fridays in the given dates ( 06/06/2005 to 01/04/2006 )I wrote a sql block for it. but i need a query for it. is it possible ? sql block
*********
declare @dt datetime
set @dt = ‘20050606’
while @dt < ‘20060401’
begin
if datename(dw,@dt) = ‘Friday’
begin
print @dt
end
set @[email protected]+1
end
Need ur help ..
Thanks and Regards.,
Bala.
<pre id="code"><font face="courier" size="2" id="code"><br />–**************************************<br />– <br />– Name: allday.sql<br />– Description:You enter a day say ‘mond<br />– ay’ & it finds the dates of all mondays <br />– in the month.<br />– By: Dhairyasheel Tawde<br />–<br />– Inputs<img src=’/community/emoticons/emotion-3.gif’ alt=’:O’ />ne just has to input the day <br />– in quotes like say<br />’monday’ OR ‘tuesday’ etc.<br />–<br />– Returns:it returns the dates, corresp<br />– onding to the day entered, in the curren<br />– t month.<br />–<br />–<b>This code is copyrighted and has– limited warranties.Please see<a target="_blank" href=http://>http://</a><br />– www.Planet-Source-Code.com/vb/scripts/Sh<br />– owCode.asp?txtCodeId=465&lngWId=5–for details</b>.–**************************************<br />– <br /><br />declare<br />x date;<br />y date;<br />z date;<br />a char(10):= &day;<br />b char(10);<br /><br /><br /> begin<br /> SELECT trunc(to_date(sysdate),’month’) INTO x FROM dual;<br /> SELECT to_char(x,’day’) INTO b FROM dual;<br /> SELECT last_day(sysdate) INTO y FROM dual;<br /> dbms_output.put_line(a);<br /> IF a=b THEN<br /> dbms_output.put_line(x);<br />END if;<br />loop<br />SELECT next_day(x,a) INTO z FROM dual;<br /> <br />x:=z;<br />EXIT WHEN x>y;<br />dbms_output.put_line(z);<br />END loop;<br />end;</font id="code"></pre id="code"><br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Contributing Editor & Forums Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS†with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
Buddy , i found the sql block already and mentioned above it ..but i need it in a single query . whether it is possible?
Do you need this? select * from yourTable
where datename(dw,dateCol) = ‘Friday’
Madhivanan Failing to plan is Planning to fail
Problem is, of course – do you know beforehand that your database will only be running under one of the English locale settings for Windows? What you think is a Friday is something quite different in other languages.
select *
from table
where abs(dateDiff(day, dateColumn, ‘20060414’)) % 7 = 0
Yes, but doesn’t that depend on some sort of "week starting day" setting? Ah, globalism!
If you are talking about my query, answer is: No, it doesn’t.
This kind of post made me creazy.
13 hours after original post, 4 guys thinking an answer and…..nothing return. Where is the urgent? Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS†with no warranties for accuracy.
quote:Originally posted by mmarovic
select *
from table
where abs(dateDiff(day, dateColumn, ‘20060414’)) % 7 = 0
Isnt it possible without specifying the next friday’s date?
Madhivanan
Failing to plan is Planning to fail
select *
from table
where abs(dateDiff(day, dateColumn, ‘20060414’)) % 7 = 0
Yes, you can use whatever friday you want.
Really i am sorry guys .That’s why i was not replying you and was on leave . Thanks for your reply . But the thing is , i am not using anytable here . Please check my sql block which i mentioned already . There is no table i mentioned. Instead of i can give date variables 06/06/2005 to 01/04/2006 . so , which you replied will not work out for my problem buddies. Just i want to find the fridays between the given dates. That’s it. Please revert me back . Thanks and Regards.,
Bala
select *
from dbo.F_TABLE_DATE ( ‘2005-06-06′,’2006-01-04’)
whereDAY_OF_WEEK = 6 use the F_TABLE_DATE from here
KH
Thank you dude…it’s working fine…With out using function , we can not handle , it seems … Anyhow buddy ,let’s try to find a query with out using a function . Let’s do some R&D. :-> Take care
Thanks and Regards.,
Bala.
You should read answers carefully, answer was already there:
declare @dt datetime
set @dt = ‘20050606’
while @dt < ‘20060401’
begin
if abs(dateDiff(day, @dt, ‘20060414’)) % 7 = 0
begin
print @dt
end
set @[email protected]+1
end
]]>