How to use between operator for dates

Question

0.00/5 (No votes)

See more:

select * From Table where Date BETWEEN '23/3/2016' AND '23/3/2016'

What I have tried:

I'm trying to run this query but it gives me the data between two dates and does not show me the data for 1st and last date

Posted 24-Jul-16 23:14pm

Member 10549697

Updated 25-Jul-16 0:51am

Add a Solution

Comments

Member 10549697 25-Jul-16 5:21am

rather i tried 23/3/2016 11:59:59 and it works for me but i dont want to use that

4 solutions

Solution 1

I Have Use this type query for get all result between and also first date and last date

SQL

select * From Table where Date >='23/3/2016' AND  Date <='23/3/2016' 

select * from non_gia_search_history where convert(varchar(10),create_time,110)>='03-15-2016' and convert(varchar(10),create_time,110) <='03-19-2016'

Posted 24-Jul-16 23:22pm

Tushar sangani

Updated 24-Jul-16 23:50pm

v2

Comments

Member 10549697 25-Jul-16 5:25am

Actually i tried this but it works like BETWEEN.so these both methods gives same results

Tushar sangani 25-Jul-16 5:38am

in date fild date insert like 23/3/2016 or 23/3/2016 00:00:00 ??

Tushar sangani 25-Jul-16 5:49am

I got this Please check date Formate in Date field is dd/mm/yyyy if not please set it
like

select * from non_gia_search_history where convert(varchar(10),create_time,110)>='03-15-2016' and convert(varchar(10),create_time,110) <='03-19-2016'

Richard Deeming 25-Jul-16 7:37am

That will prevent the database from using any index on the column to execute the query. It will always have to scan the entire table to find the matching records.

Solution 4 is a much better option.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jochen Arndt · Accepted Answer · 2016-07-25T00:51:00

I saw that you found a solution already but I think it is worth to give an explanation on what is happening.

The Date column from your table is probably a DateTime containing date and time. If you use a date without time in your query, the time portion is implicitly set to 00:00:00. So the effective query will be

SQL

select * From Table where Date BETWEEN '23/3/2016 00:00:00' AND '23/3/2016 00:00:00'

This applies also when using a comparison rather BETWEEN.

So there are two common solutions:

Use BETWEEN with time for end date:

SQL

select * From Table where Date BETWEEN '23/3/2016' AND '23/3/2016 23:59:59.999'

Or use less than comparison for the end date with the following day:

SQL

select * From Table where Date >= '23/3/2016' AND Date < '24/3/2016'

Member 10549697 · Accepted Answer · 2016-07-24T23:46:00

Solution 2

I found this solution

select * From Table where DATEDIFF(DAY,ColName,23/3/2016) <=0 AND DATEDIFF(DAY,ColName,23/3/2016)>=0

Posted 24-Jul-16 23:46pm

Member 10549697

Comments

Richard Deeming 25-Jul-16 7:36am

That will prevent the database from using any index on ColName to execute the query. It will always have to scan the entire table to find the matching records.

Solution 4 is a much better option.

Vignesh Mani · Accepted Answer · 2016-07-24T23:46:00

Solution 3

select * from Table_Name WHERE myDateTime >= '2008-01-01'
AND myDateTime <= '2008-04-01'

Posted 24-Jul-16 23:46pm

Vignesh Mani

Updated 24-Jul-16 23:47pm

v2