Showing Date problem in SQL

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi there,

I have two table named Pur_Mst and Pur_Trans.I have to join both the tables to get some datas.
In Pur_Trans table there is a column named "ExpDate".There is only 3 data in that table.The ExpDates like,12/06/2015,12/04/2013,05/08/2015.Only 1 data is of 2013 yr.While am giving condition to see data between 01/01/2015 to 12/31/2015,it shows me the 2013 yr. data.

Below am mentioning whatever i have written in store procedure,

select *
from Pur_Trans pt INNER JOIN dbo.Pur_Master pm on pm.InvoiceNo=pt.InvoiceNo
where pt.Expdate between @Expdate and @Expdate1

What is error in this,I donot able to know.why it showing 2013 data donot understand.So plz anybody help me.

Thanks in advance...!

Posted 13-Aug-13 3:10am

Shivani Dash

Updated 13-Aug-13 3:12am

v2

Add a Solution

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)

Mike Meinz · Answer 1 · 2013-08-13T03:14:00

Solution 1

The most common cause of this kind of issue is the use of string Data Types (like Varchar in SQL or string in C# or vb) instead of DateTime data types.

1. Are @Expdate and @Expdate1 declared as Data Type DateTime?
2. Is pt.ExpDate declared as DataType DateTime?

If not, change the Data Types to DateTime to solve this problem.

Posted 13-Aug-13 3:14am

Mike Meinz

Updated 13-Aug-13 3:25am

v3

Comments

Shivani Dash 13-Aug-13 9:25am

Thank u for replying... :)
No Actually hv declared as nvarchar...

Mike Meinz 13-Aug-13 9:30am

DateTime data should be declared as Data Type DateTime. Doing otherwise causes extra work and introduces problems like you experienced.

Shivani Dash 13-Aug-13 9:34am

Sorry for that..Is there any solution regarding this..Actually cant change now..it has been used in many more places..i'll b in trouble if i change now..plz help me out.

Mike Meinz 13-Aug-13 10:00am

There are workarounds but the solution is to change the database columns and the program variables to the DateTime Data Type. Workarounds involve converting values to DateTime Data Type each time they are used in a DateTime comparison. This is not a best practice and is unprofessional. If you do the work now, to change the declarations in the database and in your software to the DateTime Data Type, you will save many hours of debugging and extra coding required by the workarounds.

Gopinath0072 · Answer 2 · 2013-08-13T03:20:00

Solution 2

Can you please confirm whether the column data type is datatime or varchar?, and also please convert your data value to common data format and check it.

Posted 13-Aug-13 3:20am

Gopinath0072

Comments

Shivani Dash 13-Aug-13 9:26am

Thanks for replyg..Actually Both are declared as nvarchar.

Simon_Whale · Answer 3 · 2013-08-13T04:32:00

I know its not the answer you are after but you should change your database to use the correct types for dates and alter your program accordingly.

But a work around would be as follows BUT and its a big but this will fail if the date is of an incorrect format or value i.e.

05/01/1974 would convert
but
29/02/1974 would raise an error

SQL

select *
 from Pur_Trans pt INNER JOIN dbo.Pur_Master pm on pm.InvoiceNo=pt.InvoiceNo
 where CONVERT(DATETIME, pt.Expdate) between CONVERT(DATETIME, @Expdate) and CONVERT(DATETIME, @Expdate1)