dcsimg
Results 1 to 5 of 5

Thread: Between Operator

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2006
    Posts
    592

    Between Operator

    Between Dates e.g.

    Blah blah WHERE MyDate Between @StartDate and @EndDate

    Say i chose 11/5 to 11/25
    I expect it to returns all records between the two dates but the time to be considered as well. Currently the query is check up until 11/25 12am omitting records written after 12am.

    It should return all records from 11/5 12am THRU 11/25 11:59:59pm

    Thanks

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,353

    Re: Between Operator

    A DateTime value without a time portion is considered to be midnight (12am).

    While specifying @StartDate without a time allows all values from that day, the same does not apply to @EndDate - it will only allow values of exactly midnight.


    There are two easy ways to deal with it, either add the time 11:59:59pm to @EndDate, or convert the field (MyDate) to just the date portion.

    Exactly how you do those things depends on things like what database system and code you are using.

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2006
    Posts
    592

    Re: Between Operator

    I am using SQL Server 2008 (T-SQL)

    Would you be so kind and show me how do i add the time to @EndDate .. should i cast it from varchar or something?

    Thank you so much

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,722

    Re: Between Operator

    WHAAAT? you're using a varchar to hold a date? Ugh... Would you treat your car like a horse? Of course not! So treat a date as a date should be treated... give it the proper respect and love it deserves.

    And then add to it using the dateAdd function... http://msdn.microsoft.com/en-us/library/ms186819.aspx
    Add a day... then subtract a second...

    set @ENDDATE = dateadd(s, -1, dateadd(d, 1, @ENDDATE))

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2006
    Posts
    592

    Re: Between Operator

    No no i am not using varchar to hold a date. I was rather referring to something like following (when you have to merge the date from strings e.g.

    Code:
    SET @MyDate = CAST(CAST(YEAR(GetDate()) AS varchar(4)) + '-' + CAST(MONTH(GetDate()) AS varchar(2)) + '-' + CAST(@MyDay AS varchar(2)) AS DATETIME)
    Ok according your code i understand that you add a DAY and then subtract 1 sec. Actually it makes sense. Will give it a try. Thank you guys

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width