The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. : The Official Microsoft ASP.NET Forums

Reply

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime ...

Mar 13, 2009 07:32 AM|LINK

Hello,

I really need help with the below error.

I dont get this when i run my asp.net c# application in VS@005,

but when i make virtual directory on my office Windows server I

get this error.

Tell me what changes need to be done in C# code or crystal report or Sql queries or

server settings !!below is the error :

The conversion of a char data type to a datetime data type resulted in an

out-of-range datetime value.

Description: An unhandled exception occurred during the execution of the current web request.

Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

SqlException (0x80131904): The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.]

System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890

System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875

System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194

System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392

System.Data.SqlClient.SqlDataReader.HasMoreRows() +157

System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +197

System.Data.SqlClient.SqlDataReader.Read() +9

monthlyreport.totalleavetaken() in d:\Swirl Documents\Projects\AttendanceReport1\monthlyreport.aspx.cs:134

monthlyreport.Page_Load(Object sender, EventArgs e) in d:\Swirl Documents\Projects\AttendanceReport1\monthlyreport.aspx.cs:39

System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14

System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35

System.Web.UI.Control.OnLoad(EventArgs e) +99

System.Web.UI.Control.LoadRecursive() +50

Regards

Reply

Re: The conversion of a char data type to a datetime data type resulted in an out-of-range datet...

Mar 13, 2009 07:48 AM|LINK

READ THE BELOW LINK:
http://msdn.microsoft.com/en-us/library/ms187928.aspx

HOPE IT WILL HELP YOU.

MARK AS ANSWER if its help you.

Shawpnendu Bikash Maloroy
MCTS
http://shawpnendu.blogspot.com

Reply

Re: The conversion of a char data type to a datetime data type resulted in an out-of-range datet...

Mar 13, 2009 08:09 AM|LINK

To do this in one SQL Server, you can try this
select CONVERT(DATETIME,'18/02/2009 11:24:34',104)
go

Many Thanks & Best Regards,
HuaMin Chen

Edited by wmec on Mar 13, 2009 08:12 AM
Edited by wmec on Mar 13, 2009 08:39 AM

Reply

Re: The conversion of a char data type to a datetime data type resulted in an out-of-range datet...

Mar 13, 2009 08:32 AM|LINK

The scenario is there is a VB 6.0 exe which runs on the startup of machine and using a

simple hard coded sql query collects date time of the instance and puts in databass.

Later for checking out the entries asp.net c# application is being made where using crystal report

we are displaying the entries.

Specifically there is a report which calculates user's Leaves:

Below is the simple function written in c# for calculating leave to show in report :

