I stepped on something that appears to me an incoherent datetime format.
If you do:
select getdate() as date
union all
select NULL`
then you get:
date
2015-12-22 12:58:37.650
NULL
the date column is of type DATETIME, but it returns a NULL instead of the 1900-01-01 00:00:00.000 default "Null"-date. NULL shouldn't exist as a datetime value.
Any comment?
Martin
You're confusing NULL and BLANK. There is nothing that prevents most any column/datatype from being NULL except a constraint on the column.
Yes, you are right. Actually I thought that datetime for some reason could not be NULL. Lesson learned. Thank you!
All data types in SQL are NULL-able by definition. But I see your mindset is still back in the old Sybase/UNIX days.Microsoft is trying to get up to standards, so you should too.
"datetime2" is a completely different data type from "date", so it's definitely not better to use it, rather it's 100% incorrect, as it will give you the wrong data type. Since there's already a data type assigned to the column from the SELECT above it, NULL by itself should be preferred. Then, if the base type in the top SELECT does need to change, no change is required to the NULL entry.
Also, fyi, MS has had issues with not properly recognizing data types in upper case under certain conditions (note, too, that in sys.types, all the data types are in lower case). Much safer to use only lower case for data type names.
1.The old getdate() was replaced with the ANSI/ISO Standard CURRENT_TIMESTAMP years ago.
Ah... but it wasn't "Replaced" because it's still available and has not been deprecated. Unless you believe in the myth of seemless migratable code or have an absolute need to migrate code on a regular basis, I see nothing wrong with continuing to use GETDATE(). Heh... it's even less typing. 
2.We now have DATE, TIME and DATETIME(n) data types. DATE is the most important. Yeah, it ought to be TIMESTAMP(n), but Microsoft screwed up with that reserved wor {sic}.
They also screwed up by cutting out half the functionality of those data types. You can no longer do direct simple arithmetic to do simple things like subtracting a date/time from another to get elapsed time. Instead, you have to go through some comparatively elaborate calculations. I also continue to answer questions about how to recombine DATE and TIME because people think they need to split them apart when both are available because they don't understand either.
3.The use of SELECT without a FROM is also Sybase dialect.
So what? You think that's somehow better than having to select from a hack table like "DUAL" in Oracle?
4.It is better to use CAST(NULL AS DATETIME2(0)), so you havef {sic} control and documentation.
Unless you're using SELECT/INTO, no it's not. You don't do that for hard coded dates and you don't need to do it for a NULL
Ah... but it wasn't "Replaced" because it's still available and has not been deprecated.
Yes, just like *= can still be used. I always hated the phrase "legacy code" and thought it should be "family curse" code instead.
Unless you believe in the myth of seamless migratable code or have an absolute need to migrate code on a regular basis,
Well, I paid for my house on this myth. 
And wrote a series s of books on it. And been paid $$$ per day for it.
I fix code written by cowboy coders to get their dialect code into a portable format for major corporations.No real ship is one-SQL only today!
Five decades ago I used to do the same thing with FORTRAN. Remember that 80 – 90% of the cost of a system is in maintaining it, not in writing it. The more portable the code, the cheaer it is in the long run. Want to see all of the research by SEI, DOD, IBM I EEE, Weinberg, etc.?
I see nothing wrong with continuing to use GETDATE().
Gee, it does not port! Please a lowercase getdate(), so we will know that it came from the C programming language in UNIX so many decades ago. I find this makes the mindset errors easier to find.
they also screwed up by cutting out half the functionality of those data types. You can no longer do direct simple arithmetic to do simple things like subtracting a date/time from another to get elapsed time.<<
Do you know the ANSI/ISO standards for temporal math? Thanks to having done this with the original Sybase/UNIX model, they cannot implement the ANSI/ISO operations immediately. It will take time just as getting INNER, OUTER JOIN, MERGE, DATE and TIME took a few releases.
You and in I also continue to answer questions about how to recombine DATE and TIME because people think they need to split them apart when both are available because they don't understand either.<<
AMEN! Amen. I wish T–SQL had the extract () function from ANSI/ISO.
[It is better to use CAST(NULL AS DATETIME2(0)), so you have control and documentation].
Unless you're using SELECT/INTO, no it's not. You don't do that for hard coded dates and you don't need to do it for a NULL
The optimizer/compiler will remove the extra coding. But it is nice for people to see the data types. Also, some also select into in T-SQL dialect is not ANSI/ISO standard SQL. This is called a Singleton select and you might want to Google it. Nobody uses it. Those of us who speak real SQL, would use INSERT INTO instead. And our code would port
I also use "ELSE NULL END" or "CAST (NULL AS )" with CASE expressions, etc. for the same reason.
You really need to spend some time studying SQL Server, Joe. There are two SELECT/INTOs. One does as you say. The other is a powerful minimally-logged capable tool that you seem to know nothing about.
Heh... and I've paid for my house by fixing slow "Ansi Only" code for people that wrote code for portability that never occurred.
P.S. You're not allowed to use the second bathroom in your home because others may not have one and it may not "port".