Skip to content
Advertisement

ERROR: varchar data type to a datetime data type resulted in error out-of-range value

I get this error message when I run the below query. Do you have any idea how I can solve this error?

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

I have been messing around with “convert” and “cast” but to date I have not had any success with this.

I tried this:

CONVERT(nvarchar, XDateUpdated, 103) AS XDateUpdated

This is what the date / time looks like in my table: 7/31/2019 3:32:23,000 AM

SELECT TOP <Max_Message_Count> 
    XDateInserted, XUserInserted, XDateUpdated, XUserUpdated, ApprovalState, 
    PersonnelNumber
FROM 
    Person

I think the problem is because no match can be made with not the datetime and timestamp.

EDIT: I looked closer at the information in my tabel. This is de date/time what my database generates:

enter image description here

Excuse me for the incorrect information

Advertisement

Answer

First, you seem to want to convert a string to a datetime value. Not the other way around.

Second, SQL Server is pretty good about converting date/time values. But commas do confuse it.

So, I think this does what you want:

CONVERT(DATETIME, REPLACE(XDateUpdated, ',', '.')) AS XDateUpdated
8 People found this is helpful
Advertisement