How Can I Customize Date Format

Question

One of my table column's named "SetDate" of type DateTime looks as:

2010-08-02 02:55:58.420

The DateTime format from the application looks as:

2/11/2010

The problem is:

I'm passing an SQL query to the DB via application. The query has WHERE clause which compares SetDate to the date coming from application.

SetDate column carries this format: 2010-08-02 02:55:58.420
Date passed from application carries this format: 3/12/2010

I just need to compare the two dates without the time 2010-08-02 and 3/12/2010.
Since they are in different formats, I get no records back from the database.

I'm using C# and T-SQL.

Any ideas?

Answer 1

Try something like:

SELECT 
   * 
FROM 
   YourTable 
WHERE 
   RTRIM(CONVERT(CHAR(19), SetDate, 101)) = '3/12/2010'

Answer 2

In your Where clause, you can use the Date member of the DateTime class DateTime.Now.Date. It returns the date without the time.

As long as you work with Date Objects (.net or SQL server), the format doesn't matter, as it's your job to parse the string to object or reverse, internally the format is irrelevant.

Answer 3

Are you using a SqlCommand to run your query?

Yes? Also use SqlParameters for you users/system input.

var setDate = DateTime.Now();

using (SqlCommand command = new SqlCommand("SELECT * FROM TableX WHERE SetDate > @SetDate", connection))
{
    // Add new SqlParameter to the command.
    command.Parameters.Add(new SqlParameter("@SetDate", SqlDbType.DateTime, setDate));
    // Read in the SELECT results.
    SqlDataReader reader = command.ExecuteReader();
    //More code
}

Answer 4

On you WHERE clause for the DATETIME in the Database you need to do something like this. CONVERT(DATETIME, CONVERT(VARCHAR(11), '2010-08-02 02:55:58.420'))

Answer 5

The best way to run DB queries from code is to call stored procedures, if possible. However, whether you're doing that, or not, you'll want to use the SqlParameter object initialized with the date you need.

DateTime dateToCheck = DateTime.Now;
using(SqlCommand cmd //Set Command Here)
{
  cmd.CommandType = SqlCommandType.Procedure;
   //Doing this from memory, but that line should be pretty close
  cmd.Parameters.AddWithValue("@dateToCheck", dateToCheck);

  //Continue with call to DB as normal
}

One not here, some people do specify the type of "DateTime" on their SqlParameter object, but I've never done that, and it hasn't come back to bite me, yet. I believe (again, opperating off memory here) that if you're using a System.[whatever] type, SqlParameter can automatically assign it to the correct Sql type. So passing in a string may still yeild a string, but passing in a DateTime will yeild a DateTime.