Returning number of days in month by year

Question

Post reply

Returning number of days in month by year

luissantos

SSCertifiable

Points: 7391
More actions
June 2, 2006 at 3:34 am

#115440

Hello

I need to build a TSQL select that return to me the number of days in especific month and year, because i must build this TSQL between date and if the exit date of my employee in for exemple february is on 20-2-2006 then is more than 7 days, in this case this employee can´t appear in my TSQL statment. if exist a function to return that a was pretend.If exist could you give me a small example to use them.
Many thanks
Luis Santos

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLZ SSChampion Points: 12872 More actions · Answer 1

Try this function posted by DavidT here in the SQLServerCentral library:

CREATE function funDaysInMonth

(@Date Datetime)

returns int

as

begin

declare @Days int

set @Date = (@Date - (day(@Date) - 1))

set @Date = dateadd(mm,1,@Date)

set @Date = dateadd(dd, -1, @Date)

set @Days = datepart(dd,@Date)

return (@Days)

end

Karl
source control for SQL Server

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

I am not sure what you want.

DECLARE @Year SMALLINT

SELECT @Year = 2006

SELECT 1 'Month', 31 'Days' UNION

SELECT 2, CASE WHEN ISDATE(CONVERT(CHAR(4), @Year) + '-02-29') = 1 THEN 29 ELSE 28 END UNION

SELECT 3, 31 UNION

SELECT 4, 30 UNION

SELECT 5, 31 UNION

SELECT 6, 30 UNION

SELECT 7, 31 UNION

SELECT 8, 31 UNION

SELECT 9, 30 UNION

SELECT 10, 31 UNION

SELECT 11, 30 UNION

SELECT 12, 31

N 56°04'39.16"
E 12°55'05.25"

luissantos SSCertifiable Points: 7391 More actions · Answer 3

Thanks for your help

Karl , i need to know in TSQL how i call your function:

funDaysInMonth(31122005)

SQLZ SSChampion Points: 12872 More actions · Answer 4

select dbo.funDaysInMonth('31 Dec 2005')

Karl
source control for SQL Server

luissantos SSCertifiable Points: 7391 More actions · Answer 5

luissantos

SSCertifiable

Points: 7391

June 2, 2006 at 10:09 am

#641494

Many thanks Karl

it´s work OK

Luis Santos

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

If you'd rather just have the last day of the month, substitute any valid date for GETDATE() in the following...

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)-1

If you really want the number of days in a month for a give date, this will usually beat the pant's off most UDF's because it doesn't have the overhead...

SELECT DAY(DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)-1)

--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)

stax68 SSChampion Points: 11711 More actions · Answer 7

I agree, and on a more general point. Scalar UDFs are not a panacea. If they are used in a multi-row SQL statement, they are best used as a shorthand for frequently used expressions. Putting a UDF which does any sort of complex process in a SQL statement is not ofetne a very good idea, and especially if the UDF does any sort of data access. Don't be fooled into thinking that writing a scalar UDF is equivalent to creating a new inbuilt SQL function. It's not. It's still written in SQL (or that wretched semi-interpreted CLR code in SQL 9). And it still has to be evaluated for every row. The SQL code may look nice and short, but it's not really.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant