Sql: Difference Between Two Dates

SQL query to select dates between two dates

you should put those two dates between single quotes like..

select Date, TotalAllowance from Calculation where EmployeeId = 1
             and Date between '2011/02/25' and '2011/02/27'

or can use

select Date, TotalAllowance from Calculation where EmployeeId = 1
             and Date >= '2011/02/25' and Date <= '2011/02/27'

keep in mind that the first date is inclusive, but the second is exclusive, as it effectively is '2011/02/27 00:00:00'

Get difference between two dates in Years

So far following query seems to be working okay. My mistake was I dividing by 12 instead of 12.0 for rounding to work correctly. Who knew! :

select 
Round((DATEDIFF(mm, '01/01/2016', '07/1/2017')
    + CASE WHEN abs(DATEPART(day,  '01/01/2016') - DATEPART(day, '06/30/2017')) > 15 THEN 1 ELSE 0 END) / 12.0, 0) 

T-SQL - Total difference between two dates

You can try this below. I have created the Total label as OrderNumber + Total for ordering.

SELECT  
   CAST(O.OrderNumber AS VARCHAR) +  ' Total' OrderNumber,
    MAX(DATEDIFF(DAY,O.OrderDate,OA.EventDate)) AS [Day-Diff]
FROM #Orders O

INNER JOIN #Order_Audit OA ON OA.OrderNumber = O.OrderNumber
GROUP BY CAST(O.OrderNumber AS VARCHAR) +  ' Total'

UNION ALL

SELECT  
   CAST(O.OrderNumber AS VARCHAR) OrderNumber,
   NULL AS [Day-Diff]
FROM #Orders O

INNER JOIN #Order_Audit OA ON OA.OrderNumber = O.OrderNumber

ORDER BY 1

Average difference between two dates, grouped by a third field?

You don't specify the granularity you want for the diff. This does it in days:

select username, avg(end_date - start_date) as avg_days
from mytable
group by username

If you want the difference in seconds, use datediff():

select username, avg(datediff(ss, start_date, end_date)) as avg_seconds
...

datediff can measure the diff in any time unit up to years by varying the first parameter, which can be ss, mi, hh, dd, wk, mm or yy.

Difference between two dates in HSQLDB

If you're using embedded HSQLDB in LO Base then it's version 1.8. From http://www.hsqldb.org/doc/1.8/guide/guide.html:

DATEDIFF(string, datetime1, datetime2)

returns the count of units of time elapsed from datetime1 to datetime2. The string indicates the unit of time and can have the following values 'ms'='millisecond', 'ss'='second','mi'='minute','hh'='hour', 'dd'='day', 'mm'='month', 'yy' = 'year'. Both the long and short form of the strings can be used.

So the argument can be either string 'dd' or 'day':

SELECT DATEDIFF ('day', CURRENT_DATE, "Expiry") AS "Days Remaining" FROM "Expirations"

Difference between two dates based on ID and condition

You can use conditional aggregation as follows:

select shipment_number,
       datediff(day, min(case when event_type = 'pickup' then event_date end) 
       - max(case when event_type = 'delivered' then event_date end)  ) as diff
  from your_table
group by shipment_number

---

Related Topics