CODEWITHSUNDEEP
sqlsql-server
Shmewnix
Shmewnix

Reputation: 1573

How to find the number of days between two dates

I have a basic query:

SELECT dtCreated
    , bActive
    , dtLastPaymentAttempt
    , dtLastUpdated
    , dtLastVisit
FROM Customers
WHERE (bActive = 'true') 
    AND (dtLastUpdated > 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?

Upvotes: 18

Views: 207614

Answers (10)

vasundhara sannidhi
vasundhara sannidhi

Reputation: 1

DATEDIFF('Start Date', 'End Date')

In MySQL, This gave me the difference in Days.

Upvotes: 0

Code
Code

Reputation: 749

DECLARE @Firstdate DATE='2016-04-01',
 @LastDate DATE=GETDATE(),/*get today date*/
 @resultDay int=null

SET @resultDay=(SELECT DATEDIFF(d, @Firstdate, @LastDate))
PRINT @resultDay

Upvotes: 0

user11565652
user11565652

Reputation:

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

Upvotes: 0

Mahib
Mahib

Reputation: 4063

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 > CONVERT(DATETIME, '2012-01-0100:00:00', 102))

Upvotes: 0

Amitesh Bharti
Amitesh Bharti

Reputation: 15775

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 > '2012-01-01 00:00:00')

Tested on mysql server 5.7.17

Upvotes: 0

Er Ketan Vavadiya
Er Ketan Vavadiya

Reputation: 283

DATEDIFF(d, 'Start Date', 'End Date')

do it

Upvotes: 1

user1498339
user1498339

Reputation: 629

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 > CONVERT(DATETIME, '2012-01-0100:00:00', 102))

Upvotes: 1

idodev
idodev

Reputation: 334

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 > CONVERT(DATETIME, '2012-01-0100:00:00', 102))

EDIT: IF using MySQL you omit the 'd' leaving you with

DATEDIFF(dtLastUpdated, dtCreated) AS Difference

Upvotes: 2

Taryn
Taryn

Reputation: 247860

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 > CONVERT(DATETIME, '2012-01-0100:00:00', 102))

Upvotes: 42

Forte L.
Forte L.

Reputation: 2812

To find the number of days between two dates, you use: 

DATEDIFF ( d, startdate , enddate )

Upvotes: 10

Related Questions