none
subtract dates

    Question

  • how can i subtract dates??? like 10/11/2008 MINUS 9 days=1/11/2008?!?!?!?!
    i want to know this because in my table i have 2 columns: birth date and register date... and i want to find the registers made 15 or more days after the birth...
    Tuesday, April 14, 2009 7:07 PM

Answers

  • Hi Leo,

    Check the following query:

    select *,datediff(day,birthdate,registerDate) from  <TableName> where datediff(day,birthdate,registerDate) >= 15

    Hope it would be helpful to you...
    Kiran (www.ggktech.com) http://developerspride.blogspot.com
    Wednesday, April 15, 2009 12:12 AM
  • Hello,

    For our example I have written 

    SELECT CONVERT(DATETIME,'10 Nov 2008') AS [Date]
                 , DATEADD(Day,-9,'10 Nov 2008') AS [9Day]

    For your original requirement the query shold be like

    SELECT *
    FROM TableName
    WHERE DatDiff(Day,BirthDay,RegisterDay) >= 15

    Hope helpful...


    Pavan http://www.ggktech.com
    Wednesday, April 15, 2009 2:28 AM

All replies

  • Hello,

    Try this

    SELECT GETDATE() [CurrentDate], DATEDIFF(Day,-9, GETDATE()) [9DaysBack]

    Hope helpful...


    Pavan http://www.ggktech.com
    Tuesday, April 14, 2009 7:11 PM
  • Not quite Pavan...
    SELECT GetDate() As current_date
         , DateAdd(dd, -9, GetDate()) As nine_days_ago
    (DateAdd not DateDiff!)
    George
    Tuesday, April 14, 2009 8:37 PM
  • Hi Leo,

    Check the following query:

    select *,datediff(day,birthdate,registerDate) from  <TableName> where datediff(day,birthdate,registerDate) >= 15

    Hope it would be helpful to you...
    Kiran (www.ggktech.com) http://developerspride.blogspot.com
    Wednesday, April 15, 2009 12:12 AM
  • Perhaps something like:

    declare @test table

    ( birthDate datetime,

      registerDate datetime)

    insert into @test

    select '1/1/9', '1/14/9' union all

    select '1/1/9', '1/15/9' union all

    select '1/1/9', '1/16/9' union all

    select '1/1/9', '1/15/9 23:59:59.997' union all

    select '1/1/9 23:59:59.997', '1/16/9' union all

    select '2/1/9', '1/16/9'

     

    select * from @test

    where convert(datetime, convert(char(10), registerDate, 101))

       >= convert(datetime, convert(char(10), dateadd(day, 15, birthDate), 101))

    /* -------- Sample Output: --------

    birthDate               registerDate

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

    2009-01-01 00:00:00.000 2009-01-16 00:00:00.000

    2009-01-01 23:59:59.997 2009-01-16 00:00:00.000

    */

    Also, if you are frequently going to be selecting records from this table on a basis like this you might want to consider adding a computed column to the table so that you at least have the potential of indexing this difference.


    Kent Waldrop Ap09


    Kent Waldrop Ap09
    Wednesday, April 15, 2009 1:04 AM
  • Hello,

    For our example I have written 

    SELECT CONVERT(DATETIME,'10 Nov 2008') AS [Date]
                 , DATEADD(Day,-9,'10 Nov 2008') AS [9Day]

    For your original requirement the query shold be like

    SELECT *
    FROM TableName
    WHERE DatDiff(Day,BirthDay,RegisterDay) >= 15

    Hope helpful...


    Pavan http://www.ggktech.com
    Wednesday, April 15, 2009 2:28 AM