CODEWITHSUNDEEP
c#sql-serverwinformssql-server-2005
user311509
user311509

Reputation: 2866

How Can I Customize Date Format

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?

Upvotes: 2

Views: 159

Answers (5)

AllenG
AllenG

Reputation: 8190

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, operating 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 yield a string, but passing in a DateTime will yield a DateTime.

Upvotes: 0

Julien N
Julien N

Reputation: 3920

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.

Upvotes: 0

Brandon J. Boone
Brandon J. Boone

Reputation: 16472

Try something like:

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

Upvotes: 0

shookdiesel
shookdiesel

Reputation: 277

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'))

Upvotes: 1

Ralf de Kleine
Ralf de Kleine

Reputation: 11744

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
}

Upvotes: 2

Related Questions