sql datediff month

-- 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

3.5
6
Krish 100200 points

                                    CREATE FUNCTION FullMonthsSeparation 
(
    @DateA DATETIME,
    @DateB DATETIME
)
RETURNS INT
AS
BEGIN
    DECLARE @Result INT

    DECLARE @DateX DATETIME
    DECLARE @DateY DATETIME

    IF(@DateA &lt; @DateB)
    BEGIN
        SET @DateX = @DateA
        SET @DateY = @DateB
    END
    ELSE
    BEGIN
        SET @DateX = @DateB
        SET @DateY = @DateA
    END

    SET @Result = (
                    SELECT 
                    CASE 
                        WHEN DATEPART(DAY, @DateX) &gt; DATEPART(DAY, @DateY)
                        THEN DATEDIFF(MONTH, @DateX, @DateY) - 1
                        ELSE DATEDIFF(MONTH, @DateX, @DateY)
                    END
                    )

    RETURN @Result
END
GO

SELECT dbo.FullMonthsSeparation('2009-04-16', '2009-05-15') as MonthSep -- =0
SELECT dbo.FullMonthsSeparation('2009-04-16', '2009-05-16') as MonthSep -- =1
SELECT dbo.FullMonthsSeparation('2009-04-16', '2009-06-16') as MonthSep -- =2

3.5 (6 Votes)
0
0
0
Phoenix Logan 186120 points

                                    Small change like this can be done

  SELECT  EmplID
        , EmplName
        , InTime
        , [TimeOut]
        , [DateVisited]
        , CASE WHEN minpart=0 
        THEN CAST(hourpart as nvarchar(200))+':00' 
        ELSE CAST((hourpart-1) as nvarchar(200))+':'+ CAST(minpart as nvarchar(200))END as 'total time'
        FROM 
        (
        SELECT   EmplID, EmplName, InTime, [TimeOut], [DateVisited],
        DATEDIFF(Hour,InTime, [TimeOut]) as hourpart, 
        DATEDIFF(minute,InTime, [TimeOut])%60 as minpart  
        from times) source

0
0
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 datediff year sql sql difference between two datetimes in hours sql query to find month difference between two dates sql server datediff hours and minutes DATEDIFFERENCE SQL datediff sql function DateDiff Day SQL get diff time between 2 dates in sql server in minutes and seconds difference of months between two dates sql sql datediff in days using date diff hours in sql query t sql datediff tsql, DATEDIFF how to calculate month difference in sql SQL DATEDIFF Day datediff access sql datediff sql date difference datediff sql year month day DATEDIFF sql query datediff sql sample how to get month difference in sql datediff sql years sql datediff wk datediff in minutes in sql datediff sql minutes sql condition datediff two date difference in hours minutes seconds sql SQL SERVER DATEDIFF FOR MONTHS difference in months between two dates sql difference in hours between two dates sql between datediff MSSQL datediff in minutes sql sql statement datediff DATEDIFF IN SQL EXPLAINED sql datediff hours datediff sql month datediff as hours sql DateDiff in mssql show datediff in hours and minutes with 2 digits each one sql server datediff sql decimal hours how to get datediff in months in sql sql server datediff example GET DIFF HOURS AND MINUTES BETWEEN 2 DATES SQL SERVER t-sql datediff hours sql convert datediff to hours and minutes how to query according to datediff in sql how to query according to datediff DATEDIFF IN SQL QUERY sql date difference in minutes sql datediff in hours sql server datediff minutes sql datediff ss sql datediff seconds sql where difference between two dates in hours and minutes sql difference between two dates in hours and minutes sql datediff months diff hours between two dates sql server difference between sql datediff datediff minutes sql sql server date arithmetic datediff what type is datediff sql server get difference in hours between two dates sql what is datediff in sql t-sql difference between two dates in seconds sql datediff column sql server datediff hours minutes between two dates sql sql date diff in hours sql datediff between two dates in hours and minutes get datediff in sql datediff sql server minutes date difference in minutes in sql select datediff in sql server two date difference in sql sql datediff between a date and days after a date datediff sql&acute; datediff in select statement datediff in days sql datediff sql hour datediff -1 sql server sql server datediff in seconds datediff in days in sql sql datediff minutes between two dates sql time difference in minutes sql datetime difference in hours and minutes sql datediff between two dates SQL DATEDIFF intervals date time difference between two dates in minutes in sql server sql server datediff days ms sql datediff sql datediff function sql datediff minutes Difference of Date in Minutes:seconds in sql sql datediff month t-sql datediff datediff days sql sql datediff years sql datediff func sql compare dates jpql datediff datediff() sql server SQL command to et the difference between two dates datetime difference in hours sql datediff hours sql sql get datediff in minutes datediff sql from table datediff sql number days sql datediff hour how to use datediff in sql select datediff( sql datediff datetime sql server sql datediff hours and minutes 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