SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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

SQL Blog by Rahul Sahay

I have a total experience of 6 years primarily in databases (T-SQL and Performance Tuning). I have worked on different versions starting with SQL Server 2000 to SQL Server 2008 R2 for companies like Accenture and Airtel. I have completed Microsoft certification in MCTS (SQL SERVER). I did my MCA (Masters Of Computer Application) degree from Bangalore and have a BCA (Bachelor Of Computer Applications) from Indore.


Leave a comment on the original post [rahulsahay123.blogspot.com, opens in a new window]

Loading comments...