Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Date data type Expand / Collapse
Author
Message
Posted Wednesday, April 28, 2010 8:06 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 20,815, Visits: 32,748
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.



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)
Post #911978
Posted Wednesday, April 28, 2010 8:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:21 AM
Points: 6,098, Visits: 8,367
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
Post #912006
Posted Wednesday, April 28, 2010 8:50 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 24, 2014 8:33 AM
Points: 1,676, Visits: 1,760
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
Post #912050
Posted Wednesday, April 28, 2010 9:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 24, 2014 3:43 PM
Points: 1,142, Visits: 2,691
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
Post #912063
Posted Wednesday, April 28, 2010 10:35 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:52 AM
Points: 17,980, Visits: 15,983
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
Post #912182
Posted Wednesday, April 28, 2010 11:33 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
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
Post #912229
Posted Wednesday, April 28, 2010 11:10 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:47 AM
Points: 5,449, Visits: 1,401
Great question....


Post #912537
Posted Friday, May 7, 2010 4:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 11:06 PM
Points: 113, Visits: 85
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



Post #917761
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse