.net c# sql string to date conversion error

Question

I am having convserion error for database using SQL in visual studio.

The database I am using is a normal sql server database. It is given to me as my assignment.

This is my query method is my webservice

 [WebMethod]
    public bool search(string ddate, string dairport, string aairport, string seat)
    {
        int seat2 = Convert.ToInt32(seat);
        DateTime date = Convert.ToDateTime(ddate);

        String query1 = "SELECT * FROM Flight_Schedule S WHERE S.departure_date = '24/09/2011'";

        using (SqlConnection connect = new SqlConnection(conn))
        {
            SqlCommand cmd = new SqlCommand(query1, connect);
            connect.Open();
            SqlDataReader result = cmd.ExecuteReader();
            try
            {
                if (result.Read())
                {
                    return true;
                }

            finally
            {
                result.Close();
                connect.Close();
                connect.Dispose();
            }
            return false;
        }

    }

There is no issue with normal queries like :

"SELECT * FROM Flight_Schedule S WHERE S.origin_airport_code = '" + dairport + "'";

Error :

System.Data.SqlClient.SqlException: Conversion failed when converting date and/or time from character string.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.HasMoreRows()
   at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
   at System.Data.SqlClient.SqlDataReader.Read()
   at AirportService.AirportServices.search(String ddate, String dairport, String aairport, String seat) in C:\Users\XXXXX\Documents\Visual Studio 2010\Projects\WebService2\AirportService\AirportServices.asmx.cs:line 47

Your "normal query" (second example) is just RIPE for abuse... that is a DEADLY query. I could kill your server with that web-accessible method. — Marc Gravell
– Marc Gravell, Commented Oct 5, 2011 at 7:36
marc gravell it is a school assignment which no 1 would kill. the value '24/09/2011' is copied from the database. — user904406
– user904406, Commented Oct 5, 2011 at 8:37
@user904406 part of the problem here is that you keep thinking about dates as strings. Dates are not strings. At least, unless you are doing it wrong. Also, the value 24/09/2011 is not even how most DBs would display it by default (dd/MM/yyyy vs MM/dd/yyyy etc). Hence why parameters avoid all this mess; it will pass the actual date value (a floating point number with value 40808.0 in this case) — Marc Gravell
– Marc Gravell, Commented Oct 5, 2011 at 8:57

Marc Gravell · Accepted Answer · 2011-10-05 08:49:23Z

7

You should handle the date-time parsing logic (in your accepted format) inside the C#, and pass that down as a parameter, i.e.

String query1 = "SELECT * FROM Flight_Schedule S WHERE S.departure_date = @departureDate"

and add a SqlParameter with the DateTime value you want; that way... no problems. No parsing at the DB, and no injection risk. And query-plan re-use too. Wins all round.

For example:

DateTime when = DateTime.Parse(ddate); // better to use ParseExact and formally state the format you are using
const string query1 = "SELECT * FROM Flight_Schedule S WHERE S.departure_date = @departureDate";

using (SqlConnection connect = new SqlConnection(conn))
{
    using (SqlCommand cmd = new SqlCommand(query1, connect))
    {
        cmd.Parameters.AddWithValue("departureDate", when);
        connect.Open();
        using (SqlDataReader result = cmd.ExecuteReader())
        {
           ... etc
        }
    }
}

edited Oct 5, 2011 at 8:49

answered Oct 5, 2011 at 7:37

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user904406 Over a year ago

what is @DepartureDate for? how do i use it?

Marc Gravell Over a year ago

@user904406 that is the parameter that you would add; 2 secs, I'll flesh out more of the example (look for an edit)

Boomer · Accepted Answer · 2013-10-18 14:51:23Z

1

Marc Gravell is absolutely right. But try this:

"SELECT * FROM Flight_Schedule S WHERE S.origin_airport_code = '" + dairport.ToString("yyyyMMdd") + "'";

edited Oct 18, 2013 at 14:51

answered Oct 5, 2011 at 7:38

Boomer

1,4781 gold badge15 silver badges19 bronze badges

2 Comments

user904406 Over a year ago

i do not have .format function for my string. how do i use it?

Boomer Over a year ago

sorry, it should be dairport.toString("yyyMMdd")

Siva Charan · Accepted Answer · 2011-10-05 07:45:53Z

0

Your error says "Conversion failed when converting date and/or time from character string."

Replace this line

DateTime date = Convert.ToDateTime(ddate);

To

IFormatProvider theCultureInfo = new System.Globalization.CultureInfo("en-GB", true);
DateTime theDateTime = DateTime.ParseExact(ddate, "mm-dd-yyyy", theCultureInfo);

Try this out...

answered Oct 5, 2011 at 7:45

Siva Charan

18.1k10 gold badges64 silver badges95 bronze badges

1 Comment

user904406 Over a year ago

sorry i dont think it has to do with convert. i had removed it and the error came from the 1st sql query which is "SELECT * FROM Flight_Schedule S WHERE S.departure_date = '24/09/2011'". a very simple query that shouldnt give any error. the 2nd sql query is just a normal query with a string of the airport code (not date).

Drakkonite · Accepted Answer · 2011-10-05 09:10:42Z

-1

As a rule of best practice using SQL date, if you have to specify date in your SQL query, always try to use ISO format (yyyy-MM-dd). In most cases it will prevent any conversion errors to appear.

In your code:

"SELECT * FROM Flight_Schedule S WHERE S.origin_airport_code = '" + ddate.ToString("yyyy-MM-dd") + "'";

Kris

answered Oct 5, 2011 at 9:10

Drakkonite

5073 silver badges9 bronze badges

Collectives™ on Stack Overflow

.net c# sql string to date conversion error

4 Answers 4

2 Comments

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related