Update Query (String --> Date)

Question

I'm trying to update field (Type of Date) from a string field.

The string field example : 20/10/2015

I am new in sql. I tried this query:

UPDATE [dbo].[EmployeeWithCompCar]
SET [EMER_AttachEndDate] = cast([EMER_info1] as date)

I get this message:

Conversion failed when converting date and/or time from character string.

Can you help me fix it?

Thank's!

score 0 · Answer 1 · answered Nov 01 '15 at 13:24

0

You need to use convert() with a format. So try this:

UPDATE [dbo].[EmployeeWithCompCar]
    SET [EMER_AttachEndDate] = convert(date, [EMER_info1], 103);

The formats are available in the documentation.

Note: In SQL Server 2012+, use try_convert() instead of convert(). That way, if a string is in the wrong format, the result is NULL instead of an error.

answered Nov 01 '15 at 13:24

Gordon Linoff

1,242,037
58
646
786

Thank you. But if the string is empty It is updating to 1900-01-01. How to change this to not change it from null? – oco Nov 01 '15 at 14:00
Add `where EMER_INFO1 is not null` to the `UPDATE`. – Gordon Linoff Nov 02 '15 at 03:17

score 0 · Answer 2 · answered Nov 01 '15 at 15:26

You can use CONVERT with the format style 103.

UPDATE [DBO].[EMPLOYEEWITHCOMPCAR]
    SET [EMER_ATTACHENDDATE] = CONVERT(DATE, [EMER_INFO1], 103)

And if you don't blank to be updated to 1900-01-01 you can use NULLIF function to avoid that.

UPDATE [DBO].[EMPLOYEEWITHCOMPCAR]
    SET [EMER_ATTACHENDDATE] = CONVERT(DATE, NULLIF([EMER_INFO1], ''), 103)

Date Formats in SQL Server

NULLIF

Update Query (String --> Date)

2 Answers2