How to find Number of Days in Sql Server? [Resolved]

Posted by Kirthiga under Sql Server on 12/29/2012 | Points: 10 | Views : 1519 | Status : [Member] | Replies : 4
How to find Number of Days in Sql Server for a specific date

For Example

Declare @date datetime='2012-11-25'


I have to find No. of days for @date (ie)Number of days for November month

My answer should be 30




Responses

Posted by: Pandians on: 12/29/2012 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Check It Out!
DECLARE @date DATETIME,

@Month DATETIME,
@Days INT

SELECT @date='2012-11-25'

SELECT @Month=DATEADD(MONTH,1,@date),@Days = DAY(@Month - DAY(@Month))

SELECT @Days [Day(s)]
(OR)
DECLARE @date DATETIME

SELECT @date='2012-11-25'
SELECT DAY(DATEADD(MONTH,1,@date) - DAY(DATEADD(MONTH,1,@date))) [Day(s)]


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sandhyab on: 12/29/2012 [Member] Starter | Points: 25

Up
0
Down
Once refer this link. This may help you
http://blog.sqlauthority.com/2007/09/08/sql-server-udf-user-defined-function-get-number-of-days-in-month/

Thanks & Regards

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vivekjj on: 12/29/2012 [Member] Starter | Points: 25

Up
0
Down
try this code its useful to u....



DECLARE @dtDate DATETIME


SET @dtDate = '2011/11/28'
SELECT @dtDate= dateadd(mm,datediff(mm,0,@dtDate),0)
SELECT datediff(dd,@dtDate,dateadd(mm,1,@dtDate))


Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sinisreedhar on: 12/29/2012 [Member] Starter | Points: 25

Up
0
Down
declare @date datetime
set @date='2012-11-25'
select datediff(day, dateadd(day, 1-day(@date), @date),
dateadd(month, 1, dateadd(day, 1-day(@date), @date)))

Ready to accept challenges at any time.....

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response