Problem in select in sql server 2008

Question

1.00/5 (1 vote)

See more:

In my database i have a table which has a datetime field
when i am inserting data it is including time with date
i am facing problem in selecting the data of any specific
date
Say date1 between '08/01/2014' and '08/01/2014'
my query is

SQL

SELECT *, (case when entrytype=0 or entrytype=3 then amount else 0 end )as Dmt
,(case when entrytype=1 or entrytype=2  or entrytype=4 or entrytype=66
 then amount else 0  end )as cmt FROM FeesLedger
 WHERE EntryDate
 BETWEEN '01/08/2014' AND '01/08/2014' 
AND StudentId = 2 AND Amount > 0 ORDER BY EntryDate,EntryNumber

Posted 7-Jan-14 19:35pm

srilekhamenon

Updated 7-Jan-14 19:37pm

Tejas Vaishnav

v2

Add a Solution

Comments

Maciej Los 8-Jan-14 1:46am

What kind of problem? Query looks OK.

srilekhamenon 8-Jan-14 2:28am

it is not returning any result

srilekhamenon 8-Jan-14 2:30am

is it depend upon our system date format my system format dd/mm/yyyy

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

Maciej Los · Answer 1 · 2014-01-07T20:05:00

First of all, please, read my comment to the question.

Have a look at your WHERE statement:

SQL

WHERE EntryDate BETWEEN '01/08/2014' AND '01/08/2014'

2 things:
1) year is equal 2014. Is it correct?
2) If you want to fetch data for single date, use:

SQL

WHERE EntryDate = '01/08/2014'

Sometimes, you need to change date format using SET DATEFORMAT[^] command to properly fetch data.

SQL

SET DATEFORMAT mdy;  --for other parameters, see the documentation
SELECT ...
FROM ...
WHERE datefield = '01/08/2013'

Peter Leow · Answer 2 · 2014-01-07T19:55:00

Solution 1

try changing this:

SQL

BETWEEN '2014/01/01' AND '2014/01/07'

Posted 7-Jan-14 19:55pm

Peter Leow

Bala Selvanayagam · Answer 3 · 2014-01-07T19:57:00

Solution 2

This should work

C#

BETWEEN '01 Jan 2014' AND '07 Jan 2014'

Posted 7-Jan-14 19:57pm

Bala Selvanayagam

OriginalGriff · Answer 4 · 2014-01-07T20:00:00

Try changing the date format:

SQL

SELECT *, (case when entrytype=0 or entrytype=3 then amount else 0 end )as Dmt
,(case when entrytype=1 or entrytype=2  or entrytype=4 or entrytype=66
 then amount else 0  end )as cmt FROM FeesLedger
 WHERE EntryDate
 BETWEEN '2014-01-08' AND '2014-02-08' 
AND StudentId = 2 AND Amount > 0 ORDER BY EntryDate,EntryNumber

ISO format is SQL default, and it may be that the system the SQL Server is running in is set to US format dates.

joginder-banger · Answer 5 · 2014-01-07T20:06:00

Solution 5

Hi,

I have used this type of query many time I hope this help full you.
try this

SQL

where Post_date between CONVERT(date,first Date) and CONVERT(date,second date)

for any query free help hit to reply.
Happy coding ☺
jsb

Posted 7-Jan-14 20:06pm

joginder-banger