Post reply

Urgent help with Date comparison in TSQL

  • July 2, 2013 at 9:23 am

    #279797

    Dear friends,

    I have requirement - to retrieve order from database that were created before 12:00 AM GMT on June 3, 2013.

    I'm using datediff but it is giving me couple records that were created on June 3rd 2.59 AM also,

    below is my code of line-

    datediff(second,[WorkForce_JobPosting].[Job Posting Create Date_JP] ,'2013-06-03 12:00:00.000')> 0 )

    note the date format for thhis create date in DB is 'date time'

    thanks

    Dhananjay

  • July 2, 2013 at 9:32 am

    #1629180

    WHERE [WorkForce_JobPosting].[Job Posting Create Date_JP] < '2013-06-03 12:00:00.000'

    Why go complex when there's a simple method?

    btw, 12:00:00 is mid day. That is what you want?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 2, 2013 at 9:38 am

    #1629185

    dhananjay.nagarkar (7/2/2013)

    Dear friends,

    I have requirement - to retrieve order from database that were created before 12:00 AM GMT on June 3, 2013.

    I'm using datediff but it is giving me couple records that were created on June 3rd 2.59 AM also,

    below is my code of line-

    datediff(second,[WorkForce_JobPosting].[Job Posting Create Date_JP] ,'2013-06-03 12:00:00.000')> 0 )

    note the date format for thhis create date in DB is 'date time'

    thanks

    Dhananjay

    '2013-06-03 12:00:00.000' is noon on 6/3 so any 6/3 date prior to NOON on that date will be in the result set.

    It should be

    --midnight the next day

    SELECT datediff(second,'2013-06-03 02:59:00.000' ,'2013-06-03 00:00:00.000')

    --or

    --the last second of the current day

    SELECT datediff(second,'2013-06-03 02:59:00.000' ,'2013-06-02 23:59:59.999')

    These both return negative values as you are expecting for that particular date input.

     

  • July 2, 2013 at 9:48 am

    #1629191

    Hi friend,

    i want it before s before 12:00 AM GMT on June 3, 2013.

    can i use it as -

    [WorkForce_JobPosting].[Job Posting Create Date_JP] < '2013-06-03'

    ?

    thanks

    Dhananjay

  • July 2, 2013 at 9:50 am

    #1629193

    Dear Gail,

    I want it before 12.00 AM GMT on June 3rd.

    so can I use - [WorkForce_JobPosting].[Job Posting Create Date_JP] < '2013-06-03'? or if not what do you suggest please?

    thanks

    Dhananjay

  • July 2, 2013 at 9:53 am

    #1629194

    Dear Gail,

    I want it before 12.00 AM GMT on June 3rd.

    so can I use - [WorkForce_JobPosting].[Job Posting Create Date_JP] < '2013-06-03'? or if not what do you suggest please?

    thanks

    Dhananjay

  • July 2, 2013 at 9:58 am

    #1629196

    Mid day or mid night? Which are you calling '12:00 AM'?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 2, 2013 at 10:19 am

    #1629204

    Hi Gail

    Midnight 12.00 AM not Mid Noon since Noon is 12.00 PM

    thanks

    Dhanajay

  • July 2, 2013 at 10:29 am

    #1629206

    In that case '2013-06-03 00:00:00' as what you originally had ('2013-06-03 12:00:00') is noon, not midnight.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 2, 2013 at 12:33 pm

    #1629249

    So Gail this code should work to get me Orders Before June 3rd - 12.00 AM Midnight-

    [WorkForce_JobPosting].[Job Posting Create Date_JP] < '2013-06-03'

    correct?

  • July 2, 2013 at 12:36 pm

    #1629251

    Should do. Plus allows for potentially better performance than the datediff you had.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 2, 2013 at 9:00 pm

    #1629342

    thanks all for this awesome help the code worked for me

Viewing 12 posts - 1 through 11 (of 11 total)

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