Results 1 to 18 of 18

Thread: How To Use Between Is Select Statement

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2010
    Posts
    478

    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
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    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
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    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,929

    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
    Connecticut
    Posts
    18,263

    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,834

    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
    Connecticut
    Posts
    18,263

    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,834

    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,495

    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,900

    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.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

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

  11. #11
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    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

  12. #12

    Re: How To Use Between Is Select Statement

    HI..

    SELECT * FROM Table_Name
    where OrderDateTime
    BETWEEN '2019-11-12' AND '2019-11-22';

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

    Re: How To Use Between Is Select Statement

    Quote Originally Posted by Siddhi Patel View Post
    HI..

    SELECT * FROM Table_Name
    where OrderDateTime
    BETWEEN '2019-11-12' AND '2019-11-22';
    That does not pick up 11/22/2019

    drop table #Dates
    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' AND '2019-11-22';
    Please remember next time...elections matter!

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

    Re: How To Use Between Is Select Statement

    That is because the date is a datetime value. When you pass just the date it assumes you mean midnight so 11/22/2019 is queried as 2019-11-22 00:00:00 and you entry for 2019-11-22 is 20:31:31 so it is not inside your between values (which is why I suggested casting the OrderDateTime as a date in the clause
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  15. #15
    Member
    Join Date
    Jul 2019
    Location
    Ahmedabad
    Posts
    57

    Re: How To Use Between Is Select Statement

    Try this query :

    Code:
    select OrderDateTime from [table-name] where OrderDateTime between '2019-11-12' and '2019-11-22'

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

    Re: How To Use Between Is Select Statement

    Quote Originally Posted by Sherin View Post
    Try this query :

    Code:
    select OrderDateTime from [table-name] where OrderDateTime between '2019-11-12' and '2019-11-22'
    Post #13 proved that doesn't work...
    Please remember next time...elections matter!

  17. #17

    Re: How To Use Between Is Select Statement

    Try this query..

    SELECT * FROM Exam
    WHERE ExamDate BETWEEN '01/07/1996' AND '31/07/1996';

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

    Re: How To Use Between Is Select Statement

    Quote Originally Posted by HarshShah View Post
    Try this query..

    SELECT * FROM Exam
    WHERE ExamDate BETWEEN '01/07/1996' AND '31/07/1996';
    You should be able to just look at what you posted and tell it doesn't work...

    Msg 242, Level 16, State 3, Line 13
    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
    Please remember next time...elections matter!

Posting Permissions

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



Click Here to Expand Forum to Full Width