Query comparing dates in SQL

Instead of ‘2013-04-12’ whose meaning depends on the local culture, use ‘20130412’ which is recognized as the culture invariant format.

If you want to compare with December 4th, you should write ‘20131204’. If you want to compare with April 12th, you should write ‘20130412’.

The article Write International Transact-SQL Statements from SQL Server’s documentation explains how to write statements that are culture invariant:

Applications that use other APIs, or Transact-SQL scripts, stored procedures, and triggers, should use the unseparated numeric strings. For example, yyyymmdd as 19980924.

EDIT

Since you are using ADO, the best option is to parameterize the query and pass the date value as a date parameter. This way you avoid the format issue entirely and gain the performance benefits of parameterized queries as well.

UPDATE

To use the the the ISO 8601 format in a literal, all elements must be specified. To quote from the ISO 8601 section of datetime’s documentation

To use the ISO 8601 format, you must specify each element in the format. This also includes the T, the colons (:), and the period (.) that are shown in the format.

… the fraction of second component is optional. The time component is specified in the 24-hour format.

Related Posts:

  1. Inserting multiple rows in a single SQL query? [duplicate]
  2. SQL query to get the employee name and their manager name from the same table
  3. T-SQL split string
  4. The SQL OVER() clause – when and why is it useful?
  5. Update multiple columns in SQL
  6. SQL Server IF EXISTS THEN 1 ELSE 2
  7. SQL Query with SUM with Group By
  8. How do I perform an IF…THEN in an SQL SELECT?
  9. SQL update from one Table to another based on a ID match
  10. Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query
  11. How to drop all tables from a database with one SQL query?
  12. SQL Server : Arithmetic overflow error converting expression to data type int
  13. How to calculate percentage with a SQL statement
  14. What is the meaning of the prefix N in T-SQL statements and when should I use it?
  15. Query error with ambiguous column name in SQL
  16. How to drop all tables from a database with one SQL query?
  17. How to find sum of multiple columns in a table in SQL Server 2005?
  18. How can I delete using INNER JOIN with SQL Server?
  19. Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query
  20. CREATE TABLE IF NOT EXISTS equivalent in SQL Server
  21. How to group by month from Date field using sql
  22. How can I get column names from a table in SQL Server?
  23. Is there a combination of “LIKE” and “IN” in SQL?
  24. Search text in stored procedure in SQL Server
  25. MS SQL compare dates?
  26. MS SQL compare dates?
  27. How to format a numeric column as phone number in SQL
  28. SQL – WHERE (X, Y) IN (A, B)
  29. sql query to return differences between two tables
  30. SQL: IF clause within WHERE clause
  31. How to select the last record of a table in SQL?
  32. SQL- Ignore case while searching for a string
  33. Using Excel VBA to run SQL query
  34. What is tableName.* in SQL
  35. What do Clustered and Non-Clustered index actually mean?
  36. Conversion failed when converting date and/or time from character string while inserting datetime
  37. SQL query to select dates between two dates
  38. When should I use CROSS APPLY over INNER JOIN?
  39. Insert Data Into Temp Table with Query
  40. How to create Temp table with SELECT * INTO tempTable FROM CTE Query
  41. NOT IN vs NOT EXISTS
  42. Must declare the scalar variable
  43. Compare two dates with JavaScript
  44. Efficiently convert rows to columns in sql server
  45. Nested select statement in SQL Server
  46. How do I escape a single quote in SQL Server?
  47. T-SQL split string based on delimiter
  48. Data source name not found, and no default driver specified
  49. Optimistic vs. Pessimistic locking
  50. Column name or number of supplied values does not match table definition
  51. Can a foreign key be NULL and/or duplicate?
  52. When to use SELECT … FOR UPDATE?
  53. Difference between JOIN and INNER JOIN
  54. The target principal name is incorrect. Cannot generate SSPI context
  55. The EXECUTE permission was denied on the object ‘xxxxxxx’, database ‘zzzzzzz’, schema ‘dbo’
  56. BCP error “Unable to open BCP host data-file”
  57. “CASE” statement within “WHERE” clause in SQL Server 2008
  58. What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()?
  59. Conversion failed when converting from a character string to uniqueidentifier – Two GUIDs
  60. Operand type clash: int is incompatible with date + The INSERT statement conflicted with the FOREIGN KEY constraint
  61. Keeping it simple and how to do multiple CTE in a query
  62. Find all tables containing column with specified name – MS SQL Server
  63. DateTime2 vs DateTime in SQL Server
  64. Difference between numeric, float and decimal in SQL Server
  65. SQL Server Insert if not exists
  66. SQL Server: Invalid Column Name
  67. sql server invalid object name – but tables are listed in SSMS tables list
  68. How to get a date in YYYY-MM-DD format from a TSQL datetime field?
  69. What is the equivalent of ‘describe table’ in SQL Server?
  70. How do I query for all dates greater than a certain date in SQL Server?
  71. String or binary data would be truncated. The statement has been terminated
  72. Add a column with a default value to an existing table in SQL Server
  73. Error converting data type varchar to float
  74. How to concatenate text from multiple rows into a single text string in SQL Server
  75. Count(*) vs Count(1) – SQL Server
  76. ClassNotFoundException – com.microsoft.jdbc.sqlserver.SQLServerDriver
  77. The SELECT permission was denied on the object ‘Users’, database ‘XXX’, schema ‘dbo’
  78. How do you open an SDF file (SQL Server Compact Edition)?
  79. Using RegEx in SQL Server
  80. Conversion failed when converting the varchar value ‘simple, ‘ to data type int
  81. Trouble Connecting to sql server Login failed. “The login is from an untrusted domain and cannot be used with Windows authentication”
  82. Bulk load data conversion error (truncation)
  83. Difference between database and schema
  84. ‘Microsoft.ACE.OLEDB.16.0’ provider is not registered on the local machine.
  85. SQL Server Linked Server Example Query
  86. How do I format a number with commas in T-SQL?
  87. SQL datetime compare
  88. SQL Server dynamic PIVOT query?
  89. Incorrect syntax near ”
  90. Error converting data type varchar
  91. SQL Server “cannot perform an aggregate function on an expression containing an aggregate or a subquery”, but Sybase can
  92. To add server using sp_addlinkedserver
  93. Get everything after and before certain character in SQL Server
  94. How to connect to a local database in SQL Server Management Studio?
  95. What are named pipes?
  96. Inner Joining three tables
  97. Comma separated results in SQL
  98. INSERT VALUES WHERE NOT EXISTS
  99. How to implement LIMIT with SQL Server? [duplicate]
  100. Cannot insert explicit value for identity column in table ‘table’ when IDENTITY_INSERT is set to OFF

Leave a Comment