CODEWITHSUNDEEP
sql-serverdatetime
GuidoG
GuidoG

Reputation: 12014

SQL Server compare datetime fields

I have a datetime field in my table that I need to use in a where clause. The fieldname is DatumAanname In my dataviewgrid it shows for example "16/12/2014 15:57:04"

I looked at the page from microsoft with all the datetime options for a convert, but this format does not seems to be on that page. The closest format I can see there is "16 12 2014 15:57:04" which should be 113

I tried a query like this

SELECT o.DatumAanname,
    (convert(DATETIME, '16 dec 2014 15:57:04:000', 113)),
    (convert(DATETIME, '16 dec 2014 15:57:04', 113)),
    (convert(DATETIME, o.DatumAanname, 113))
FROM vwOpdracht o

this returns 4 fields which all look exact the same

but when I do

where (convert(datetime, o.DatumAanname, 113)) = (convert(datetime, '16 dec 2014 15:57:04:000', 113))

it returns 0 records

What am I doing wrong here ?

Upvotes: 0

Views: 6888

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131180

Dates have no formats, they are native types with their own binary storage. Formats and conversions apply only when converting from/to string and they frequently cause problems if incompatible collations and cultures are used.

The best way to avoid problems is simply to remove strings entirely: use date-typed columns in the table and query using parameterized queries. Actually, if the date is stored as text in the table there is no generic and fault-proof way to acoid problems.

Pass strongly typed values instead of strings (eg DateTime in C#, date, datetime,datetime2 in T-SQL). For example:

where o.DatumAanname= @dateParam

will return matching entries if the values match.

If passing date parameters isn't possible, you should only use the unambiguous ISO8691 format, eg:

where o.DatumAanname = '2014-12-16T15:57:04'

If you want to pass date-only values you can use the unseparated format which is also unambiguous

where o.DatumAanname = '20141216'

One thing to note is that date values must match exactly, down to the millisecond. Typically dates are used in range queries where this isn't an issue. In equality queries though, this can be a problem.

You can avoid this by using datetime2(0) as the field type, which ensures that milliseconds aren't stored at all.

Another option is to use a range instead of an equality query, eg:

where o.DatumAanname between '2014-12-16T15:57:04' and '2014-12-16T15:57:04.999'

or

where o.DatumAanname between '2014-12-16T15:57:00' and '2014-12-16T15:57:59'

if you want minute-level precision

Upvotes: 1

Related Questions