Conversion error from date time to string format

Question

I have a problem with this section of code, I'm trying to insert a record into my booking table. The values I'm trying to input are (6, 3, 3, 20/06/2018 00:00:00, 400, 2800.00, True, 560.00)

        public void insertBooking(int bookingID, int customerID, int entertainmentID, 
                                  DateTime bookingDate, int numberOfGuests, double price, 
                                  bool deposit, decimal depositPrice)
        {
            db.Cmd = db.Conn.CreateCommand();
            db.Cmd.CommandText = "INSERT INTO Booking (bookingID, customerID, entertainmentID, 
                                  [Booking Date], [Number Of Guests], [Price], [Deposit?], 
                                  [Deposit Price]) " + "Values ('" + bookingID + "','" + 
                                  customerID + "','" + entertainmentID + "','" + 
                                  bookingDate + "','" + numberOfGuests + "','" + price + 
                                  "','" + deposit + "','" + depositPrice + "')";
            db.Cmd.ExecuteNonQuery();
        }

The error I'm getting is as follows,

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

I have tried to research the problem as best I can but I can't figure out how to fix this. Any help is appreciated.

Learn to use parameters for your queries. Don't munge queries with string constants. — Gordon Linoff, Apr 22 '18 at 13:51
And the fix to your problem and to SQL Injection is always the same. Use Parameters — Steve, Apr 22 '18 at 13:52
Sorry should have said they are parameters, the values I said i was inputting are just examples — Conor Casey, Apr 22 '18 at 13:53
@ConorCasey those are not sql parameters. What we are telling you is to use the Parameters collection of the Command object https://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i — Steve, Apr 22 '18 at 13:54
Your code takes the date format set in the machine where it runs. There are only two possibilities, input the date in the same locale format or explicitly set the locale for your application. From the test data, i assume it is dd/mm/yyyy hh:mm:ss, check is it the same date setting in your machine — Thangadurai, Apr 22 '18 at 13:55
@Steve oh right my bad, I understand what you mean now, Thanks for the advice but Kzrystof's answers worked for me anyway. Thanks all. — Conor Casey, Apr 22 '18 at 13:58

Soner Gönül · Accepted Answer · 2018-04-22T14:25:44.800

Okey, you have a few issues on your code. I will try to explain all of them in order.

First of all, if you use DateTime values with string concatenation (on + operator), .ToString method automatically called for them and you get may or may not "proper" textual generated for your database column. Do not choose wrong data types for your columns. You need to pass your DateTime value directly. If you don't know, which SqlDbType you know for your values, you can read the Mapping CLR Parameter Data also.

As suggested on the comments, best way to solve this kind of situations is using parameterized queries. On the other hand, these string concatenations are open for SQL Injection attacks.

Also use using statement to dispose your connection (which we don't see but..) and commands automatically instead of calling .Dispose method (which you didn't) manually.

As an example;

public void insertBooking(int bookingID, int customerID, int entertainmentID,
                          DateTime bookingDate, int numberOfGuests, double price,
                          bool deposit, decimal depositPrice)
{
    using (var con = new SqlConnection(yourConnectionString))
    using (var cmd = con.CreateCommand())
    {
        cmd.CommandText = @"INSERT INTO Booking (bookingID, customerID, entertainmentID, 
                            [Booking Date], [Number Of Guests], [Price], [Deposit?], 
                            [Deposit Price]) Values(@bookId, @cusId, @entId, @bookdate, @guests, @price, @deposit, @depositPrice)";
        cmd.Parameters.Add("@bookId", SqlDbType.Int).Value = bookingID;
        cmd.Parameters.Add("@cusId", SqlDbType.Int).Value = customerID;
        cmd.Parameters.Add("@entId", SqlDbType.Int).Value = entertainmentID;
        cmd.Parameters.Add("@bookdate", SqlDbType.DateTime).Value = bookingDate;
        cmd.Parameters.Add("@guests", SqlDbType.Int).Value = numberOfGuests;
        cmd.Parameters.Add("@price", SqlDbType.Decimal).Value = price;
        cmd.Parameters.Add("@deposit", SqlDbType.Bit).Value = deposit;
        cmd.Parameters.Add("@depositPrice", SqlDbType.Decimal).Value = depositPrice;

        con.Open();
        cmd.ExecuteNonQuery();
    }
}

Thanks this has cleared up all my issues, relatively new to SQL so not 100% with all the typical conventions, but I'll definitely be using the Parameters function from now on. Thanks a lot! — Conor Casey, Apr 22 '18 at 14:41

Kzryzstof · Answer 2 · 2018-04-22T14:15:02.777

1

To fix the error, use bookingDate.ToString("O").

That being said, it is not considered a good practice to build your SQL query like that. As mentioned in the comments, it is recommended that you use the Parameters property of your SQLCommand instance to avoid such problems.

edited Apr 22 '18 at 14:15

answered Apr 22 '18 at 14:06

Kzryzstof

7,688
10
61
108

score 0 · Answer 3 · answered Apr 22 '18 at 14:08

0

SQL Server comes with the following data types for storing a date or a date/time value in the database:

DATE - format YYYY-MM-DD.
DATETIME - format: YYYY-MM-DD HH:MI:SS.
SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS.

TIMESTAMP - format: a unique number.

DateTime your_datetime_instance = DateTime.Now;
var str = your_datetime_instance.ToString("yyyy-MM-dd");

answered Apr 22 '18 at 14:08

Steffen22

29
6

Well that's just wrong. SQL Server does not store display format for any date or datetime data type. It can implicitly convert strings of specific formats into datetime and datetime2 - and these formats are either culture-specific or ISO 8601 - so for dates use either `yyyy-MM-dd` or `yyyyMMdd`, and for datetime - `yyyy-MM-ddTHH:mm:ss`. Please note that omitting the `T` from the DateTime format will make the string representation of the Datetime value culture specific if you are converting to DateTime, but not if you are converting to DateTime2. – Zohar Peled Apr 22 '18 at 15:07

Conversion error from date time to string format

3 Answers3

Linked