Number of Days Between Two Dates - Ansi SQL

Number of days between two dates - ANSI SQL

ANSI SQL-92 defines DATE - DATE as returning an INTERVAL type. You are supposed to be able to extract scalars from INTERVALS using the same method as extracting them from DATEs using – appropriately enough – the EXTRACT function (4.5.3).

<extract expression> operates on
a datetime or interval and returns an
exact numeric value representing the
value of one component of the datetime
or interval.

However, this is very poorly implemented in most databases. You're probably stuck using something database-specific. DATEDIFF is pretty well implemented across different platforms.

Here's the "real" way of doing it.

SELECT EXTRACT(DAY FROM DATE '2009-01-01' - DATE '2009-05-05') FROM DUAL;

Good luck!

Calculate separate No of Week and Days between 2 Dates

If you are using MSSQL (Transact-SQL) you can use the datediff-method:

DATEDIFF ( datepart , startdate , enddate )  

whic in your case would be

DATEDIFF(day,10-01-2018, 19-01-2018);

And then divide the days with 7 and take the remainer as days.

More info at MS Docs

Calculating the days between 2 dates

Here's one approach:

WITH some_cte AS (
  SELECT Account
       , DCBalance
       , Credit
       , Debit
       , CASE
           WHEN TxDate >= DateAdd(dd, -30, GetDate()) THEN '0-30'
           WHEN TxDate >= DateAdd(dd, -60, GetDate()) THEN '30-60'
           WHEN TxDate >= DateAdd(dd, -90, GetDate()) THEN '60-90'
           ELSE '90+'
         END AS date_period
  ...
  WHERE TxDate >= DateAdd(dd, -90, GetDate())
)
SELECT Account
     , DCBalance
     , date_period
     , Sum(Credit) - Sum(Debit) As balance
FROM   some_cte
GROUP
    BY Account
     , DCBalance
     , date_period
;

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'

Count days between dates in two rows based on condition

updated after clarifications in the PO

let'say that it needs to do a sort of aggregation on data called ranking, that is a type of classification based on numbering in succession order tbale's rows.

In our case the order is given by the Orders dates.

This is a quite cross-dbms solution (date fields are suppased to be Datetime type and DATEDIFF is a function of MySql) so I think that you can adapt to your dbms quite easily.

You can try the sql on Sql Fiddle at http://sqlfiddle.com/#!9/290e9

Table

CREATE TABLE Orders
    (`ID` int, `IDToner` int, `Quantity` int, `Location` int, `Order_date` Date, `Send_date` Date)
;

INSERT INTO Orders
    (`ID`, `IDToner`, `Quantity`, `Location`, `Order_date`, `Send_date`)
VALUES
    (1, 2, 1, 55, STR_TO_DATE('20.01.2015','%d.%m.%Y'), STR_TO_DATE('26.01.2015','%d.%m.%Y')),
    (2, 2, 1, 41, STR_TO_DATE('22.02.2015','%d.%m.%Y'), STR_TO_DATE('26.02.2015','%d.%m.%Y')),
    (3, 2, 1, 35, STR_TO_DATE('23.02.2015','%d.%m.%Y'), STR_TO_DATE('26.02.2015','%d.%m.%Y')),
    (4, 5, 1, 77, STR_TO_DATE('25.02.2015','%d.%m.%Y'), STR_TO_DATE('25.02.2015','%d.%m.%Y')),
    (5, 5, 1, 77, STR_TO_DATE('25.04.2015','%d.%m.%Y'), STR_TO_DATE('25.04.2015','%d.%m.%Y')),
    (6, 5, 1, 77, STR_TO_DATE('25.06.2015','%d.%m.%Y'), STR_TO_DATE('25.06.2015','%d.%m.%Y')),
    (7, 5, 1, 77, STR_TO_DATE('25.08.2015','%d.%m.%Y'), STR_TO_DATE('25.08.2015','%d.%m.%Y')),
    (8, 2, 1, 55, STR_TO_DATE('25.02.2015','%d.%m.%Y'), STR_TO_DATE('26.02.2015','%d.%m.%Y'))
;

Query

SELECT 
        ID,
        ID_Toner,
        Quantity,
        Location, 
        Order_date, 
        Send_date,        
        days_from_previous_order
FROM(
  SELECT 
        current_ID AS ID,
        current_IDToner AS ID_Toner,
        current_Quantity AS Quantity,
        current_Location AS Location, 
        current_Send_Date AS Send_date,        
        current_Order_Date AS Order_date, 
        previous_Order_Date,
        COALESCE(DATEDIFF(current_Order_Date, previous_Order_Date),0) AS days_from_previous_order
  FROM(
        SELECT 
              TabOrdersRanking_currents.ID AS current_ID,
              TabOrdersRanking_currents.IDToner AS current_IDToner,
              TabOrdersRanking_currents.Quantity AS current_Quantity,
              TabOrdersRanking_currents.Location AS current_Location, 
              TabOrdersRanking_currents.Send_Date AS current_Send_Date,        
              TabOrdersRanking_currents.Order_Date AS current_Order_Date, 
              TabOrdersRanking_previous.Order_Date AS previous_Order_Date 
        FROM(
          SELECT Orders.*, @rank1 := @rank1 + 1 rank
          FROM Orders 
          ,(Select @rank1 := 0) r1   
          order by location, order_date
        ) TabOrdersRanking_currents
        LEFT JOIN( 
          SELECT Orders.*, @rank2 := @rank2 + 1 rank
          FROM Orders 
          ,(Select @rank2 := 0) r2   
          order by location, order_date
        ) TabOrdersRanking_previous
        on TabOrdersRanking_currents.Location = TabOrdersRanking_previous.Location 
        and TabOrdersRanking_currents.rank - TabOrdersRanking_previous.rank = 1      
  ) TabOrdersSuccessionRanking
) TabWithDaysFromPrevious;

How can I get the number of days between 2 dates in Oracle 11g?

I figured it out myself. I need

select extract(day from sysdate - to_date('2009-10-01', 'yyyy-mm-dd')) from dual

ANSI SQL for subtracting a day

There's no publicly available version for the ANSI SQL 2003 specs, but no vendor fully support it to begin with. Any how, there are enough differences in the syntax between these two to write different code for each.

MySQL has a DATE_ADD function:

SELECT DATE_ADD(CURRENT_DATE, INTERVAL 30 DAY)
FROM dual;

SQL Server has a DATEADD function (without the underscore):

SELECT DATEADD(DAY, 30, CAST(GETDATE() AS date))

GETDATE() return both date & time. The cast strips the time portion off.

---

Related Topics