Getting this error "Error converting data type varchar to datetime"

Question

0.00/5 (No votes)

See more:

this is my procedure

SQL

Create PROC[dbo].[EventLog_Search2]
(
    @min AS DATETIME = NULL,
    @max AS DATETIME = NULL
)
AS

SELECT 
		[idx],
		[eventName],
		[userIdx],
		[description],
		[ip],
		[status],
		creationDate,
		[visible]
	FROM eventLog cc 
	
WHERE  cc.Visible = 1 and
	cc.[creationDate] >= @min AND
	cc.[creationDate] <= @max

and I am Executing it like

SQL

EXEC	[dbo].[EventLog_Search2]
	@min = '28/12/2013',
	@max = '10/03/2014'

and i am getting this error
"Msg 8114, Level 16, State 5, Procedure EventLog_Search2, Line 0
Error converting data type varchar to datetime."

My value is in "dd/mm/yyyy" format.
i Also tried this

SQL

cc.[creationDate] >= convert(datetime,@min,103) AND
cc.[creationDate] <= convert(datetime,@max,103)

But noting happening same error occurred

C# Code

C#

SqlCommand cmd = new SqlCommand("EventLog_Search2", DALHelper.GetConnection());
cmd.Parameters.AddWithValue("@min", cls.min);
cmd.Parameters.AddWithValue("@max", cls.max);
SqlConnection con = cmd.Connection;
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
using (con)
{
   SqlDataReader dr = cmd.ExecuteReader();

Posted 11-Mar-14 22:14pm

abdussalam143

Updated 11-Mar-14 23:23pm

v3

Add a Solution

Comments

Kornfeld Eliyahu Peter 12-Mar-14 4:26am

Probably you SQL do not know that the format should be dd/mm/yyyy - so interprets 28 as month and fails...
Try setting the format - http://technet.microsoft.com/en-us/library/ms189491.aspx

abdussalam143 12-Mar-14 5:27am

how could i make this changes in shard hosting.

Kornfeld Eliyahu Peter 12-Mar-14 5:31am

You must declare cls.min and cls.max as datetime so it will send to SQL as binary...

abdussalam143 12-Mar-14 5:36am

both values declare as datetime

OPees 12-Mar-14 6:14am

try this
convert(date,cc.[creationDate],103) >= convert(date,@min,103) AND
convert(date,cc.[creationDate],103) <= convert(date,@max,103)

abdussalam143 12-Mar-14 7:26am

i need date and time both coz some time time also come in the parameter coz it is search proc for event log

OPees 12-Mar-14 8:32am

then you compare date part and time part separate..
may be these two links can help you..

http://stackoverflow.com/questions/807909/how-can-i-compare-time-in-sql-server
http://stackoverflow.com/questions/7710449/how-to-get-time-from-datetime-format-in-sql

5 solutions

Solution 1

Because cls.Idx and cls.TermIdx have the wrong Type - they must be DateTime instead of string!

Posted 11-Mar-14 22:28pm

Bernhard Hiller

Solution 2

Convert your datatype to datetime or try using CultureInfo parameter in your Code Behind page to control Datetime format isuues.

Posted 11-Mar-14 23:29pm

Manikandan K

Comments

abdussalam143 12-Mar-14 5:39am

protected void Application_BeginRequest(object sender, EventArgs e)
{
System.Globalization.CultureInfo newCulture = (System.Globalization.CultureInfo)System.Threading.Thread.CurrentThread.CurrentCulture.Clone();
newCulture.DateTimeFormat.ShortDatePattern = "dd-MMM-yyyy";
newCulture.DateTimeFormat.DateSeparator = "-";
System.Threading.Thread.CurrentThread.CurrentCulture = newCulture;
}

i use this code to set date format

Solution 3

CultureInfo culture= new CultureInfo("fr-FR");

DateTime dt1 = Convert.ToDateTime(txtDate1, culture);

Posted 12-Mar-14 0:11am

Manikandan K

Solution 4

Try This One:

VB

convert(varchar,cc.[creationDate],112) >= convert(varchar,@min,112) AND
    Convert(varchar,cc.[creationDate],112) <= convert(varchar,@max,112)

Posted 12-Mar-14 0:22am

RahulRana723

Comments

abdussalam143 12-Mar-14 7:26am

if i convert it in varchar or nvarchar it reacts like string not date time.

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)

abdussalam143 · Accepted Answer · 2014-03-12T03:28:00

I made these changes in proc and it start working

SQL

Create PROC[dbo].[EventLog_Search2]
(
    @min AS varchar(Max)= NULL,
    @max AS varchar(Max)= NULL
--make them varchar(max)

)
AS
 
SELECT 
		[idx],
		[eventName],
		[userIdx],
		[description],
		[ip],
		[status],
		creationDate,
		[visible]
	FROM eventLog cc 
	
WHERE  cc.Visible = 1 and
	cc.[creationDate] >= convert(datetime,@min,103) AND
	cc.[creationDate] <= convert(datetime,@max,103) 
--(convert them to date time here)

Getting this error "Error converting data type varchar to datetime"

5 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Solution 5

Add your solution here

Preview 0

Existing Members

...or Join us