DateTime

[Roy]

I need to compare a datetime value against the value in a datetime column in
the WHERE clause as accurate as ticks (100 ns). When I construct a query by
string.Format("WHERE MyDatetimeColumn = '{0}'", myDatetime);
I will get
WHERE MyDatetimeColumn = '6/6/2008 3:21:01 PM'
However, this is only for second. How do I get ticks for the query?
Thanks.

 

[KH]

You need to use one of the datetime format strings that includes
milliseconds. Look up DateTimeFormatInfo in MSDN fro them.

Also, if you don't mind some unsolicited input, a couple things that might
help,

One is that it is best practice for SQL Server (not clear that you're using
sql server but it's probably true for other dbs as well) is to use non
culture specific date formats, because in the US 8/9/08 is "Aug 9" but in UK
that is "Sept 8th". The format in SQL Server is 'YYYYMMDD HH:MM:SS.FFF'
So for example August 9th is '20080809 14:15:36.789'
You can put dashes in the date if you want: '2008-08-09 14:15:36.789'

The second and most important thing is building queries like that leaves you
vulnerable to SQL injection attacks -- google that term for more info, but to
sum up it's bad. You should use parameterized queries unless it's absolutly
impossible to do otherwise.

HTH

 

[Jon Skeet [C# MVP]]

I need to compare a datetime value against the value in a datetime column in
the WHERE clause as accurate as ticks (100 ns). When I construct a query by
string.Format("WHERE MyDatetimeColumn = '{0}'", myDatetime);
I will get
WHERE MyDatetimeColumn = '6/6/2008 3:21:01 PM'
However, this is only for second. How do I get ticks for the query?

Don't construct the query by formatting the value in the SQL. Use a
parameterised query instead. It's very, very rarely a good idea to
embed a value directly in the SQL. Using parameterised queries protects
you from SQL injection attacks, escaping issues, and i18n issues.

 

[qglyirnyfgfo]

One is that it is best practice for SQL Server (not clear that you're using

sql server but it's probably true for other dbs as well) is to use non
culture specific date formats, because in the US 8/9/08 is "Aug 9" but in UK
that is "Sept 8th". The format in SQL Server is 'YYYYMMDD HH:MM:SS.FFF'
So for example August 9th is '20080809 14:15:36.789'
You can put dashes in the date if you want: '2008-08-09 14:15:36.789'

Hi KH,

Would you still have to worry about this if you where using a
parameterized query where the value of the sql parameter was set using
an actual date variable?

I would assume you would have to worry about it that way but I am
wondering if I am missing something.

Thanks.

 

[Arne Vajhøj]

Would you still have to worry about this if you where using a
parameterized query where the value of the sql parameter was set using
an actual date variable?

No. The ADO.NET provider and/or the database handles it all for you.

I would assume you would have to worry about it that way but I am
wondering if I am missing something.

DateTime is really a binary value specifying number of some units
since a specific time, which is completely culture neutral.

Ofcourse you may need to consider format if you need to convert from
string representation to System.DateTime struct, but you can do that
without having to worry about the database.

Arne

 

[Chris Jobson]

I need to compare a datetime value against the value in a datetime column

in
the WHERE clause as accurate as ticks (100 ns). When I construct a query
by
string.Format("WHERE MyDatetimeColumn = '{0}'", myDatetime);
I will get
WHERE MyDatetimeColumn = '6/6/2008 3:21:01 PM'
However, this is only for second. How do I get ticks for the query?

If is is SQL Server then (at least for SQL Server 2005) then you won't get
that accuracy. From the documentation: "Values with the datetime data type
are stored internally by the SQL Server 2005 Database Engine as two 4-byte
integers. The first 4 bytes store the number of days before or after the
base date: January 1, 1900. The base date is the system reference date. The
other 4 bytes store the time of day represented as the number of
1/300-second units after midnight."

Chris Jobson

 

[Roy]

Sql Server 2008 has a datetime2 data type with ticks (100ns). What is the
TSQL syntax to for the query?

 

[Roy]

I am using the query for a SqlDataAdaptor to populate the DataSet a table. I
use SqlCommandBuilder to create Insert/Update/Delete query. Can parameterized
query be used in such a case?

 

[Jon Skeet [C# MVP]]

I am using the query for a SqlDataAdaptor to populate the DataSet a table. I
use SqlCommandBuilder to create Insert/Update/Delete query. Can parameterized
query be used in such a case?

I would certainly imagine so, although I haven't personally used
SqlCommandBuilder myself. I'd ask on the ADO.NET newsgroup though -
they're bound to know more there.