Sql 2008 Date Search Error

Question

1.00/5 (1 vote)

See more:

, +

Hello Guys,

i am trying to search this query

SQL

SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY PERS_NAME ASC) AS Row,
 T_AKARAT_BILLS.*,T_AKARAT_PERS_DATA.PERS_NAME,BILL_TP_NAME
 FROM T_AKARAT_BILLS join T_AKARAT_RENTS
 on T_AKARAT_RENTS.ID=T_AKARAT_BILLS.RENT_ID join T_AKARAT_PERS_DATA
 on T_AKARAT_PERS_DATA.ID=t_AKARAT_RENTS.TENANT_ID   join T_AKARAT_BUILDINGS
 on T_AKARAT_BUILDINGS.ID=T_AKARAT_RENTS.BUILDING_ID  join T_AKARAT_OFFICE_DATA
 on T_AKARAT_OFFICE_DATA.ID=T_AKARAT_BUILDINGS.OFFICE_ID join T_AKARAT_BILL_TP
 on T_AKARAT_BILL_TP.ID=T_AKARAT_BILLS.BILL_TP_ID
 where
 T_AKARAT_BUILDINGS.OFFICE_ID= 3 and T_AKARAT_BILLS.BILL_DT ='23/10/2013' ) AS TableWithRowNumbers WHERE Row > '0' AND Row <= '10'

My Problem Exists in (where Condition) exactly the (date value) which is Formatted as Day,Month,Year (23/10/2013)

If i searched with the above Query i am getting this error:

Conversion failed when converting date and/or time from character string.

If i changed this value to be Month,Day,Year (10/23/2013), it Works Fine !!!!

but i need to search date with this format (Day,Month,Year)

Any Ideas?

Posted 27-Oct-13 2:30am

_ProgProg_

Updated 27-Oct-13 2:33am

v3

Add a Solution

Comments

_ProgProg_ 27-Oct-13 9:21am

Any Ideas Plz?

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)

Mike Meinz · Answer 1 · 2013-10-27T03:58:00

The database column T_AKARAT_BILLS.BILL_DT should be declared as a DATE data type.

When you use T_AKARAT_BILLS.BILL_DT in the Where clause, the best practice is to use a parameterized query where the date to be compared with T_AKARAT_BILLS.BILL_DT is passed as a DATE Data Type.

Doing so, eliminates date problems!

C#

...

SqlCommand SQLcom = new SqlCommand();

...

SQLcom.Parameters.AddWithValue("@SelectDate",dtSelectDate);

...

SQLcom.CommandText="SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY PERS_NAME ASC) AS Row,
 T_AKARAT_BILLS.*,T_AKARAT_PERS_DATA.PERS_NAME,BILL_TP_NAME
 FROM T_AKARAT_BILLS join T_AKARAT_RENTS
 on T_AKARAT_RENTS.ID=T_AKARAT_BILLS.RENT_ID join T_AKARAT_PERS_DATA
 on T_AKARAT_PERS_DATA.ID=t_AKARAT_RENTS.TENANT_ID   join T_AKARAT_BUILDINGS
 on T_AKARAT_BUILDINGS.ID=T_AKARAT_RENTS.BUILDING_ID  join T_AKARAT_OFFICE_DATA
 on T_AKARAT_OFFICE_DATA.ID=T_AKARAT_BUILDINGS.OFFICE_ID join T_AKARAT_BILL_TP
 on T_AKARAT_BILL_TP.ID=T_AKARAT_BILLS.BILL_TP_ID
 where
 T_AKARAT_BUILDINGS.OFFICE_ID= 3 and T_AKARAT_BILLS.BILL_DT = @SelectDate) AS TableWithRowNumbers WHERE Row > '0' AND Row <= '10'";

...

_ProgProg_ · Answer 2 · 2013-10-27T04:46:00

Solution 2

I 've Solved it My self and this is the answer:

T_AKARAT_BILLS.BILL_DT = Convert(datetime,'23/10/2013',103)

Posted 27-Oct-13 4:46am