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

Question

User-797751191 posted

Hi

  In sql i have below code and value in field is 15-07-2019

Update [Test1] SET [Posting Date]=CONVERT(datetime,[P Date],120)

Thanks

Answer 1

User-1038772411 posted

Hello, jsshivalik

If you have SQL Server 2012, use WHERE TRY_CONVERT (DATE, DocDate, 120) IS NULL.

Reference link :

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9f362e03-40bf-4693-85ba-725dc6ac9441/how-to-solve-this-error-the-conversion-of-a-nvarchar-data-type-to-a-datetime-data-type-resulted-in?forum=transactsql

Thanks.

Answer 2

User-797751191 posted

Hi 

  I have sql 2008.  why the above error is coming

Thanks

Answer 3

User-1038772411 posted

Hi, jsshivalik

you can use "try_convert" instead of only "convert". may be your that is error cause.

And above link which i provided in my first answer to you kindly go there check their answers.

Thanks. 

Answer 4

User475983607 posted

I have sql 2008.  why the above error is coming

The syntax does not adhere, at all, to the SQL CONVERT reference documentation.  Unfortunately, the code shown does not make a sense without the DB schema. 

If [P Date] is a DateTime type then you do not need the CONVERT function. If the [P Date] is a VARCHAR then you have a design bug.  

Answer 5

User753101303 posted

Hi,

Try 

Update [Test1] SET [Posting Date]=CONVERT(datetime,[P Date],103)

(as I believe you have been told previously). Now you are using 120 which is for yyyy-mm-dd according to  https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017#date-and-time-styles 

Or you end up in mixing date styles into your [Posting Date] string column maybe and you need to use 103 for some rows and 120 for some other rows ??? I would consider moving away from this design....

Try :

SELECT CONVERT(DATE,'15-07-2019',103),CONVERT(DATE,'2019-07-15',120)