Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
SQL Server

Working with new data type DATE introduced in SQL Server 2008. (SQL Server new features)

Author: Zunnair

In this simple article you will learn that how you can use new DATE data type in sql server 2008.

Sql Server 2008 introduced new data type with name DATE. This new data type just stores the date. It is based on the Gregorian calendar and handles years from 1 to 9999.


Storage difference:


Datetime Data type in Sql Server consumes 6 Bytes to store a datetime value but Date data type takes only 3 bytes for storage. It saves many bytes like if you want to store Date of birth then normally you don’t want to save time as well. Now if you just want to store the month, day, and year and are concerned about storage size, you could use tinyints (1 byte) for the month and day and a smallint (2 bytes) for the year. This would reduce your storage size to 4 bytes, which is still greater than the 3 bytes required for the DATE type.


Internal functions support:


As we know that there are some functions uses for datEtime data type formatting like DATEPART, DATENAME, DATEDIFF, and DATEADD, these function are now supported with new DATE type also.





DECLARE @currentDate DATE

DECLARE @newyears DATE


Value Assignment:


SET @currentDate = SYSDATETIME()

SET @newyears = '2007-12-31'


Use In T-Sql:



@currentDate as 'Current Day',

DATEADD(week,5,@currentDate) as 'Five weeks from Current Day',

DATEDIFF(day,@currentDate,@newyears) as'Days until New Years Day',

DATALENGTH(@currentDate) as 'Size of date in bytes'


This data type increase proformance of your code and accelerate your work.

Add Article Comment:
Name :
Email Address :
Comments :
<< How to Generate Row Numbers using SQL 2005

Disclaimer - Privacy
© 2002-2017