AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=1717&pId=-1
SQL Server 2008 New Features - Date and Time
page
by Nidal Arabi
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 19684/ 75

Introduction

Microsoft SQL Server 2008 date and time variables have been criticized in the past of not being separate or having enough precision. Well, these days are over with the introduction of new data types that will allow the end user to have much more elaborate and efficiency control over this kind of data.

Requirements

In order to apply the article procedures, you should have installed any version of Microsoft SQL Server 2008 (Express, Standard, Developer, or Enterprise).

Step by Step Explanation

Follow the easy steps below to create your environment and work area.

1.    Open Microsoft SQL Server Management Studio.

2.    Right click the server name and choose New query window.

3.    Enter the listing below to create a variable of type date in MSSQL and then print its content.

Listing 1 - T-SQL to create a date variable and use it

DECLARE @Date date
SELECT @Date = GETDATE()
PRINT @Date

4.    The listing above does have a very simple yet very important implication. When you define a variable in MSSQL server as datetime you allocate 8 bytes, whereas the new date type allocates 3 bytes (a huge saving I guess).

5.    Now, moving to the time variable. SQL Server has excelled in this point. It allows you to have time data type that will store fractions of second up to 7 digits. We are talking nanoseconds here. The listing below provides some examples of using the time variable.

Listing 2 - T-SQL Showing the declaration of time data type and its usage

DECLARE 
      @SampleTime time = GETDATE(), 
      @SampleTimeP7 time(7) = GETDATE(), 
      @SampleTimeP2 time(2) = GETDATE(), 
      @SampleTimeP0 time(0) = GETDATE()
 
PRINT @SampleTime 
PRINT @SampleTimeP7
PRINT @SampleTimeP2
PRINT @SampleTimeP0

Figure 1

6.    The number inside the parenthesis indicates the second fraction precision of your content. Please note here if you do not specify a precision, then precision 7 is taken.

7.    SQL Server has also introduced a new variable data type called datetime2 which provides a combination of the date and time variables with the second precision over the standard datetime variable. Listing 3 illustrates the use of this new data type.

Listing 3 - T-SQL to create a variable of type datetime2

Declare @ExtendedDateTime as datetime2(6) = getdate()
Print @ExtendedDateTime

8.    Finally, another nifty addition that shows Microsoft cares about internationalism is the datetimeoffset variable that allows the developer to store the GMT offset inside the variable itself. Look at listing 4.

Listing 4 - T-SQL to show the use of the offset date and time variable

Declare @ExtendedDateTimeOffset as datetimeoffset(6) = getdate()
Print @ExtendedDateTimeOffset
Set @ExtendedDateTimeOffset = '2008-08-31 14:42 -06:00'
Print @ExtendedDateTimeOffset

Figure 2

Summary

The date and time variable changes are very good in the sense of storage size, precision as well as usability. I personally think that SQL Server is providing a rich set of new features in their 2008 edition. I am looking forward to seeing the next article with a new feature.



©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-07-22 4:36:55 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search