-
Nov 22nd, 2019, 10:43 AM
#1
Thread Starter
Hyperactive Member
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?
-
Nov 22nd, 2019, 11:02 AM
#2
Re: How To Use Between Is Select Statement
-
Nov 22nd, 2019, 07:40 PM
#3
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.
-
Nov 23rd, 2019, 02:16 PM
#4
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)
-
Nov 25th, 2019, 12:45 PM
#5
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?
-
Nov 26th, 2019, 09:29 AM
#6
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!
-
Nov 26th, 2019, 09:59 AM
#7
Re: How To Use Between Is Select Statement
@tyson - yes that works, although it kills any use of date indexes you might have...
-
Nov 26th, 2019, 10:24 AM
#8
Re: How To Use Between Is Select Statement
Originally Posted by szlamany
@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!
-
Nov 26th, 2019, 10:53 AM
#9
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
-
Nov 27th, 2019, 05:43 AM
#10
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
-
Nov 30th, 2019, 06:51 PM
#11
Re: How To Use Between Is Select Statement
Originally Posted by FunkyDexter
I stand with Gary. #DateFunctions FTW.
Ack.
Originally Posted by FunkyDexter
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
-
Dec 17th, 2019, 11:15 PM
#12
Member
Re: How To Use Between Is Select Statement
HI..
SELECT * FROM Table_Name
where OrderDateTime
BETWEEN '2019-11-12' AND '2019-11-22';
-
Dec 18th, 2019, 06:55 AM
#13
Re: How To Use Between Is Select Statement
Originally Posted by Siddhi Patel
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!
-
Dec 20th, 2019, 07:51 AM
#14
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
-
Jan 16th, 2020, 04:10 AM
#15
Member
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'
-
Jan 16th, 2020, 06:51 AM
#16
Re: How To Use Between Is Select Statement
Originally Posted by Sherin
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!
-
Jan 19th, 2020, 11:35 PM
#17
Member
Re: How To Use Between Is Select Statement
Try this query..
SELECT * FROM Exam
WHERE ExamDate BETWEEN '01/07/1996' AND '31/07/1996';
-
Jan 20th, 2020, 06:54 AM
#18
Re: How To Use Between Is Select Statement
Originally Posted by HarshShah
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|