hi all
i need a query which will give the list of all the months between two given dates
I am not sure this works in all query languages, but in T-SQL you can use the following:
SELECT DATEDIFF(m, GetDate(), '12/25/2006')
This gives you the number of months between today and Christmas. Running that query in SQL will return "1" (if you ran it today anyway). The structure for the command is:
DATEDIFF(datepart, startdate, enddate)
For the datepart, you use "m" to indicate you want the number of months. You could also use "d" for days or "y" for years.
Hope this helps.
-Jacob
|||no jacob i need list of months between two dates
forexample
input:
startdate:1/1/2006
enddate:1/12/2006
output:
jan-2006
feb-2006
.....
......
dec-2006
|||Funny, rather work on other bugs right now than my own heehee, if anyone knows how to print a barcode from MS Reporting Services let me know.
I have a thread about ithere.
But here's some code for vmssanthosh:
DECLARE @.date1datetime, @.date2datetime, @.totalMonthsint, @.counterintSET @.date1 ='01/01/2006'SET @.date2 ='01/01/2007'--'02/02/2007'SET @.totalMonths =DATEDIFF(m, @.date1, @.date2)if(@.totalMonths < 0)SELECT'Second date parameter is prior to the first date parameter'if(@.totalMonths =0)SELECT'Same month'elseBEGINcreate table #temp_months(nameOfMonthvarchar(9))SET @.counter = 0WHILE @.counter < @.totalMonthsBEGININSERT INTO #temp_months (nameOfMonth)VALUES (DATENAME(month, @.date1))SET @.counter = @.counter + 1SET @.date1 =DATEADD(Month, 1, @.date1)ENDSELECT *FROM #temp_monthsEND|||
For the format you specified, change the INSERT statement to this:
INSERT INTO #temp_months (nameOfMonth)VALUES (LEFT(DATENAME(month, @.date1),3) +'-' +DATENAME(year, @.date1))
