Post reply

BETWEEN alternative

  • July 30, 2015 at 8:52 am

    #319421

    I have a query like

    SELECT DISTINCT S.studyCode AS StudyCode

    ,CL.clientName AS Sponsor

    ,CL.clientCode

    ,OP.operatorName AS Operator

    ,CT.contactName AS Contact

    ,S.modifiedOn AS StudyCreationDate

    FROM Studies S

    INNER JOIN StudiesClients SC ON SC.studyIncId = S.studyIncId

    AND SC.studySqlId = S.studySqlId

    AND SC.isDeleted = 0x0

    LEFT JOIN Clients AS CL ON CL.clientSqlId = SC.clientSqlId

    AND CL.clientIncId = SC.clientIncId

    AND CL.isDeleted = 0x0

    LEFT JOIN StudiesPositions SP ON SP.studySqlId = S.studySqlId

    AND SP.studyIncId = S.studyIncId

    AND SP.isDeleted = 0x0

    INNER JOIN Positions PS ON PS.positionIncId = SP.positionIncId

    AND PS.positionSqlId = SP.positionSqlId

    AND PS.isDeleted = 0x0

    AND PS.positionCode = 'ESM001'

    LEFT JOIN Contacts CT ON CT.contactIncId = SP.contactIncId

    AND CT.contactSqlId = SP.contactSqlId

    AND CT.isDeleted = 0x0

    LEFT JOIN Operators OP ON OP.operatorIncId = SP.operatorIncId

    AND OP.operatorSqlId = SP.operatorSqlId

    AND OP.isDeleted = 0x0

    where S.isDeleted = 0x0 and cl.clientCode= N'C-000000052'

    AND ((S.createdOn between '2015-06-30 00:00:00' and '2015-07-30 00:00:00')

    OR (SP.modifiedOn between '2015-06-30 00:00:00' and '2015-07-30 00:00:00'))

    Here I want to get the data for the date '2015-07-30 00:00:00' also.

    Is there any alternative for 'between' to get this date data also.

  • July 30, 2015 at 9:00 am

    #1816171

    Have you thought about using <= and >=?

    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • July 30, 2015 at 9:07 am

    #1816172

    <= and >= did not give me the required results.

    I tried that way.

  • July 30, 2015 at 9:15 am

    #1816175

    AND ((S.createdOn >='2015-06-30 00:00:00' and S.createdOn < '2015-07-31 00:00:00')

    OR (SP.modifiedOn >='2015-06-30 00:00:00' and SP.modifiedOn <'2015-07-31 00:00:00'))

    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 31, 2015 at 3:44 pm

    #1816578

    I agree with GilaMonster ... DateValue >= @StartDate and DateValue < dateadd(Day,1,@EndDate). If @startDate = '07/01/2015' and @endDate = '07/30/2015' you get all of the end date, including time: 7/30/2015 at 9pm will be returned.

Viewing 5 posts - 1 through 4 (of 4 total)

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