how i can find total no. of days in month in SQL Server2000?
-
how i can find total no. of days in month in SQL Server2000?
-
Something like this?
DECLARE @ThisMonth DATETIME
DECLARE @NextMonth DATETIME
SET @ThisMonth = '2005-03-24'
IF DATEPART(DAY, @ThisMonth) <> 1
BEGIN
SET @ThisMonth = DATENAME(YEAR, @ThisMonth) + '-' + DATENAME(MONTH, @ThisMonth) + '-01'
END
SET @NextMonth = DATEADD(MONTH, 1, @ThisMonth)
SELECT DATEDIFF(DAY, @ThisMonth, @NextMonth)
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Well, everything depends on what data you have and what is the desired result... SQLS doesn't have any table that would directly store numbers of days in all months, and also it doesn't offer any function that would directly calculate it. You can create your own UDF, if you'll be needing it often.
There is a DATEDIFF function that will tell you how many days it is from one date to another - so if you feed it with the first and last date of a month, you'll get the number of days in that month. Following SQL is what I'm using for similar purposes (credits should go to Kenneth Wilhelmsson)
-- date of the 1st of month
select convert(char(6), @date, 112) + '01'
-- date of the last day of month
select dateadd(day, -1, dateadd(month, 1, convert(char(6), @date, 112) + '01'))
Well, now try to stuff this into the DATEDIFF and you should get a number of days in the month.
SELECT DATEDIFF(day, convert(char(6), @date, 112) + '01', DATEADD(day, -1, DATEADD(month, 1, convert(char(6), @date, 112) + '01'))) + 1
HTH, Vladan
EDIT: Sorry for not declaring the variable. You'll need to declare it and assign a value to i - like this
declare @date datetime
set @date = '2005.04.01'
-
Just to round out the list of possible solutions...
--===== Find number of days in current month
SELECT DATEPART(dd,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)-1)
It works by finding the first day of next month, subtracts one day, and takes the "day" datepart to give you the last day of the current month which just happens to be how many days are in the month.
You can substitute any valid date or variable containing a valid date for GETDATE() and it does not matter if the time element is included or not.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply