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

Question

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

Answer 1

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

• Click here to view result

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.

Answer 2

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

Answer 3

as you can see on the answer to this question: Conversion of a varchar data type to a datetime data type resulted in an out-of-range value

-- set the dateformat for the current session
set dateformat dmy

-- The conversion of a varchar data type 
-- to a datetime data type resulted in an out-of-range value.
select cast('2017-08-13 16:31:31'  as datetime)

-- get the current session date_format
select date_format
from sys.dm_exec_sessions
where session_id = @@spid

-- set the dateformat for the current session
set dateformat ymd

-- this should work
select cast('2017-08-13 16:31:31'  as datetime)

Answer 4

I struggled with the same problem. I have stored dates in SQL Server with format 'YYYY-MM-DD HH:NN:SS' for about 20 years, but today that was not able anymore from a C# solution using OleDbCommand and a UPDATE query.

The solution to my problem was to remove the hyphen - in the format, so the resulting formatting is now 'YYYYMMDD HH:MM:SS'. I have no idea why my previous formatting not works anymore, but I suspect there is something to do with some Windows updates for ADO.

Answer 5

some problem, but I find the solution, this is :

---

2 February Feb 28 (29 in leap years)

this is my code

 public string GetCountArchiveByMonth(int iii)
        {
// iii: is number of months, use any number other than (**2**)

            con.Open();

            SqlCommand cmd10 = con.CreateCommand();
            cmd10.CommandType = CommandType.Text;
            cmd10.CommandText = "select count(id_post) from posts where dateadded between CONVERT(VARCHAR, @start, 103) and CONVERT(VARCHAR, @end, 103)";
            cmd10.Parameters.AddWithValue("@start", "" + iii + "/01/2019");
            cmd10.Parameters.AddWithValue("@end", "" + iii + "/30/2019");
            string result = cmd10.ExecuteScalar().ToString();

            con.Close();

            return result;
        }

now for test

 lbl1.Text = GetCountArchiveByMonth(**7**).ToString();  // here use any number other than (**2**)

**

because of check **February** is maxed 28 days,

**