date in sql query with asp.net program

Question

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?

use parameters and DateTime directly

Selvin
– Selvin

2020-09-30 13:29:38 +00:00
Commented Sep 30, 2020 at 13:29 — Selvin
– Selvin, Commented Sep 30, 2020 at 13:29
Is your asp.net server and sql server different cultures?

mxmissile
– mxmissile

2020-09-30 14:11:03 +00:00
Commented Sep 30, 2020 at 14:11 — mxmissile
– mxmissile, Commented Sep 30, 2020 at 14:11

Joel Coehoorn · Accepted Answer · 2020-09-30 14:35:54Z

3

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.

edited Sep 30, 2020 at 14:35

answered Sep 30, 2020 at 13:45

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

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

1 Comment

Joel Coehoorn Over a year ago

Hmm... the error message looks more like it came from Sql Server, but again: until you tag the question with the correct database engine, we can't really know.

Collectives™ on Stack Overflow

date in sql query with asp.net program

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related