Home/ Questions/Fixing the error: the conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Next
Answered
Lily Evans
  • 27
Asked: 2022-05-17T14:14:19+00:00 In:

Fixing the error: the conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

  • 27

. Advertisement .

..3..

. Advertisement .

..4..

Hi developer experts, I have a small but frustrating use case, and so far, I couldn’t get my head around this problem & ideal solution. I am running my command and facing one problem with the conversion of a varchar data type to a datetime data type resulted in an out-of-range value. Below is the command I used:

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

When I run it, I get the following error:

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

I am looking forward to gaining some knowledge from all experts. Thank you, guys!

sql-server
Share

2 Answers

  1. Best Answer
    Expert

    The cause: The first query selects mm/dd/yyyy format, while the second query attempts to filter using dd/mm/yyyy format. Either of these will fail, resulting in an error.

    Solution: To fix the problem, make sure you’re filtering dates in the correct format for your database.

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

    The different one:

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

  2. We hope you find this helpful. 

    SELECT CAST(LoginTime AS DATE)
 FROM AuditTrail

    You can use built-in functions like Year and Month to filter this datetime.

    • 10

Leave an answer

You must login to add an answer.

Need An Account,