problem while retrieving by comparing datefield

Question

2.00/5 (1 vote)

See more:

i have a sql table named student and a field in the table "dat" which is of type date when i m writing a query like...select * from student where dat='01-05-2011',it is not showing anything,but actually a record having this date is there in the table.....i have tried many things but i can not do it

Posted 1-Dec-11 5:57am

Himu from Orissa

Updated 1-Dec-11 15:25pm

Eduard Lu

v2

Add a Solution

Comments

Uday P.Singh 1-Dec-11 12:19pm

what is the database server?

Himu from Orissa 1-Dec-11 12:20pm

sql server

Uday P.Singh 1-Dec-11 12:35pm

by default sql sever 2008 date type takes date in YYYY-MM-DD format, what record do you have inserted in your dat field and in which format?

Himu from Orissa 1-Dec-11 12:44pm

dd-mm-yyyy

Himu from Orissa 1-Dec-11 14:04pm

its working after using convert method with style id 120 for yyyy-mm-dd,thanx for ur response

3 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)

Morgs Morgan · Answer 1 · 2011-12-01T07:17:00

How about if you try and limit your query accuracy. Instead of using:

SQL

SELECT * FROM student WHERE dat= '2011-05-01';

Try using a range like so ( using '=>' instead of '=' ):

SQL

SELECT * FROM student WHERE dat=> '2011-05-01';

Caution! this should depend on what data you would like to retrieve, if you want to retrieve an exact match then solution 1 is your best bet otherwise go wild and use solution 2.

Happy coding,
Morgs

Eduard Lu · Answer 2 · 2011-12-01T15:29:00

Solution 3

Try converting both data to one format before comparing. See my code:

SQL

SELECT * FROM student WHERE CONVERT(date, dat,101) = CONVERT(date, '2011-05-01', 101)

Please mark as answer and vote 5 if this solved your problem

Regards,
Eduard

Posted 1-Dec-11 15:29pm

Eduard Lu

OriginalGriff · Answer 3 · 2011-12-01T06:05:00

Solution 1

Try using ISO format dates: yyyy-MM-dd:

SQL

SELECT * FROM student WHERE dat='2011-05-01'

Posted 1-Dec-11 6:05am

OriginalGriff

Comments

Himu from Orissa 1-Dec-11 12:15pm

i get nothing

OriginalGriff 1-Dec-11 12:24pm

Have you checked you have any matching values in the table? 1st May 2011?

Himu from Orissa 1-Dec-11 12:33pm

yes that date is there in the table

OriginalGriff 1-Dec-11 14:05pm

Then you got your original question wrong: you are using DateTime fields, not Date. The time part is significant! Try
SELECT * FROM Student WHERE dat BETWEEN '2011-11-17 00:00:00' AND '2011-11-17 23:59:59'