InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 02:38:14 GMT20http://www.sqlservercentral.com/Forums/Topic1381858-145-1.aspxIf you want to see some more date calculations, look here:[b][url]http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url][/b]Mon, 19 Nov 2012 00:38:19 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1381858-145-1.aspxSimple:[code="sql"]declare @ThisDate datetime;set @ThisDate = getdate();select @ThisDate, dateadd(qq, datediff(qq, 0, @ThisDate), -1);set @ThisDate = '20120507';select @ThisDate, dateadd(qq, datediff(qq, 0, @ThisDate), -1);[/code]Mon, 19 Nov 2012 00:37:15 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1381858-145-1.aspxhi, You can write a query likeselect Case When Month(getdate()) <=3 Then '31-03-'+ Convert(Varchar(4),Year(GetDate())) When Month(getdate()) > 3 AND Month(getdate()) <=6 THEN '31-06-' + Convert(Varchar(4),Year(GetDate())) When Month(getdate()) > 9 AND Month(getdate()) <=9 THEN '31-09-' + Convert(Varchar(4),Year(GetDate())) ELSE '31-12-' + Convert(Varchar(4),Year(GetDate())) ENDthis will return you the expected resultMon, 19 Nov 2012 00:04:32 GMTAjay.Kedarhttp://www.sqlservercentral.com/Forums/Topic1381858-145-1.aspxThen I suggest you build a table which houses all the different probabilitiesWed, 07 Nov 2012 04:25:28 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1381858-145-1.aspxThanks,But the thing is it works for only current year and the input date can be last year date.for example input date '2010-07-06'(6th July 2010) then output should be 30.06.2010.Wed, 07 Nov 2012 04:15:55 GMTningaraju.nhttp://www.sqlservercentral.com/Forums/Topic1381858-145-1.aspxYou need to do a condition where Date >= FromDate and Date <= ToDate[code="sql"]DECLARE @Table TABLE (FromDate DATETIME, ToDate DATETIME, ResultDate DATETIME)INSERT INTO @Table VALUES('2012-01-01','2012-03-31','2011-12-31'),('2012-04-01','2012-06-30','2012-03-31'),('2012-07-01','2012-09-30','2012-06-30'),('2012-10-01','2012-12-31','2012-09-30')SELECT ResultDateFROM @TableWHERE GETDATE() >= FromDate AND GETDATE() <= ToDate[/code]Wed, 07 Nov 2012 03:44:41 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1381858-145-1.aspxI have a problem with datesProblem: There is a master table which defines the date based on range as follows[sup][b][From][/b] [b][To] [/b] [b][ResultDate][/b]'1st Jan' '31st March ' '31.12.YYYY-1''1st April' '30th June' '31.03.YYYY''1st July' '30th Sep' '30.06.YYYY''1st Oct' '31st Dec' '30.09.YYYY'[/sup]If my input date is today's date then it should return 30.09.2012 as today is between [b]from[/b] and [b]to[/b] dates of the 4th row.If my input is '2012-05-07'(7th May 2012) then I should get 31.03.2012Wed, 07 Nov 2012 03:18:05 GMTningaraju.n