conversion of a varchar data type to a datetime data type resulted in an out-of-range value

by

Ambiguous date formats are interpreted according to the language of the login. This works

set dateformat mdy

select CAST('03/28/2011 18:03:40' AS DATETIME)

This doesn’t

set dateformat dmy

select CAST('03/28/2011 18:03:40' AS DATETIME)

If you use parameterised queries with the correct datatype you avoid these issues. You can also use the unambiguous “unseparated” format yyyyMMdd hh:mm:ss

More Related Contents:

  1. Ambiguous column name error
  2. How to calculate age (in years) based on Date of Birth and getDate()
  3. CROSS JOIN vs INNER JOIN in SQL
  4. INNER JOIN vs LEFT JOIN performance in SQL Server
  5. Function vs. Stored Procedure in SQL Server
  6. Get day of week in SQL Server 2005/2008
  7. Getting result of dynamic SQL into a variable for sql-server
  8. How to get difference between two rows for a column field?
  9. Simple Query to Grab Max Value for each ID
  10. SQL Server: Extract Table Meta-Data (description, fields and their data types)
  11. SQL Server 2008: how do I grant privileges to a username?
  12. SQL Server SELECT into existing table
  13. Insert Into… Merge… Select (SQL Server)
  14. The parameterized query expects the parameter which was not supplied
  15. SQL : in clause in stored procedure:how to pass values
  16. Split values over multiple rows [duplicate]
  17. Best way to do nested case statement logic in SQL Server
  18. Must declare the scalar variable
  19. How to get the identity of an inserted row?
  20. Insert Update stored proc on SQL Server
  21. SQL Server: should I use information_schema tables over sys tables?
  22. TSQL – How to use GO inside of a BEGIN .. END block?
  23. Finding a Top Level Parent in SQL
  24. How to create a Date in SQL Server given the Day, Month and Year as Integers
  25. Count records for every month in a year
  26. How can I find Unicode/non-ASCII characters in an NTEXT field in a SQL Server 2005 table?
  27. Backup SQL Schema Only?
  28. SQL Server: +(unary) operator on non-numeric Strings
  29. Parameterise table name in .NET/SQL?
  30. How to automatically generate unique id in SQL like UID12345678?

Leave a Comment