Need to determine number of days between dates - getting strange return

• jcobb 20350

  Default port

  Points: 1438

  More actions

  February 5, 2017 at 8:42 am

  #334777

  I'm trying to develop a purchasing calendar. I have a starting date that is static and a number of cycle days that are also static. If today's date is greater than the start date + cycle days, I need to know the number of days. If it is less, I need start + cycle. I have this so far;

  DECLARE @SD datetime, @cycle numeric  SET @SD = '1-1-17' SET @cycle = 21

  SELECT CASE WHEN GETDATE() > @SD + @cycle THEN DATEDIFF(day, @SD, GETDATE())
ELSE @cycle + @SD END AS 'NOD' /* NOD = Next Order Date*/

  SELECT DATEDIFF(day, @SD, GETDATE())

  When I run the first SELECT I get a weird value of 1900-02-05 00:00:00. I expected to see what I get when I run the second SELECT which is 35. 35 is the difference between GETDATE and 1-1-17. Why am I getting the weird 1900 date and/or how do I get the first query to return 35?

• Phil Parkin

  SSC Guru

  Points: 246937

  More actions

  February 5, 2017 at 9:02 am

  #1926764

  jcobb 20350 - Sunday, February 5, 2017 8:42 AM

  I'm trying to develop a purchasing calendar. I have a starting date that is static and a number of cycle days that are also static. If today's date is greater than the start date + cycle days, I need to know the number of days. If it is less, I need start + cycle. I have this so far;

  DECLARE @SD datetime, @cycle numeric  SET @SD = '1-1-17' SET @cycle = 21

  SELECT CASE WHEN GETDATE() > @SD + @cycle THEN DATEDIFF(day, @SD, GETDATE())
  ELSE @cycle + @SD END AS 'NOD' /* NOD = Next Order Date*/

  SELECT DATEDIFF(day, @SD, GETDATE())

  When I run the first SELECT I get a weird value of 1900-02-05 00:00:00. I expected to see what I get when I run the second SELECT which is 35. 35 is the difference between GETDATE and 1-1-17. Why am I getting the weird 1900 date and/or how do I get the first query to return 35?

  If I format your code slightly differently, the problem becomes obvious:

  DECLARE @SD DATETIME,
  @cycle NUMERIC;

  SET @SD = '1-1-17';
SET @cycle = 21;

  SELECT NOD = CASE
      WHEN GETDATE() > @SD + @cycle THEN
       DATEDIFF(DAY, @SD, GETDATE())
      ELSE
       @cycle + @SD
     END; /* NOD = Next Order Date*/

  SELECT DATEDIFF(DAY, @SD, GETDATE());

  Part 1 of your CASE returns an INT, part 2 returns a Datetime. Datetime takes precedence, so that's what you get.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• jcobb 20350

  Default port

  Points: 1438

  More actions

  February 5, 2017 at 10:34 am

  #1926766

  Thanks Phil. You got me on the right path. This is what I'm going to try out.

  DECLARE @SD datetime, @ord_cycle numeric

  SET @SD = '1-1-17' SET @ord_cycle = 30
  

  /* Get number of days beyond the start date*/

  ; WITH D AS

  (SELECT DATEDIFF(day,@SD, GETDATE()) AS 'Last')
  

  SELECT CASE WHEN D.Last > @ord_cycle THEN

         /* When CTE days are greater than the cycle days, divide by the cycle to see

                how many periods have passed, then round up with CEILING.

                The round up * the cycle * original start date, gives the next order date */

         CEILING(D.Last/@ord_cycle) * @ord_cycle + @SD

                /* If CTE days are less than cycle use cycle + orinial start date to get next order date.

                       This will only happen once in the life cycle */

                       ELSE @ord_cycle + @SD END AS 'DD'

  FROM D

  Edit: Above should be + start date not * as shown. Correction: The round up * the cycle + original start date

• Shankar Walvekar

  SSC Veteran

  Points: 278

  More actions

  February 6, 2017 at 4:36 am

  #1926824

  Look at below code, see if it give the correct results
  DECLARE @SD DATETIME
    ,@cycle NUMERIC

  SET @SD = '1-1-17'
SET @cycle = 21

  SELECT CASE
        WHEN GETDATE() > @cycle + @SD
            THEN GETDATE() + @cycle
        ELSE @cycle + @SD
        END AS 'NOD' /* NOD = Next Order Date*/

  Thanks & regards,
  Shankar Walvekar
  

• paulo.margarido

  Ten Centuries

  Points: 1245

  More actions

  February 7, 2017 at 9:20 am

  #1927118

  i was trying other aproach
  
DECLARE @SD datetime, @ord_cycle numeric
SELECT @SD = '2017-03-01', @ord_cycle = 21
SELECT    ISNULL(
            NULLIF(
                 (CEILING(DATEDIFF(day, @SD, GETDATE()) / @ord_cycle))
            , 0)
        , 1)
        * @ord_cycle + @SD


  ... doesn't go in the right way ... 🙁
  but didn't give up and end up with this, check it out

  
DECLARE @SD datetime, @ord_cycle numeric
SELECT @SD = '2017-03-01', @ord_cycle = 21
SELECT    ISNULL(
            NULLIF(
                 (ABS(CEILING(DATEDIFF(day, @SD, GETDATE()) / @ord_cycle))
                     + CEILING(DATEDIFF(day, @SD, GETDATE()) / @ord_cycle)) / 2
            , 0)
        , 1)
        * @ord_cycle + @SD


  some credits to The Dixie Flatline in this post

• jcobb 20350

  Default port

  Points: 1438

  More actions

  February 9, 2017 at 11:16 am

  #1927608