dcsimg
Results 1 to 11 of 11

Thread: How To Use Between Is Select Statement

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2010
    Posts
    472

    How To Use Between Is Select Statement

    A column OrderDateTime in a SQL table store data like below.

    2019-11-01 11:31:31.163

    2019-11-12 09:31:31.163

    2019-11-15 15:31:31.163

    2019-11-22 20:31:31.163

    How to create a SQL sentence to select a date between 2019-11-12 and 2019-11-22?

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,565

    Re: How To Use Between Is Select Statement

    * 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??? *

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,482

    Re: How To Use Between Is Select Statement

    This question has got exactly zero to do with VB.NET. You may have lazily assumed that it did because you're using this SQL in a VB.NET application but the fact that there's no mention of that in your question should have been a clue. SQL is the same no matter the language used to write the application that executes it. This site has a forum dedicated to Database Development and that's exactly what SQL is. Posting to the correct forum prevents pollution of other forums but it also means that you are more likely to get help. Plenty of people who don't use VB.NET can help you with SQL. I have asked the mods to move this thread.

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

    Re: How To Use Between Is Select Statement

    Thread moved to the 'Database Development' forum - which is where you should always post SQL questions (while SQL can be used in VB, it is certainly not specific to VB)

  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,864

    Re: How To Use Between Is Select Statement

    If you have both DATES and TIMES in your DATETIME field, then BETWEEN gets a little problematic when dealing with dates for the low and high.

    This is one of the reasons I always store TIME in a separate field (unless the field is a time-stamp in which case the date/time is really together).

    Are you having problems with getting activity up to midnight of the end date of your between?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,366

    Re: How To Use Between Is Select Statement

    MS SQL

    create table #Dates(OrderDateTime datetime)
    insert into #Dates(OrderDateTime) values('2019-11-01 11:31:31.163')
    insert into #Dates(OrderDateTime) values('2019-11-12 09:31:31.163')
    insert into #Dates(OrderDateTime) values('2019-11-15 15:31:31.163')
    insert into #Dates(OrderDateTime) values('2019-11-22 20:31:31.163')

    select * from #Dates where convert(varchar,orderdatetime,101) between '11/12/2019' and '11/22/2019'
    Please remember next time...elections matter!

  7. #7
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,864

    Re: How To Use Between Is Select Statement

    @tyson - yes that works, although it kills any use of date indexes you might have...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  8. #8
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,366

    Re: How To Use Between Is Select Statement

    Quote Originally Posted by szlamany View Post
    @tyson - yes that works, although it kills any use of date indexes you might have...
    I don't know how to finish getting the last few seconds before midnight.

    create table #Dates(OrderDateTime datetime)
    insert into #Dates(OrderDateTime) values('2019-11-01 11:31:31.163')
    insert into #Dates(OrderDateTime) values('2019-11-12 09:31:31.163')
    insert into #Dates(OrderDateTime) values('2019-11-15 15:31:31.163')
    insert into #Dates(OrderDateTime) values('2019-11-22 20:31:31.163')

    select * from #Dates where orderdatetime between '2019-11-12 00:00:00.01' and '2019-11-22 23:59:59.163'
    Last edited by TysonLPrice; Nov 26th, 2019 at 10:27 AM.
    Please remember next time...elections matter!

  9. #9
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,366

    Re: How To Use Between Is Select Statement

    I normally take the last date supplied (just ask for the date) and do a dateAdd (DAYS,1,suppliedDate) and use >= and <
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,371

    Re: How To Use Between Is Select Statement

    I stand with Gary. #DateFunctionsFTW.

    Where MyField >= @SartDate
    And MyField < DateAdd(Days, 1, @EndDate)

    Another approach is to cast the Field to a Date if you really want to use a Between:-
    Where Cast(MyField as Date) Between @StartDate and @EndDate
    The casting removes the time element.


    Casting is heavy which is why I prefer the first approach. I'm also not sure if the second approach would mean the indexes wouldn't get used but I think they would.
    I definitely try to avoid getting strings involved. They're both heavy and unreliable.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  11. #11
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,435

    Re: How To Use Between Is Select Statement

    Quote Originally Posted by FunkyDexter View Post
    I stand with Gary. #DateFunctions FTW.
    Ack.

    Quote Originally Posted by FunkyDexter View Post
    Where MyField >= @StartDate And MyField < DateAdd(Days, 1, @EndDate)
    The "Between-equivalent" for the above would be:

    Where MyField Between @StartDate And DateAdd(ms, 86399998, @EndDate)

    Both queries require though, that one will not pass Start- and EndDate-Params which contain "fractional time-parts".

    If one want's to make it entirely bullet-proof (not requiring the App, to pass such "Integer-Dates" into the Params), then additional Casts could be added into the Query-SQL-String (as e.g. for my example above):

    Where MyField Between
    Cast(@StartDate As Date) And
    DateAdd(ms, 86399998, Cast(Cast(@EndDate As Date) As DateTime))

    There should be no performance-loss in the query, because these "more expensive param-evaluations" will be resolved as "const-expressions".

    Olaf

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