Using DateTime datatype in SQL Tables

Question

I'm trying to create a method that checks a date to see if it already exists within my database. However I'm getting an exception that the program is having trouble converting from varchar. Anyone able to tell me why it's doing so?

public static int CheckDate(DateTime date)
    {
        using (SqlConnection connection = new SqlConnection(_connectionstring))
        {
            int nooforders = 0;
            connection.Open();
            string SqlQuery = string.Format("SELECT COUNT(OrderID) AS 'OrderCount' FROM OrderTable WHERE DateofCollectionDelivery = '{0}'", date);
            SqlCommand datecheck = new SqlCommand(SqlQuery, connection);
            SqlDataReader sqlDataReader = datecheck.ExecuteReader();
            while (sqlDataReader.Read())
            {
                nooforders = (int)sqlDataReader["OrderCount"];
            }
            connection.Close();
            return nooforders;
        }
    }

This is the method I run and this is the exception:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

The fields in the table have a datatype of datetime, so I'm unsure why it's throwing the exception.

Use SQL Parameters. They wont convert your date to text like that code does. — Ňɏssa Pøngjǣrdenlarp, Mar 15 '18 at 16:20
Possible duplicate of [What is passing parameters to SQL and why do I need it?](https://stackoverflow.com/questions/3216233/what-is-passing-parameters-to-sql-and-why-do-i-need-it) — Clockwork-Muse, Mar 15 '18 at 16:25
use `date.ToString("yyyy-MM-ddTHH:mm:ss")`, you can find more about date to string format in here https://blog.nicholasrogoff.com/2012/05/05/c-datetime-tostring-formats-quick-reference/ — iSR5, Mar 15 '18 at 16:26
No need to covert dates to strings in any particular format. The NET DB providers are perfectly capable of passing Dates to the database. They would be near to worthless if they couldnt — Ňɏssa Pøngjǣrdenlarp, Mar 15 '18 at 16:27
@mordechai - the OP `date` variable is a `DateTime`. `DateTime` variables _have no format_. — Chris Dunaway, Mar 15 '18 at 17:58

score 1 · Accepted Answer · answered Mar 15 '18 at 16:53

Try solution provided in following link: SQL - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

or try this one

Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query

score -1 · Answer 2 · answered Mar 15 '18 at 16:28

-1

Use the format mm/dd/yyyy or yyyy-mm-dd. use the DateTime.Parse() function to parse your date to this format.

answered Mar 15 '18 at 16:28

N.Cristian

17
4

For the -1, I should give more info's (i think). Use it like this: string SqlQuery = string.Format("SELECT COUNT(OrderID) AS 'OrderCount' FROM OrderTable WHERE DateofCollectionDelivery = '{0}'", DateTime.Parse(data, new CultureInfo("fr-FR", false)).Date); – N.Cristian Mar 16 '18 at 07:45

Using DateTime datatype in SQL Tables

2 Answers2