Sql server 2005 between clause with date

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

         emp_name                emp_state      joining_date
1	Surendra Kumar Verma	Uttar pradesh	01/01/2012
2	Prem Kumar Verma	Uttar pradesh	02/02/2012
3	Rajendra Kumar Verma	Uttar pradesh	05/04/2012
4	Suresh Kumar Verma	Uttar pradesh	02/08/2012
5	Aditya Rajput	        Delhi	        06/12/2012

i want to use between clause like this Query

select emp_name,emp_state from emp_reg where  joining_date between '01/01/2012' and '06/02/2012'

but this is not giving correct output
can anybody tell me what is the correct format to use between clause with dates.
i'm using Sql server 2005.

Posted 2-Nov-12 22:27pm

Surendra0x2

Add a Solution

Comments

Abhishek Pant 3-Nov-12 7:04am

Your Syntax is correct.

2 solutions

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)

**AmitGajjar** · Accepted Answer · 2012-11-02T22:50:00

Solution 1

Hi,

Have you tried some online discussion ?

Try something like,

SQL

SELECT *
FROM emp_reg
WHERE joining_date between  Convert(datetime ,'01/01/2012') AND Convert(datetime ,'06/02/2012');

Let me know if it won't work for you.

Posted 2-Nov-12 22:50pm

AmitGajjar

Updated 3-Nov-12 0:21am

v2

Comments

Surendra0x2 3-Nov-12 5:11am

i'm using calender control for textbox and it returns date in 01/12/2012 format
there are two textbox for searching record with given dates..
like from date to todate and in both textbox i'm using calender control in dateformat 01/12/2012 so how can i use this between clause sql query to search from my table?

AmitGajjar 3-Nov-12 5:13am

Ok in that case pass the value in the query. Here in the query i have given you two dates. instead of that pass your dates.

Surendra0x2 3-Nov-12 5:21am

what is to_date in the above query sir?

AmitGajjar 3-Nov-12 6:01am

Sorry it's an oracle syntax, you can use Convert(datetime ,'2002/01/01') instead.

Surendra0x2 3-Nov-12 6:14am

sir if the datatype of joining date is datetime then ?
my sql query is to create table create table emp_reg(emp_id int identity,emp_name varchar(20), emp_city varchar(20),joining_date datetime)

calender control selected date gives format like dd/mm/yyyy n this format is saving in databse like
1 surendra jabalpur 2012-01-06 00:00:00.000

then how can we write the same query for this ?

AmitGajjar 3-Nov-12 6:22am

Please see my updated query and let me know the result.

Surendra0x2 3-Nov-12 6:42am

Sir as u updated ur query it is returning rows accroding to dd(date) it is not considering mm(month)
in ur query
SELECT *
FROM emp_reg
WHERE joining_date between Convert(datetime ,'01/01/2012') AND Convert(datetime ,'06/02/2012');

01 and 02 showing months so the above query should return only first 2 rows of table mentioned above but it is returing all rows as 01 and 06 dates containing all rows.

sorry for bothering u sir.

AmitGajjar 3-Nov-12 6:44am

ok then pass '02/06/2012' instead of '06/02/2012'

Surendra0x2 3-Nov-12 7:00am

i changed 02/06/2012 now it is showing first two rows but sir
SELECT * FROM emp_reg WHERE joining_date between Convert(datetime ,'01/01/2012') AND Convert(datetime ,'02/06/2012');
here 01 and 02 are dates so it is giving output according to date and 01 and 06 are months so accroding to me it should return rows from 01 to 06 months. but it is not considering months :| and when converting above varchar datatype to datetime the date will be in format yyyyy/mm/dd..

AmitGajjar 3-Nov-12 7:02am

your server regional setting is MM/dd/yyyy so it is doing like so. you need to pass date in form of the regional setting format.

Surendra0x2 3-Nov-12 7:11am

sir what about ur regional language?as i'm also in india i want to set regional language to india.

AmitGajjar 3-Nov-12 7:16am

See http://support.microsoft.com/kb/307938

Default date format in the windows system is MM/dd/yyyy so you need to pass date accordingly.

Surendra0x2 3-Nov-12 7:15am

british language having format dmy so should i change to british?
SELECT * FROM sys.syslanguages
set language british
can i use this?

Surendra0x2 3-Nov-12 7:17am

it worked sir when i changed to british language thanks a ton sir for giving me ur time.
n sorry for bothering you :(

AmitGajjar 3-Nov-12 7:18am

No issue, just mare solution as answered if it works for you.

*Abhishek Pant* · Accepted Answer · 2012-11-03T00:15:00

Solution 2

applied the command

SQL

select emp_name,emp_state from Code where  joining_date between '01/01/2012' and '06/02/2012'

as you wrote above and Entered the same values you inserted..
my table design is:

Column Name Data Type Allow Null
emp_name | nchar(30) |
emp_state | nchar(50)|
joining_date| date|

I kept all values not null.

Therefore I got Top 3 data insterted in your data table as answer as a correct output.

Overall,I suggest you to Use date as Datatype

Posted 3-Nov-12 0:15am

Abhishek Pant

Updated 3-Nov-12 0:54am

v2

Comments

Surendra0x2 3-Nov-12 6:28am

create table emp_reg(emp_id int identity,emp_name varchar(20), emp_city varchar(20),joining_date date)
when i'm executing this query i'm getting error
Msg 2715, Level 16, State 7, Line 1
Column, parameter, or variable #4: Cannot find data type Date.
i'm using sql server 2005
datetime datatype working fine. but date giving this error.

Surendra0x2 3-Nov-12 6:32am

i think sqlserver2005 does not support Date datatype....?

Abhishek Pant 3-Nov-12 6:56am

Oops! but this works for me.(Using Sql Server 2008 R2) and date datatype was introduced in Sql server 2008.But for you solution I again changed its datatype as datetime(as you said). It is still works for me- again answers are correct. Got names as surendra ...... , Prem......., rajendra.... as format of datetime is yyyy-mm-dd

Surendra0x2 3-Nov-12 7:03am

sir can you write the query u used to execute to get the desired output plz post the query.

Abhishek Pant 3-Nov-12 7:15am

I just copied your query so that no mistake happens. select * from Code where joining_date between '01/01/2012' and '06/02/2012' (you can change * to other fields).

Abhishek Pant 3-Nov-12 7:17am

select * from Code where joining_date between '01/01/2012' and '06/02/2012'

My table name was Code.