public void totalleavetaken()
    {
//        select count(status) from user_info
//where status = 'leave' and user_name = 'vishal'
//and user_date &lt; '04/30/2008'


        string username;
        DateTime enddate;
        DateTime startdate;


        username = ddluser.SelectedValue;
        enddate = CalendarPopup2.SelectedDate;
        startdate = CalendarPopup1.SelectedDate;


        sqlconn = new SqlConnection(creativeconfiguration.DbConnectionString);
        string str = "select count(status) from user_info where status = 'Leave' and user_name ='" + username + "' and user_date between '" + startdate + "' and '" + enddate + "'";
        cmd = new SqlCommand(str, sqlconn);
        sqlconn.Open();
        dr =  cmd.ExecuteReader();
        if (dr.Read()) ----------------------------------> The error coming on this line
         {
            lbltotalleave.Text = Convert.ToString(dr[0]);
        }
        sqlconn.Close();
    

    }


    public void totalleavebalance()
    {
       

        string username;
        DateTime enddate;


        username = ddluser.SelectedValue;
        enddate = CalendarPopup2.SelectedDate;


        sqlconn = new SqlConnection(creativeconfiguration.DbConnectionString);
        string str = "select 35 - count(status) from user_info where status = 'Leave' and user_name ='" + username + "' and user_date <= '" + enddate + "'";
        cmd = new SqlCommand(str, sqlconn);
        sqlconn.Open();
        dr = cmd.ExecuteReader();
        if (dr.Read())
        {
            lblbalanceleave.Visible = true;
            lblbalanceleave.Text = Convert.ToString(dr[0]);
        }
        sqlconn.Close();

Regards

Edited by etti on Mar 13, 2009 08:34 AM
Edited by etti on Mar 13, 2009 08:36 AM

Reply

Re: The conversion of a char data type to a datetime data type resulted in an out-of-range datet...

Mar 13, 2009 09:29 AM|LINK

wmec

To do this in one SQL Server, you can try this
select CONVERT(DATETIME,'18/02/2009 11:24:34',104)
go

How to write this conversion in C# ??

Regards

Edited by etti on Mar 13, 2009 09:30 AM

Reply

Re: The conversion of a char data type to a datetime data type resulted in an out-of-range datet...

Mar 13, 2009 10:15 AM|LINK

Where are your codes? Are you changing one table field value from a char to a datetime field? If yes, you can change it like this

        sqlconn = new SqlConnection(creativeconfiguration.DbConnectionString);
        string str = "select CONVERT(DATETIME,field_in_char,104) from table_a where ...";
        cmd = new SqlCommand(str, sqlconn);
        DateTime DateTime_temp;
        sqlconn.Open();
        dr = cmd.ExecuteReader();
        if (dr.Read())
        {
            ...
            DateTime_temp = Convert.ToDateTime(dr[0]);
            ....

Many Thanks & Best Regards,
HuaMin Chen

Edited by wmec on Mar 13, 2009 10:18 AM

Reply

etti

Member

10 Points

27 Posts

Re: The conversion of a char data type to a datetime data type resulted in an out-of-range datet...

Mar 13, 2009 10:35 AM|LINK

wmec

Where are your codes? Are you changing one table field value from a char to a datetime field? If yes, you can change it like this

        sqlconn = new SqlConnection(creativeconfiguration.DbConnectionString);
        string str = "select CONVERT(DATETIME,field_in_char,104) from table_a where ...";
        cmd = new SqlCommand(str, sqlconn);
        DateTime DateTime_temp;
        sqlconn.Open();
        dr = cmd.ExecuteReader();
        if (dr.Read())
        {
            ...
            DateTime_temp = Convert.ToDateTime(dr[0]);
            ....

No am not changing the datatype.

Its same in front end and back end ie DATETIME

What i feel about this error is the time in

the database and the time which my application taking from the server

while running in IIS is not marching. Because otherwise this application working very good

in VS2005..but not on server(IIS)

Regards

Edited by etti on Mar 13, 2009 10:36 AM

Reply

wmec

Contributor

6556 Points

3325 Posts

Re: The conversion of a char data type to a datetime data type resulted in an out-of-range datet...

Mar 14, 2009 11:11 AM|LINK

You can directly change the format to be in string and take it from the table

Many Thanks & Best Regards,
HuaMin Chen

Reply

etti

Member

10 Points

27 Posts

Re: The conversion of a char data type to a datetime data type resulted in an out-of-range datet...

Mar 16, 2009 09:58 AM|LINK

etti

Normal 0 false false false MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;}

The scenario is there is a VB 6.0 exe which runs on the startup of machine and using a

simple hard coded sql query collects date time of the instance and puts in databass.

Later for checking out the entries asp.net c# application is being made where using crystal report

we are displaying the entries.

Specifically there is a report which calculates user's Leaves:

Below is the simple function written in c# for calculating leave to show in report :
public void totalleavetaken()
    {
//        select count(status) from user_info
//where status = 'leave' and user_name = 'vishal'
//and user_date &lt; '04/30/2008'


        string username;
        DateTime enddate;
        DateTime startdate;


        username = ddluser.SelectedValue;
        enddate = CalendarPopup2.SelectedDate;
        startdate = CalendarPopup1.SelectedDate;


        sqlconn = new SqlConnection(creativeconfiguration.DbConnectionString);
        string str = "select count(status) from user_info where status = 'Leave' and user_name ='" + username + "' and user_date between '" + startdate + "' and '" + enddate + "'";
        cmd = new SqlCommand(str, sqlconn);
        sqlconn.Open();
        dr =  cmd.ExecuteReader();
        if (dr.Read()) ----------------------------------> The error coming on this line
         {
            lbltotalleave.Text = Convert.ToString(dr[0]);
        }
        sqlconn.Close();
    

    }


    public void totalleavebalance()
    {
       

        string username;
        DateTime enddate;


        username = ddluser.SelectedValue;
        enddate = CalendarPopup2.SelectedDate;


        sqlconn = new SqlConnection(creativeconfiguration.DbConnectionString);
        string str = "select 35 - count(status) from user_info where status = 'Leave' and user_name ='" + username + "' and user_date <= '" + enddate + "'";
        cmd = new SqlCommand(str, sqlconn);
        sqlconn.Open();
        dr = cmd.ExecuteReader();
        if (dr.Read())
        {
            lblbalanceleave.Visible = true;
            lblbalanceleave.Text = Convert.ToString(dr[0]);
        }
        sqlconn.Close();

Finally some one helped me and i found the solution as follows::

Your SQL is very susceptable to injection attack

string str = "select count(status) from user_info where status = 'Leave' and user_name ='" 
  + username + "' and user_date between '" + startdate + "' and '" + enddate + "'";

I suggest that you change it to use stored procedures or parameterised TSQL

string str = "select count(status) from user_info where status = 'Leave' and user_name = @USER and user_date between @startdate and @enddate'"; 

cmd.Parameters.Add("@USER", SqlDbType.NVarChar, 100);  -- Assuming type and size
cmd.Parameters["@USER"].Value = username
cmd.Parameters.Add("@startdate ", SqlDbType.DateTime);
cmd.Parameters["@startdate "].Value = startdate ;
cmd.Parameters.Add("@enddate", SqlDbType.DateTime);
cmd.Parameters["@enddate"].Value = enddate;

Regards

Marked as answer by etti on Mar 16, 2009 09:59 AM

Reply

etti

Member

10 Points

27 Posts

Re: The conversion of a char data type to a datetime data type resulted in an out-of-range datet...

Mar 16, 2009 10:11 AM|LINK

We can do it this way too.

But this answer is not that worth.It still gets inhection attacks sometimes.

username = ddluser.SelectedValue;
        DateTime NewEnddate = new DateTime(2009, 03, 12);
        DateTime NewStartDate = new DateTime(2009, 03, 12);
        NewEnddate = CalendarPopup2.SelectedDate;
        NewStartDate = CalendarPopup1.SelectedDate;
        SqlDataAdapter Adapter = new SqlDataAdapter("select count(status) from user_info where and status = 'Leave' and user_name ='" + username + "' and user_date between '" + NewStartDate + "' and '" + NewEnddate + "'", creativeconfiguration.DbConnectionString);
        SqlParameter MyParam = new SqlParameter();
        MyParam.ParameterName = "@NewEnddate";
        MyParam.ParameterName = "@NewStartDate";
        MyParam.SqlDbType = SqlDbType.DateTime;
        MyParam.Value = NewEnddate;
        MyParam.Value = NewStartDate;
        Adapter.SelectCommand.Parameters.Add(MyParam);
        DataSet Ds = new DataSet();
        Adapter.Fill(Ds);
        lbltotalleave.Text = Ds.Tables[0].Rows.Count.ToString();

Regards

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. RSS

9 replies

etti

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime ...

shawpnendu

Re: The conversion of a char data type to a datetime data type resulted in an out-of-range datet...

wmec

Re: The conversion of a char data type to a datetime data type resulted in an out-of-range datet...

etti

Re: The conversion of a char data type to a datetime data type resulted in an out-of-range datet...

etti

Re: The conversion of a char data type to a datetime data type resulted in an out-of-range datet...

wmec

wmec

wmec

Re: The conversion of a char data type to a datetime data type resulted in an out-of-range datet...

etti

Re: The conversion of a char data type to a datetime data type resulted in an out-of-range datet...

wmec

wmec

Re: The conversion of a char data type to a datetime data type resulted in an out-of-range datet...

etti

Re: The conversion of a char data type to a datetime data type resulted in an out-of-range datet...

etti

etti

Re: The conversion of a char data type to a datetime data type resulted in an out-of-range datet...

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft