sql server query for datediff

-- DOES NOT ACCOUNT FOR LEAP YEARS
DECLARE @date1 DATETIME, @date2 DATETIME, @result VARCHAR(100);
DECLARE @years INT, @months INT, @days INT,
    @hours INT, @minutes INT, @seconds INT, @milliseconds INT;

SET @date1 = '1900-01-01 00:00:00.000'
SET @date2 = '2018-12-12 07:08:01.123'

SELECT @years = DATEDIFF(yy, @date1, @date2)
IF DATEADD(yy, -@years, @date2) < @date1 
SELECT @years = @years-1
SET @date2 = DATEADD(yy, -@years, @date2)

SELECT @months = DATEDIFF(mm, @date1, @date2)
IF DATEADD(mm, -@months, @date2) < @date1 
SELECT @months=@months-1
SET @date2= DATEADD(mm, -@months, @date2)

SELECT @days=DATEDIFF(dd, @date1, @date2)
IF DATEADD(dd, -@days, @date2) < @date1 
SELECT @days=@days-1
SET @date2= DATEADD(dd, -@days, @date2)

SELECT @hours=DATEDIFF(hh, @date1, @date2)
IF DATEADD(hh, -@hours, @date2) < @date1 
SELECT @hours=@hours-1
SET @date2= DATEADD(hh, -@hours, @date2)

SELECT @minutes=DATEDIFF(mi, @date1, @date2)
IF DATEADD(mi, -@minutes, @date2) < @date1 
SELECT @minutes=@minutes-1
SET @date2= DATEADD(mi, -@minutes, @date2)

SELECT @seconds=DATEDIFF(s, @date1, @date2)
IF DATEADD(s, -@seconds, @date2) < @date1 
SELECT @seconds=@seconds-1
SET @date2= DATEADD(s, -@seconds, @date2)

SELECT @milliseconds=DATEDIFF(ms, @date1, @date2)

SELECT @result= ISNULL(CAST(NULLIF(@years,0) AS VARCHAR(10)) + ' years,','')
     + ISNULL(' ' + CAST(NULLIF(@months,0) AS VARCHAR(10)) + ' months,','')    
     + ISNULL(' ' + CAST(NULLIF(@days,0) AS VARCHAR(10)) + ' days,','')
     + ISNULL(' ' + CAST(NULLIF(@hours,0) AS VARCHAR(10)) + ' hours,','')
     + ISNULL(' ' + CAST(@minutes AS VARCHAR(10)) + ' minutes and','')
     + ISNULL(' ' + CAST(@seconds AS VARCHAR(10)) 
     + CASE
            WHEN @milliseconds > 0
                THEN '.' + CAST(@milliseconds AS VARCHAR(10)) 
            ELSE ''
       END 
     + ' seconds','')

SELECT @result

Are there any code examples left?
Create a Free Account
Unlock the power of data and AI by diving into Python, ChatGPT, SQL, Power BI, and beyond.
Sign up
Develop soft skills on BrainApps
Complete the IQ Test
Relative searches
sql server datediff in minutes DATEDIFFERENCE SQL datediff sql function t sql datediff tsql, DATEDIFF datediff access sql datediff sql date difference DATEDIFF sql query datediff sql sample datediff sql years sql datediff wk datediff sql minutes sql condition datediff SQL SERVER DATEDIFF FOR MONTHS between datediff MSSQL datediff in minutes sql sql statement datediff DATEDIFF IN SQL EXPLAINED sql datediff hours DateDiff in mssql sql server datediff example how to query according to datediff in sql how to query according to datediff DATEDIFF IN SQL QUERY sql server datediff minutes sql datediff ss sql datediff seconds difference between sql datediff datediff minutes sql sql server date arithmetic datediff what type is datediff sql server what is datediff in sql sql datediff column sql server datediff hours get datediff in sql datediff sql server minutes select datediff in sql server datediff sql&acute; datediff in select statement datediff -1 sql server sql server datediff in seconds sql datediff minutes between two dates sql server datediff days ms sql datediff sql datediff function sql datediff minutes sql datediff month t-sql datediff sql datediff years sql datediff func jpql datediff datediff() sql server datediff sql from table how to use datediff in sql select datediff( sql datediff datetime sql server SELECT DATEDIFF datediff for day in sql server datediff sqlserver datediff() sql datediff sql - datediff sql server example datediff in sql server datediff function in sql datediff minutes sql server datediff sqk datediff in sql server in second SQLSERVER DATEDIFF sql datediff in minutes sql number of months between two dates DATEDIFF ORALCE SQL WHOW TO DO DIFFERENCE WITH DATES IN SQL sql server datedff time diff between dates sql sql server difference between two dates in minutes datediff mssql date difference of an entire query in sql Get years between two dates in sql datediff in sql in minutes how to take difference of two dates in sql mssql datediff yearDiff sql date subtraction in sql sql datediff days tsql datediff duration in sql w3schools duration in sql date diffrnce in sql sql difference between dates DATEDIFF(date1, date2) how to calculate date and year in sql msql date diff date difference mssql sql datediff funtion where with datediff sql Date.diff_minutes sql date difference sql subtract datetime datediff dias &uacute;teis sql server datediff sql sql see the outcome of datediff datediff in sql date diff in sql server in day sql difference between dates in days sql server get difference between two dates select datediff datetime sql get date difference from today sql get date difference date difference sql how to calculate the duration between two dates in sql datediff days in sql datediff milliseconds sql server datediff total Time difference in mssql datediff sql server date date difference in minutes sql server datediff example diff between two dates sql sql server syntax date diff datediff sql datediff sql date difference days sql datediff and return days date difference in sql datediff t-sql tsql return datediff in milli days difference between two dates in sql use datediff in access SQL QUERY
Made with love
This website uses cookies to make IQCode work for you. By using this site, you agree to our cookie policy

Welcome Back!

Sign up to unlock all of IQCode features:
  • Test your skills and track progress
  • Engage in comprehensive interactive courses
  • Commit to daily skill-enhancing challenges
  • Solve practical, real-world issues
  • Share your insights and learnings
Create an account
Sign in
Recover lost password
Or log in with

Create a Free Account

Sign up to unlock all of IQCode features:
  • Test your skills and track progress
  • Engage in comprehensive interactive courses
  • Commit to daily skill-enhancing challenges
  • Solve practical, real-world issues
  • Share your insights and learnings
Create an account
Sign up
Or sign up with
By signing up, you agree to the Terms and Conditions and Privacy Policy. You also agree to receive product-related marketing emails from IQCode, which you can unsubscribe from at any time.
Creating a new code example
Code snippet title
Source