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 Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93891 Visits: 38955
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
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18683 Visits: 12426
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
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2195 Visits: 1817
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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1610 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 Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65995 Visits: 18570
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
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3566 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
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9739 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