SQL server errors when converting null to datetime

Question

Have you ever came across this error in SQL Server "Conversion failed when converting date and/or time from character string" my query was converting a null value to datetime and throwing that error. To fix it I had to switch from using convert to cast but I want to know why:

If you do the following:

SELECT CONVERT ( datetime, NULL, 101 )

You get no errors and it returns NULL

but when you use CHAR(0) which is equivalent to NULL

SELECT CONVERT ( datetime, CHAR(0), 101 )

You get an error, is there a difference between NULL or CHAR(0) ?

Assertion that char(0) is equivalent to null is false. select ascii(Null) B, ascii(char(0)) one is null one is not. — xQbert
– xQbert, Commented Oct 25, 2017 at 14:56

SqlZim · Accepted Answer · 2017-10-25 14:52:52Z

4

A character representing null and a null value are not equivalent.

select char(0) does not return null, it returns the character with ascii code 0.

answered Oct 25, 2017 at 14:52

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL server errors when converting null to datetime

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related