Monday, February 25, 2013

SQL BLOG: Calculating total number of days from 1st Jan without using DATEDIFF function

Below script will calculate total number of days based on the input from user from 1st Jan. Though we have a built in function DATEDIFF from which we can calculate this easily. But in the below script, i have tried calculating the days without this built in function.

I have used Scalar UDF for calculating remaining days in the month. Script for the same can be found in my first article.

/* Calculate total number of days from 1st Jan */

declare @fresult integer
declare @tdate date
declare @mdate integer
declare @counter integer
declare @intermidate_result integer
set @tdate = '2012-12-31'
set @counter = 1
set @mdate = MONTH(@tdate)
set @intermidate_result = 0
set @fresult = 0
while @counter < @mdate
/* below function will give last month remaining days */
set @fresult = dbo.udf_calculate_remaining_days(DATEADD(mm,-@counter,@tdate))
/* will give total numbers of days minus current month */
set @intermidate_result = DATEPART(dd,dateadd(mm,-@counter,@tdate))+ @fresult+@intermidate_result
set @counter = @counter + 1
declare @current_date integer
set @current_date = DAY(@tdate)
set @intermidate_result = @intermidate_result + @current_date
select 'Total Number Of Days from :'+'2012-01-01'+'  '+'To'+'  '+CAST(@tdate as varchar(10))+'  '+'is:'+'  '+CAST(@intermidate_result as varchar(4))

Sample Output:
Total Number Of Days from :2012-01-01  To  2012-12-31  is:  366

No comments:

Post a Comment