DateDiff for only @p_start_date and @p_enddate

  • So I am trying to get the datediff for parameters selected, even though the 2 dates could span 60 days, for an example I have 2 dates available(2018-01-05 11:08:00) and unavailable (2017-10-30 05:05:00) I only want the datediff where @p_start_date = 2017-12-01 and @p_end_date = 2017-12-31 those are the selected parameters.

  • What "DateDiff" are you trying to get?  The number of days, weeks, months, years, hours, seconds, etc?

    I suppose the easiest way to help you would be if you could post some DDL of your source data, such as:

    DECLARE @tmp TABLE (dates DATETIME)
    INSERT INTO @tmp
    VALUES (GETDATE()), (DATEADD(days,-1,GETDATE())

    And your expected output.  Basically, you haven't really told us what dates you are comparing to get a datediff of.  Is DateDiff a column or do you mean the actual SQL function "DATEDIFF", which requires 2 dates as input and gives you the difference between them?

    If you just want the datediff on 2 input parameters, then something like this:

    DECLARE @p_start_date DATETIME = CAST('2017-12-01 00:00:00' AS DATETIME);
    DECLARE @p_end_date DATETIME = CAST('2017-12-31 00:00:00' AS DATETIME);
    SELECT
    DATEDIFF( DAY
    , @p_start_date
    , @p_end_date
    );

    You say you have 2 dates, Available and Unavailable, but I don't understand what those are telling me or how they are stored in your database.  Are "Available" and "Unavailable" columns in the table?  Are you wanting to compare the @p_start_date and @p_end_date to one or both of these columns?

  • I am trying to get the hours difference.

  • DECLARE @p_start_date DATETIME = CAST('2017-12-01 00:00:00' AS DATETIME);
    DECLARE @p_end_date DATETIME = CAST('2017-12-31 00:00:00' AS DATETIME);
    SELECT
    DATEDIFF( HOUR
    , @p_start_date
    , @p_end_date
    );
  • cbrammer1219 wrote:

    So I am trying to get the datediff for parameters selected, even though the 2 dates could span 60 days, for an example I have 2 dates available(2018-01-05 11:08:00) and unavailable (2017-10-30 05:05:00) I only want the datediff where @p_start_date = 2017-12-01 and @p_end_date = 2017-12-31 those are the selected parameters.

    So, since you want hours, what do you want to do with the end date?  Include all the hours for that end date or none of them?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • @cbrammer1219:

    This is pure undeluted guesswork, since you haven't provided the requested input and output data samples yet.

    I'm imagening a scenario, where products may be unavailable for periods of time (i.e. not on stock).

    DROP TABLE IF EXISTS #products;
    DROP TABLE IF EXISTS #AvailabilityStatus;
    create table #products (
    ItemId varchar(20) PRIMARY KEY,
    ItemDescription varchar(80)
    );
    create table #AvailabilityStatus (
    ItemId varchar(20) not null,
    StatusCode char(1) not null,
    StatusChangeDate datetime not null,
    constraint PK_AvailabilityStatus primary key (
    ItemId asc,
    StatusCode asc,
    StatusChangeDate asc
    )
    );

    insert into #products (ItemId, ItemDescription) -- some products, whatever...
    values ('AK99','Wine Combo Pack #1, 2015'),
    ('BK230','Wine Combo Pack #2, 2015'),
    ('WF882B','Wine Combo South Africa 2016'),
    ('BI1993','Wine Combo Pack Chile 2016'),
    ('CI022','The Wine & Dine Book'),
    ('CI023','Treats for your Guests, Book'),
    ('UJ2757','Belgian Chocolade, 200 pieces')
    insert into #AvailabilityStatus (ItemId, StatusCode, StatusChangeDate) -- some recorded changes to product availability
    values ('AK99','U','2017-10-27 17:03:17'),
    ('AK99','A','2017-11-04 14:16:42'),
    ('AK99','U','2018-04-27 08:03:22'),
    ('AK99','A','2018-06-04 14:03:47'),
    ('AK99','U','2020-06-26 08:52:26'),
    ('BK230','U','2017-10-30 05:05:00'),
    ('BK230','A','2018-01-05 11:08:00'),
    ('CI022','U','2017-08-22 17:05:59'),
    ('CI022','A','2017-09-12 09:32:17'),
    ('UJ2757','U','2019-12-09 23:18:42'),
    ('UJ2757','A','2019-12-30 07:33:49');

    -- Define period of interest...
    declare
    @p_start_date date = '2017-12-01',
    @p_end_date date = '2017-12-31';

    -- Return data with calculated hours of unavailability
    with UnavailablePeriods AS (
    select
    as_u.ItemId,
    as_u.StatusChangeDate AS UnavailableDateTime,
    as_a.StatusChangeDate AS AvailableDateTime
    from #AvailabilityStatus as_u
    outer apply (
    select top(1) StatusChangeDate
    from #AvailabilityStatus
    where ItemId=as_u.ItemId
    and StatusCode='A'
    and StatusChangeDate > as_u.StatusChangeDate
    order by StatusChangeDate asc
    ) as_a
    where as_u.StatusCode='U'
    )
    select
    p.ItemId,
    p.ItemDescription,
    up.UnavailableDateTime,
    up.AvailableDateTime,
    datediff(hour,up.UnavailableDateTime,up.AvailableDateTime) as HoursOfUnavailability
    from #products p
    left join UnavailablePeriods up on up.ItemId=p.ItemId
    --where up.UnavailableDateTime between @p_start_date and @p_end_date
    --and up.AvailableDateTime is null or up.AvailableDateTime <= @p_end_date
    order by
    ItemId,
    UnavailableDateTime

    Obviously I don't know if this is anything like what you have, but it might help you describe what you have and what you want.

    Also, I haven't done much testing. I think the output is correct given the input, but if not bear with me. 🙂

    PS. The commented out "where section" is what I think you want to use your parameters for, but I'm not sure.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply