prepared statement and date quality?

  • Thread starter Thread starter me
  • Start date Start date
M

me

Why am i not getting a match with this search:

string sql = "select ... from ... where somedate = @fd";
cmd.CommandText = sql;
cmd.Parameters.Add(new SqlParameter("@fd",System.Data.SqlDbType.DateTime));
cmd.Parameters["@fd"].Value = somedatetimevalue;
cmd.Prepare();
cmd.ExecuteNonQuery();

I know the datetime value I use exists in the database (sql server).

If server and client does not use the same locale, does prepare()
take this into account?
 
Found out the millisecs are not compared correctly in the sql statement.
One solution is to make sure millisecs are 0 when storing a datetime
in the database, but I would prefure to build a sql statement that ignores
millis when comparing for equality - just dont know how to.
 
You can try using DAY, MONTH, YEAR etc. functions in the query. i.e.

Select * FROM SomTable WHERE YEAR(DateColumn)=@ParamYear AND
MONTH(DateColumn)=@ParamMonth AND DAY(DateColumn)=@ParamDay

Then just set the parameters to the corresponding property of the DateTime
instance you want to compare to. I can't tell you what is the difference in
the performace in big tables though.

Hope this helps
Martin
me said:
Found out the millisecs are not compared correctly in the sql statement.
One solution is to make sure millisecs are 0 when storing a datetime
in the database, but I would prefure to build a sql statement that ignores
millis when comparing for equality - just dont know how to.



me said:
Why am i not getting a match with this search:

string sql = "select ... from ... where somedate = @fd";
cmd.CommandText = sql;
cmd.Parameters.Add(new SqlParameter("@fd",System.Data.SqlDbType.DateTime));
cmd.Parameters["@fd"].Value = somedatetimevalue;
cmd.Prepare();
cmd.ExecuteNonQuery();

I know the datetime value I use exists in the database (sql server).

If server and client does not use the same locale, does prepare()
take this into account?
Click to expand...
Click to expand...
 
Back
Top