CODEWITHSUNDEEP
c#
Lakshminarayana R
Lakshminarayana R

Reputation: 21

date in sql query with asp.net program

On the server, the date format is mm-dd-yyyy. On my local machine, the date format is mm/dd/yyyy.

I have the following code:

DateTime FrDate, Todate;

string yy = "2020";           

string mm = "04";

string dd = "01";

DateTime.TryParse(mm + "/" + dd + "/" + yy, out FrDate);

And then I am trying to run this query:

select * from helpdesk_tranactions where compl_date `= '" + FrDate.ToShortDateString() + "'

This is working fine when I run on my local machine, where the date format is mm/dd/yyyy. However, when this code is deployed to the server, the same query is not working (since the date format is mm-dd-yyyy).

The error I get is:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.

How can I write this query in such a way that it will work regardless of the system date formatting?

Upvotes: 1

Views: 726

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

Noooooooo! If you're worried about string formats for SQL date values something has already gone horribly wrong.

The column in your database should be a Date, Timestamp, or DateTime rather than varchar field, and if that's true there is no human-readable format! The data is binary. What you see is a convenience shown to you by your management tool or connection library. If you're not using one of the date types for the column column, you should fix the schema, because it really is broken.

Once you have confirmed or fixed the column so you know it is a DateTime value, you should also be using DateTime variables in the C# code. And once you're doing that, the correct way to include those values in a query looks like this (assuming MySql because of the backtick, but you should also tag the question with the correct database engine):

string sql = "select * from helpdesk_tranactions where compl_date = @compl_date";
using (var cn = new MySqlConnection("connection string here"))
using (var cmd = new MySqlCommand(sql, cn))
{
    cmd.Parameters.Add("@compl_date", MySqlDbType.DateTime).Value = FrDate;
    cn.Open();

    // ...

}

Notice this assigns the FrDate field directly to the query parameter. There's no conversion in your code to worry about.

This technique is also the best and only truly safe way to avoid certain other types of problem with sql.

Upvotes: 3

Related Questions