Post reply

Urgent help need to verify DateDiff is correct?

  • March 7, 2013 at 2:35 pm

    #272081

    Hi Friends,

    my requirement is-

    "SmartLaborII Work Order End Date" is NOT greater than "SmartLaborII Previous Work Order End Date"]-

    and the code I'm using is-

    //

    datediff(day,[WorkForce_WorkOrder].[Work Order End Date_WO]

    ,[WorkForce_WorkOrder].[Previous Work Order End Date_WO] )

    >= '0'

    //

    Since I'm new to TSQL world, wanted to know if my above code for my requirement is correct ? if not what should be the right code and why please?

    Kind Regards

    Dhananjay

  • March 7, 2013 at 2:46 pm

    #1594707

    If they are DATE or DATETIME data types why not just do this?

    [WorkForce_WorkOrder].[Work Order End Date_WO] <= [WorkForce_WorkOrder].[Previous Work Order End Date_WO]

  • March 7, 2013 at 2:48 pm

    #1594708

    I would prefer datediff since that way not dependent of type of date format in DB hence need help please:-)

  • March 7, 2013 at 2:57 pm

    #1594711

    dhananjay.nagarkar (3/7/2013)

    I would prefer datediff since that way not dependent of type of date format in DB hence need help please:-)

    If the columns are defined as one of the date data types, you don't need to use DATEDIFF. You can compare dates just like you can compare integers values.

  • March 7, 2013 at 3:05 pm

    #1594715

    Hi Lynn,

    that will not help since in my case one of the dates can remian NULL hence if that's the case then it will not work using > , = etc; hence datediff

    thanks

    Dhananjay

  • March 7, 2013 at 3:16 pm

    #1594720

    dhananjay.nagarkar (3/7/2013)

    Hi Lynn,

    that will not help since in my case one of the dates can remian NULL hence if that's the case then it will not work using > , = etc; hence datediff

    thanks

    Dhananjay

    If either if the date values is NULL, then it will fail the test either way, with or without DATEDIFF.

    You need to explain what you want to happen if one or both of the values is NULL.

  • March 7, 2013 at 3:25 pm

    #1594725

    observe:

    declare @TestDate datetime;

    select datediff(dd,getdate(),@TestDate)

    select datediff(dd,@TestDate,getdate())

    set @TestDate = '20130318';

    select datediff(dd,getdate(),@TestDate)

    select datediff(dd,@TestDate,getdate())

    -----------

    NULL

    (1 row(s) affected)

    -----------

    NULL

    (1 row(s) affected)

    -----------

    11

    (1 row(s) affected)

    -----------

    -11

    (1 row(s) affected)

  • March 7, 2013 at 6:21 pm

    #1594764

    Thanks Lynn you are right

Viewing 8 posts - 1 through 7 (of 7 total)

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