Tag Info

Hot answers tagged

5

Since BETWEEN is very problematic due to rounding of different date/time types and other problems, and since YYYY-MM-DD is not a safe format without the awkward T, an open-ended range using ISO standard full dates with no separators is a much better approach: WHERE Created >= '20141101' AND Created < '20141201';


3

I do not understand why the data is being converted from varchar to datetime when 'Created' is set to datetime The literals you are providing for comparison to the Created column are strings. To compare those literals with the datetime column, SQL Server attempts to convert the strings to datetime types, according to the rules of data type precedence. ...


2

I checked your profile and saw that you are in the UK. If your sql server is set to use the dateformat dmy then that explains your issue. Without using the 'T' instead of the space in the datetime string, Sql Server won't recognize it as ISO8601 format. Try this: select count(*) from dbo.profile where [created] between ...


1

Another alternative, I recommend using ODBC datetime literals. Despite their name they do not require you connect via ODBC. They bypass the usual conversion rules in SQL Server and are always interpreted as a datetime. SELECT COUNT(*) FROM dbo.profile WHERE [Created] BETWEEN {TS '2014-11-01 00:00:00.000'} AND {TS '2014-11-30 23:59:59.997'};



Only top voted, non community-wiki answers of a minimum length are eligible