Datetime conversion of empty string

Question

Datetime conversion of empty string

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

steve.jacobs SSCommitted Points: 1830 More actions · Answer 1

Sean Lange (11/25/2013)
Nice easy question to start the week. I am rather shocked that 16% (101 people) at the time of this posting missed this one.

Me too :ermm:. I can only assume a vast percentage of those that missed it may have read it too quickly and could not decide if an error would have been thrown (conversion) or "thought" an empty to be NULL (conversion), so they picked the error. Who knows...

select convert(datetime, null)

which would have resulted in NULL.

Good question.

steve.jacobs SSCommitted Points: 1830 More actions · Answer 2

steve.jacobs (11/25/2013)
Sean Lange (11/25/2013)
Nice easy question to start the week. I am rather shocked that 16% (101 people) at the time of this posting missed this one.
Me too :ermm:. I can only assume a vast percentage of those that missed it may have read it too quickly and could not decide if an error would have been thrown (conversion) or "thought" an empty to be NULL (conversion), so they picked the error. Who knows...
select convert(datetime, null)
which would have resulted in NULL.
Good question.

My apologies for the code I added. I did not read the answer, thus did not see the author has already put a sample that would return NULL.:-P

Revenant SSC-Forever Points: 42467 More actions · Answer 3

It is ALWAYS good to go back to basics. Thanks, Gabe!

TomThomson SSC Guru Points: 104773 More actions · Answer 4

Richard Warr (11/25/2013)
Straightforward enough question but, as it's a DATETIME you will actually get 1900-01-01 00:00:00.000 as the result.

Glad to see I'm not the only pedant in the pool. 🙂

But I think the pedantry is incorrect in this case, because we can't write datetimes, only sequences of letters (or of ideographs in some languages, of dots and dashes in morse, of flag positions in semaphore, or of something else in some other languages or language encodings) that somehow represent them; and if we know the expression "1900-01-01" is being used (when writing in English) to represent a datetime value, we know that it represents the same value - which we could alternatively represent in many many different ways in English writing - as does "1900-01-01 00:00:00.000": the datetime which denotes the instant which lies on the boundary between the penultimate year of the 19th century of the Christian Era and the following year.

So the original anser is just as right as 1900-01-01 00:00:00.000 and actually superior to it in some ways because it wastes less space on the page.

Tom