Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query

Try ISDATE() function in SQL Server. If 1, select valid date. If 0 selects invalid dates.

SELECT cast(CONVERT(varchar, LoginTime, 101) as datetime)  
FROM AuditTrail 
WHERE ISDATE(LoginTime) = 1

EDIT :

As per your update i need to extract the date only and remove the time, then you could simply use the inner CONVERT

SELECT CONVERT(VARCHAR, LoginTime, 101) FROM AuditTrail

or

SELECT LEFT(LoginTime,10) FROM AuditTrail

EDIT 2 :

The major reason for the error will be in your date in WHERE clause.ie,

SELECT cast(CONVERT(varchar, LoginTime, 101) as datetime)  
FROM AuditTrail
where CAST(CONVERT(VARCHAR, LoginTime, 101) AS DATE) <= 
CAST('06/18/2012' AS DATE)

will be different from

SELECT cast(CONVERT(varchar, LoginTime, 101) as datetime)  
FROM AuditTrail
where CAST(CONVERT(VARCHAR, LoginTime, 101) AS DATE) <= 
CAST('18/06/2012' AS DATE)

CONCLUSION

In EDIT 2 the first query tries to filter in mm/dd/yyyy format, while the second query tries to filter in dd/mm/yyyy format. Either of them will fail and throws error

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

So please make sure to filter date either with mm/dd/yyyy or with dd/mm/yyyy format, whichever works in your db.

Related Posts:

  1. Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query
  2. T-SQL split string
  3. How can I delete using INNER JOIN with SQL Server?
  4. How can I get column names from a table in SQL Server?
  5. sql query to return differences between two tables
  6. How to select the last record of a table in SQL?
  7. Inserting multiple rows in a single SQL query? [duplicate]
  8. SQL query to select dates between two dates
  9. SQL query to get the employee name and their manager name from the same table
  10. Rename column SQL Server 2008
  11. Efficiently convert rows to columns in sql server
  12. How to delete duplicate rows in SQL Server?
  13. The SQL OVER() clause – when and why is it useful?
  14. Update multiple columns in SQL
  15. SQL Query with NOT LIKE IN
  16. SQL Server IF EXISTS THEN 1 ELSE 2
  17. T-SQL split string based on delimiter
  18. SQL Query with SUM with Group By
  19. How do I perform an IF…THEN in an SQL SELECT?
  20. SQL update from one Table to another based on a ID match
  21. An attempt was made to access a socket in a way forbidden by its access permissions
  22. How to drop all tables from a database with one SQL query?
  23. SQL Server : Arithmetic overflow error converting expression to data type int
  24. How to calculate percentage with a SQL statement
  25. Query comparing dates in SQL
  26. What is the meaning of the prefix N in T-SQL statements and when should I use it?
  27. Get day of week in SQL Server 2005/2008
  28. Query error with ambiguous column name in SQL
  29. How to drop all tables from a database with one SQL query?
  30. How to find sum of multiple columns in a table in SQL Server 2005?
  31. How can I truncate a datetime in SQL Server?
  32. DateTime2 vs DateTime in SQL Server
  33. SQL Server Insert if not exists
  34. CREATE TABLE IF NOT EXISTS equivalent in SQL Server
  35. SQL Server: Invalid Column Name
  36. How to group by month from Date field using sql
  37. SQL – The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
  38. Is there a combination of “LIKE” and “IN” in SQL?
  39. Search text in stored procedure in SQL Server
  40. How to convert SQL Server’s timestamp column to datetime format
  41. How do I query for all dates greater than a certain date in SQL Server?
  42. MS SQL compare dates?
  43. MS SQL compare dates?
  44. How do I fix ‘Invalid character value for cast specification’ on a date column in flat file?
  45. Determine ROW that caused “unexpected end of file” error in BULK INSERT?
  46. Determine ROW that caused “unexpected end of file” error in BULK INSERT?
  47. Sql query to insert datetime in SQL Server
  48. What is the maximum characters for the NVARCHAR(MAX)?
  49. Using RegEx in SQL Server
  50. Microsoft OLE DB Provider for SQL Server error ‘80004005’
  51. Bulk load data conversion error (truncation)
  52. How to format a numeric column as phone number in SQL
  53. SQL – WHERE (X, Y) IN (A, B)
  54. Subtract one day from datetime
  55. SQL: IF clause within WHERE clause
  56. SQL datetime compare
  57. How do I query for all dates greater than a certain date in SQL Server?
  58. Backup a single table with its data from a database in sql server 2008
  59. How can I group by date time column without taking time into consideration
  60. SQL- Ignore case while searching for a string
  61. Comma separated results in SQL
  62. SHOWPLAN permission denied in database ‘tempdb’. in sql server 2008
  63. Filter data based on date in sql
  64. Varchar invalid for Sum operator
  65. Using Excel VBA to run SQL query
  66. What is tableName.* in SQL
  67. When should I use CROSS APPLY over INNER JOIN?
  68. Insert Data Into Temp Table with Query
  69. How to create Temp table with SELECT * INTO tempTable FROM CTE Query
  70. How to turn IDENTITY_INSERT on and off using SQL Server 2008?sql-server-2008
  71. Nested select statement in SQL Server
  72. Inserting data into a temporary table
  73. CREATE VIEW must be the only statement in the batch
  74. SQL Server FOR EACH Loop
  75. Sql Server string to date conversion
  76. Conversion failed when converting from a character string to uniqueidentifier – Two GUIDs
  77. Find all tables containing column with specified name – MS SQL Server
  78. sql server invalid object name – but tables are listed in SSMS tables list
  79. How to get a date in YYYY-MM-DD format from a TSQL datetime field?
  80. SQL Server IF NOT EXISTS Usage?
  81. String or binary data would be truncated. The statement has been terminated
  82. How to insert date values into table
  83. Error converting data type varchar to float
  84. How to concatenate text from multiple rows into a single text string in SQL Server
  85. How to join two tables by multiple columns in SQL?
  86. Count(*) vs Count(1) – SQL Server
  87. Arithmetic overflow error converting numeric to data type numeric
  88. Difference between database and schema
  89. SQL Server Linked Server Example Query
  90. How do I fix the error ‘Named Pipes Provider, error 40 – Could not open a connection to’ SQL Server’?
  91. Replacing NULL with 0 in a SQL server query
  92. SQL Server dynamic PIVOT query?
  93. Error converting data type varchar
  94. SQL Server “cannot perform an aggregate function on an expression containing an aggregate or a subquery”, but Sybase can
  95. Get everything after and before certain character in SQL Server
  96. ORDER BY items must appear in the select list if SELECT DISTINCT is specified
  97. how to increment integer Columns value by 1 in SQL
  98. What are database constraints?
  99. Inner Joining three tables
  100. Cannot insert explicit value for identity column in table ‘table’ when IDENTITY_INSERT is set to OFF

Leave a Comment