total of days in sql

I want to get the total days in sql from two different dates.

2011-06-02 15:00:00.000
2011-11-02 15:00:00.000
VBdotnet2005Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Monica PConnect With a Mentor Software DeveloperCommented:
DAy difference

SELECT DATEDIFF(day,'2008-08-05','2008-06-05') AS DiffDate



minutes
-----------------
SELECT DATEDIFF(MI,'2011-10-14 02:18:58' , '2011-10-14 02:19:01') AS MIN_DIFF


if need accuracy in seconds too then

seconds
----------------
SELECT DATEDIFF(second,'2011-10-14 02:18:58' , '2011-10-14 02:19:01')/60.0 AS MIN_DIFF




get all from one using SP

select
        *,
        Days          = datediff(dd,0,DateDif),
        Hours         = datepart(hour,DateDif),
        Minutes       = datepart(minute,DateDif),
        Seconds       = datepart(second,DateDif),
        MS            = datepart(ms,DateDif)
from
        (
        select
                DateDif = EndDate-StartDate,
                aa.*
        from
                (  -- Test Data
                Select
                        StartDate = convert(datetime,'20130715 02:44:37.923'),
                        EndDate   = convert(datetime,'20130730 13:24:45.837')
                ) aa
        ) a
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT DATEDIFF(dd, '2011-06-02', '2011-11-02' )
0
 
Simone BSenior E-Commerce AnalystCommented:
SELECT DATEDIFF(d,'2011-06-02 15:00:00.000','2011-11-02 15:00:00.000')
0
 
Simone BConnect With a Mentor Senior E-Commerce AnalystCommented:
When you use d or dd, the time is ignored. If you want to round to the closest number of days, you can include the time, calculated the difference in hours, then divide by 24:

Here's an example using different time values. It will give you 152 instead of 153:

SELECT DATEDIFF(hh,'2011-06-02 23:00:00.000','2011-11-02 00:00:00.000')/24
0
 
mimran18Commented:
Select datediff(day,'2011-06-02 15:00:00.000','2011-11-02 15:00:00.000') +1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.