Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

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
0
VBdotnet2005
Asked:
VBdotnet2005
2 Solutions
 
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 BSenior 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
 
Monica PSoftware 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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now