Date data type

Question

Date data type

Viewing 13 posts - 16 through 27 (of 27 total)

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

Ray K SSC-Dedicated Points: 31163 More actions · Answer 1

Wow . . . I was going to answer (1), until I tried running the query, and was surprised by what came back.

I was skeptical when I first read the question, but it turned out to be better than I expected. Good question!

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

webrunner SSC-Dedicated Points: 31863 More actions · Answer 2

Great question. I think the answer choices were suitably plausible, so it made it hard for me to guess. I happened to guess right. 🙂 Seriously, though, this is good information to know, and the question illustrated it well.

I have only one suggestion. Most other answers include a reference link, and I didn't see one for this question. It's easy enough to look up in Books Online, but if you post future questions, including the link will allow people to click right over to it.

Thanks!

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Paul White SSC Guru Points: 150467 More actions · Answer 3

Hugo Kornelis (4/28/2010)
What I dislike about the question is:
* The date format. Not all locale settings recognise "Dec" as december. (Try adding "SET LANGUAGE German;" as the first line - at least on SQL Server 2005 and with the data type changed to datetime, that results in a conversion error)
* The missing semicolons. In SQL Server 2008, not terminating statements with a semicolon is deprecated.
* There were two SELECT statements, so the incorrect answer options should have included two results sets. (I guess the author intended the first SELECT to read "SELECT @Today = @Today + 1;" - yet another reason to prefer SET over SELECT).

My sentiments exactly. A good question - but a shame about some of the details.

For me (to read carefully and think instead of just shooting from the hip), and for many others (that adding an integer to a date is not supported - just as it should never have been supported for datetime).

I tend to agree (and wish for a proper interval data type).

However, it appears that Steve Kass disagrees (or did at the time):

https://connect.microsoft.com/sql/feedback/ViewFeedback.aspx?FeedbackID=290011

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 4

Good question. I've learned the hard way not to assume I know the answer. I would not have expected the error in this case, and something else new about date data types.

Converting oxygen into carbon dioxide, since 1955.

Chad Crawford SSChampion Points: 11610 More actions · Answer 5

This was quite a surprise to me. I would have expected the Date and Datetime to have worked the same way, but I guess that's why I come back here every day.

Hugo Kornelis (4/28/2010)
In SQL Server 2008, not terminating statements with a semicolon is deprecated.

This was even a bigger surprise. I saw that you could do it in 2K8, but didn't know that not doing it was deprecated. Wow. One more thing to add to the upgrade list.

Thanks,

Chad

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

In SQL Server 2008, just as with the DATE data type, this is also true of datetimeoffset and datetime2. If you have SQL Server 2008 available, try those data types as well.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 7

Chad Crawford (4/28/2010)
This was quite a surprise to me. I would have expected the Date and Datetime to have worked the same way, but I guess that's why I come back here every day.
Hugo Kornelis (4/28/2010)
In SQL Server 2008, not terminating statements with a semicolon is deprecated.
This was even a bigger surprise. I saw that you could do it in 2K8

You could do it in SQL 2005 and SQL 2000 as well. Probably in 7.0 too, maybe even before that, but my memory sometimes fails me.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 8

skjoldtc (4/28/2010)
Good question. The difference between DATE and DATETIME in SQL Server 2008 is important. Thanks.
The answer 40147 looked familiar to me. It is what I have known to be called a "hundred-year" format date. I have seen it used on the IBM i. 40147 is that format equivalent to 2009-12-01. It's not really important. I just found it interesting.

The functionality to allow conversion of int to datetime exists in SQL Server 2005/2008 and it is identical. Datetime needs 8 bytes of storage (2 integers). One int (first 4 bytes) is used to store the number of days from the zero datetime ('1900-01-01') and the other to store the number of ticks from midnight. This is why you can add number to an instance of a date.

select cast(40147 as datetime); -- displays 2009-12-02 00:00:00.000

select cast(-53690 as datetime); -- displays smallest datetime value '1753-01-01'

select cast(getDate() as int); -- displays 40294

select getDate() + 1; -- displays tomorrows date same time

The reason the latter works (or why the addition of int to a datetime works) is because 1 is first implicitly converted to datetime ('Dec 2 1900') and + operator is legal with datetime values. So to the database engine the statement

select getDate() + 1;

is identical to

select getDate() + cast(1 as datetime); -- 1 as datetime is '1900-01-02'

is identical to

select getDate() + cast('1900-01-02' as datetime);

This is the reason it works (All of the above assumes En-US format yyyy-mm-dd).

It looks like with date data type in SQL Server 2008 the situation is different because date needs 3 bytes of storage and therefore it would be stupid for the database team to allow implementation of addition of a 4 byte int to the 3 byte struct. Thus, they don't allow it raising operand clash exception.

Oleg

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 9

I almost missed this one but reread it at the last moment before hitting submit and saw DATE instead of DATETIME. I've always used the DATEADD but have supported a lot of code written by other with code like getdate() + 1.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Thanks for the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

john.arnott SSChampion Points: 11882 More actions · Answer 11

webrunner (4/28/2010)
........ Most other answers include a reference link, and I didn't see one for this question.........................

To save anyone the trouble of finding where the heck this is, it's documented under the "+" arithmetic add operator:

Syntax
expression + expression
Arguments
--------------------------------------------------------------------------------
expression
Is any valid expression of any one of the data types in the numeric category except the bit data type. Cannot be used with date or time data types.

Note that last sentence -- you can't add with DATE or TIME datatypes.

See http://msdn.microsoft.com/en-us/library/ms178565(v=SQL.100).aspx

Anipaul SSC-Insane Points: 24681 More actions · Answer 12

Anipaul

SSC-Insane

Points: 24681

April 28, 2010 at 11:10 pm

#1159421

Great question....

Parag_79 SSC Enthusiast Points: 177 More actions · Answer 13

I nearly goofed it up. Managed to get correct answer.

Explanation was eyeopener, I must say that I need to start more work on SQL 2K8.

Its a good question.

Regards,

Parag