Folks,

For getting the number of days below is the function

I am creating my own schema and creating the user defined function in that schema like below for getting the no of days in the month

Go to new query window and wrote the below query and execute them

create schema chaitu

go

create function chaitu.fn_daysperMonth(@getdate datetime)

returns

int

as

begin

set @getdate=getdate()

declare @result int

SELECT @result=CASE WHEN MONTH(@getdate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31

WHEN MONTH(@getdate) IN (4, 6, 9, 11) THEN 30

ELSE

CASE WHEN ( YEAR(@getdate) % 4 = 0

AND YEAR(@getdate) % 100 != 0)

OR (YEAR(@getdate) % 400 = 0) THEN 29

ELSE 28

END

END

return @result

end

To execute the function

Select chaitu.fn_daysperMonth(getdate())

Don’t execute the function like below

Select * from chaitu.fn_daysperMonth(getdate())

You will get the error like below as this is the scalar function which is returning the variable int

For tabular functions you need use the syntax like below

Select * from userdefinedfuncuion()

If you don’t want to use the function, run the below script you will get the result

declare @getdate datetime

set @getdate=getdate()

SELECT CASE WHEN MONTH(@getdate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31

WHEN MONTH(@getdate) IN (4, 6, 9, 11) THEN 30

ELSE

CASE WHEN ( YEAR(@getdate) % 4 = 0

AND YEAR(@getdate) % 100 != 0)

OR (YEAR(@getdate) % 400 = 0) THEN 29

ELSE 28

END

END as ‘No of days per this month’

Regards,

Chaitanya,

Webiste : https://mssqlbuzz.wordpress.com/

To Send Mail : mssqlbuzz