SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Date data type


Date data type

Author
Message
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39039 Visits: 38514
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10879 Visits: 11978
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1775 Visits: 1813
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
Trey Staker
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1266 Visits: 2788
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
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32008 Visits: 18551
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

john.arnott
john.arnott
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1960 Visits: 3059
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
Anipaul
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7235 Visits: 1407
Great question....



Parag_79
Parag_79
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 105
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search