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

3

I have a table with a column that stores the date and time. I need to write a query to get only the date from that column,

SELECT CAST(CONVERT(VARCHAR, LoginTime, 101) AS datetime) FROM AuditTrail 

But, when I run the query I am getting this error:

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

the data in the column is datetime ex: 2012-06-18 12:08:04.000 so i need to extract the date only and remove the time note that the [Logintime] column is datatime format

This question is tagged with sql sql-server sql-server-2008 datetime

~ Asked on 2015-02-02 05:05:41

The Best Answer is


10

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.

~ Answered on 2015-02-02 05:12:31


1

hope this may help you:

SELECT  CAST(LoginTime AS DATE)
         FROM    AuditTrail 

If you want to have some filters over this datetime or it's different parts, you can use built-in functions such as Year and Month

~ Answered on 2015-02-02 12:12:45


Most Viewed Questions: