locked
DateDiff function doesn't return correct difference RRS feed

  • Question

  • This statement has to bring back the difference between 100 and birthdate year. But it doesn't. What the problem is.

    Declare @age AS INT = 100

    SELECT a.BirthDate, DateDiff(yy,@Age,a.BirthDate)
    FROM [dbo].[Employees] a

    Regards,

    CloudsInSky


    CloudsInSky

    Thursday, February 22, 2018 5:31 AM

Answers

  • Hello team,

    I did this and it worked for me:

    SELECT BirthDate, DateAdd(YY,100, [BirthDate])
    FROM [dbo].[Employees]

    I just wanted to find out the 100 birth date.

    Regards,

    CloudsInSky


    CloudsInSky

    Hi CloudsInSky,

    Since you have got your answer, Please close the thread by marking the useful reply as answer.

    Thanks for your contribution.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by cloudsInSky Wednesday, February 28, 2018 4:01 AM
    Tuesday, February 27, 2018 8:17 AM

All replies

  • Syntax for DateDiff is DATEDIFF ( datepart , startdate , enddate ) .

    Second parameter has to be a date to calculate proper diff.

    Thanks,


    Kindly mark the reply as answer if they help

    Thursday, February 22, 2018 5:38 AM
  • Hi,

    Start date of an SSMS is defaulted to '19000101'.

    Which means SELECT DATEDIFF(YY, '19000101', GETDATE()) OR SELECT DATEDIFF(YY, 1 , GETDATE()) they both will give result as 118. Now if you pass 100, then SQL Server engine will consider 100th Day of year 1900 and hence you will get the difference between year 1900 and year part from BirthDate.

    In case this does not answer your question then please post what you are expecting as an output for us to help you better

    Please mark it as an answer if it was helpful


    Sandeep Prajapati

    Thursday, February 22, 2018 5:52 AM
  • Then can we convert 100 to a date?

    If yes, how?

    Regards,

    CloudsInSky


    CloudsInSky

    Thursday, February 22, 2018 5:53 AM
  • Not clear, if your objective to minus some number from a date then you can use a negative  number and use dateadd instead of datediff like below

    Declare @age AS INT = -100

    SELECT DateAdd(yy,@Age,getdate())


    Cheers,

    Amit Tomar

    ---------------------------------------------------

    Please mark this as answer if it solved your query

    Please vote this as helpful if it solved your query

    ---------------------------------------------------

    My Blog My Wiki Page

    Thursday, February 22, 2018 5:58 AM
  • Hello Amit,

    I have BirthDate column and I want to know their birthdate when they become 100 years old. This is what I need.

    Regards,

    CloudsInSky


    CloudsInSky

    Thursday, February 22, 2018 6:17 AM
  • Then can we convert 100 to a date?

    If yes, how?

    Regards,

    CloudsInSky


    CloudsInSky

    what does 100 represent?

    100 is not a valid date value

    Unless it is an offset, converting it to date is meaningless


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, February 22, 2018 6:22 AM
  • But it doesn't. What the problem is.


    DateDiff do return correct value, you pass a wrong date value to the function; 100 is an integer and not a date value.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, February 22, 2018 6:24 AM
  • Hello Amit,

    I have BirthDate column and I want to know their birthdate when they become 100 years old. This is what I need.

    Regards,

    CloudsInSky


    CloudsInSky

    you mean all who is 100 years or older and their Birthdate?

    then 

    SELECT BirthDate,.. (other information)
    FROM Table
    WHERE 
    CASE WHEN MONTH(GETDATE()) > MONTH(BirthDate)
    OR (MONTH(GETDATE()) = MONTH(BirthDate)
    AND DAY(GETDATE()) > DAY(BirthDate))
    THEN DATEDIFF(yy,BirthDate,GETDATE())
    ELSE  DATEDIFF(yy,BirthDate,GETDATE())-1
    END >= 100


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, February 22, 2018 6:25 AM
  • Run below query

    Create table #Employees (EmpName char(5), birthdate datetime)
    insert into #Employees
    select 'A','1918-02-22'
    union
    select 'B','1918-02-22'
    union
    select 'C','1965-12-02'
    union
    select 'D','1990-02-22'
    union
    select 'E','1995-01-12'
    union
    select 'F','1936-02-22'

    SELECT *
    FROM #Employees
    where DateDiff(yy,birthdate,Convert(varchar(10),getdate(),120))=100

    drop table #Employees


    Cheers,

    Amit Tomar

    ---------------------------------------------------

    Please mark this as answer if it solved your query

    Please vote this as helpful if it solved your query

    ---------------------------------------------------

    My Blog My Wiki Page

    Thursday, February 22, 2018 6:53 AM
  • Hello Amit-Tomar,

    Can you please explain what is going on with: where DateDiff(yy,birthdate,Convert(varchar(10),getdate(),120))=100

    ?

    Where did you bring 120? How did you know that it should be 120 not 130?

    Regards,

    CloudsInSky


    CloudsInSky

    Sunday, February 25, 2018 2:50 AM
  • Where did you bring 120?


    120 is simply the date format parameter for ODBC format, see CAST and CONVERT (Transact-SQL) => Date and Time Styles

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Sunday, February 25, 2018 5:47 AM
  • Hello team,

    I did this and it worked for me:

    SELECT BirthDate, DateAdd(YY,100, [BirthDate])
    FROM [dbo].[Employees]

    I just wanted to find out the 100 birth date.

    Regards,

    CloudsInSky


    CloudsInSky

    Monday, February 26, 2018 3:03 AM
  • Hello team,

    I did this and it worked for me:

    SELECT BirthDate, DateAdd(YY,100, [BirthDate])
    FROM [dbo].[Employees]

    I just wanted to find out the 100 birth date.

    Regards,

    CloudsInSky


    CloudsInSky

    Hi CloudsInSky,

    Since you have got your answer, Please close the thread by marking the useful reply as answer.

    Thanks for your contribution.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by cloudsInSky Wednesday, February 28, 2018 4:01 AM
    Tuesday, February 27, 2018 8:17 AM