Get Records between two dates in MS SQL

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

I have a table in which i have a column named "dtCreated" which has got datatype as VARCHAR(50)

Now I want records between two dates.

I wrote my query as written below

<br />
SELECT * FROM mytable WHERE dtcreated>=fromdate AND dtcreated<=todate<br />

This query work fine when fromdate and ToDate are of same month. i.e if fromDate =1/1/2011
and todate is 1/31/2011 it will work fine

But if fromdate is 1/1/2011 and todate is 2/1/2011 then no records will be returned.

Please give me solution of it

Posted 7-Feb-11 6:53am

markand911

Add a Solution

Comments

tusharchikhale 1-Jan-13 13:00pm

my data type of date column is date
so when i use SELECT * FROM mytable WHERE date>=fromdate AND date<=todate in my C# code runtime error occurred
Operand type clash: date is incompatible with int

please give me solution how i can compare the date which is of date datatype

5 solutions

Solution 2

While Umair's Answer might work, it is not guaranteed to get you want you want.

I say might because: why are you using VARCHAR(50) to hold dates instead of DateTime? In your app all these might be the same, but not when you run your query.

01/01/2011<br />
Jan-01-2011<br />
JAN 01, 2011<br />
2011/01/01<br />
January 01, 2011<br />
01-JAN-11<br />

Since your are using VARCHAR(50) they all valid data to store in your field. Is it possible to change your field type to DateTime? if so, you will have less headache and maintence.

just my 2cts

Posted 7-Feb-11 8:03am

Yusuf

Comments

dasblinkenlight 7-Feb-11 17:14pm

Excellent point. Another thing to remember is that Umair's solution would be slow on very large volumes of data, because CONVERT(DATETIME,dtcreated) kills any possibility of indexing.

Solution 3

It's a good practice to use appropriate data type for your data in sql.

if your data is a date value. then you can use 'datetime' data type or 'smalldatetime'. This will also help you avoid future problems with data integrity. coz, sql will not allow non-date value in your data.

if you have converted your data type to 'datetime' or 'smalldatetime'. you can have this simple query:

SQL

SELECT * FROM mytable WHERE dtcreated BETWEEN fromdate AND todate

Posted 7-Feb-11 20:04pm

ulyses31

Updated 1-May-12 23:02pm

v2

Solution 4

"SELECT * FROM tblBooking WHERE (BPickupDate >='" + tbxPickupDate.Text + "') AND (BDropDate <= '" + tbxDropDate.Text + "')";

after using this u can search data between two field of table.
--<sanjay>--

Posted 1-May-12 22:12pm

Delving

Updated 1-May-12 23:03pm

v3

Comments

phil.o 2-May-12 5:10am

Reason for my vote of 2 :
It is a very bad habit to construct SQL queries concatenating fields like you do. It leaves the solution open for SQL injection attacks.
Better use a SqlCommand object and add it some SqlParameters ; it will defeat any attempt of SQL injection, and it will also make sure of the validity of the parameters.

Solution 5

DECLARE @StartDate DateTime DECLARE @EndDate DateTime SET @StartDate = '2016-01-15' SET @EndDate = '2016-01-27' SELECT [Policy Issue date] FROM [HealthBuzz_Premium Register] WHERE [Policy Issue date] BETWEEN @StartDate AND DATEADD(s,-1,DATEADD(d,1,@EndDate))

Posted 27-Jan-16 0:07am

Member 11258245

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)

Umair Feroze · Accepted Answer · 2011-02-07T07:03:00

Solution 1

This will work for you:

SQL

SELECT * FROM mytable WHERE CONVERT(DATETIME,dtcreated)>=fromdate AND CONVERT(DATETIME, dtcreated)<=todate

Hope this answer your question

Posted 7-Feb-11 7:03am

Umair Feroze