How to find the number of days between two dates

Question

I have a basic query   SELECT dtCreated       bActive       dtLastPaymentAttempt       dtLastUpdated       dtLastVisit FROM Customers WHERE  bActive    true        AND  dtLastUpdated  gt  CONVERT DATETIME   2012-01-0100 00 00   102     I want to add another column to the output    lets call it  Difference  to find out the number of days between  dtcreated  and  dtlastupdated   So for example if record 1 has a dtcreated of 1 1 11 and dtlastupdated is 1 1 12 the  Difference  column would be  365      Can this be accomplished in a query

User · Answer

To find the number of days between two dates  you use    DATEDIFF   d  startdate   enddate

User · Answer

The DATEDIFF function is use to calculate the number of days between the required date   Example if you are diff current date from given date in string format  SELECT     DATEDIFF CURDATE   STR TO DATE  01 11 2017     m  d  Y    AS days FROM consignments WHERE code    1610000154    Here  STR TO DATE      Take a string and returns a date specified by a format mask   For your example     SELECT dtCreated       bActive       dtLastPaymentAttempt       dtLastUpdated       dtLastVisit       DATEDIFF dtLastUpdated  dtCreated  as Difference FROM Customers WHERE  bActive    true        AND  dtLastUpdated  gt   2012-01-01 00 00 00     Tested on mysql server 5 7 17

User · Answer

If you are using MySQL there is the DATEDIFF function which calculate the days between two dates   SELECT dtCreated       bActive       dtLastPaymentAttempt       dtLastUpdated       dtLastVisit       DATEDIFF dtLastUpdated  dtCreated  as Difference FROM Customers WHERE  bActive    true        AND  dtLastUpdated  gt  CONVERT DATETIME   2012-01-0100 00 00   102

User · Answer

You would use DATEDIFF   declare  start datetime declare  end datetime  set  start    2011-01-01  set  end    2012-01-01   select DATEDIFF d   start   end   results   365   so for your query   SELECT dtCreated       bActive       dtLastPaymentAttempt       dtLastUpdated       dtLastVisit       DATEDIFF d  dtCreated  dtLastUpdated  as Difference FROM Customers WHERE  bActive    true        AND  dtLastUpdated  gt  CONVERT DATETIME   2012-01-0100 00 00   102

User · Answer

DECLARE  Firstdate DATE  2016-04-01     LastDate DATE GETDATE     get today date     resultDay int null  SET  resultDay  SELECT DATEDIFF d   Firstdate   LastDate   PRINT  resultDay

User · Answer

Get No of Days between two days  DECLARE  date1 DATE  2015-01-01     date2 DATE  2019-01-01     Total int null  SET  Total  SELECT DATEDIFF DAY   date1   date2   PRINT  Total

User · Answer

DATEDIFF d   Start Date    End Date     do it

User · Answer

As  Forte L  mentioned you can do the following as well   SELECT dtCreated       bActive       dtLastPaymentAttempt       dtLastUpdated       dtLastVisit        DATEDIFF day  dtCreated  dtLastUpdated  Difference  FROM Customers WHERE  bActive    true        AND  dtLastUpdated  gt  CONVERT DATETIME   2012-01-0100 00 00   102

User · Answer

I would use the DATE DIFF function to provide this value as below   SELECT dtCreated       bActive       dtLastPaymentAttempt       dtLastUpdated       dtLastVisit       DATEDIFF d  dtLastUpdated  dtCreated  AS Difference FROM Customers WHERE  bActive    true        AND  dtLastUpdated  gt  CONVERT DATETIME   2012-01-0100 00 00   102     EDIT  IF using MySQL you omit the  d  leaving you with  DATEDIFF dtLastUpdated  dtCreated  AS Difference

[sql] How to find the number of days between two dates

Examples related to sql

Examples related to sql-server