SQL Server 2008 New Features - Date and Time
page 2 of 3
by Nidal Arabi
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 18606/ 60

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

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

      @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

View Entire Article

User Comments

Title: abstract of "sql server 2008"   
Name: ashutosh
Date: 2008-09-24 11:10:15 AM
informative site
Title: cant read print screens   
Name: David
Date: 2008-09-02 10:03:43 AM
Try puting the results in text or cropping the picture.

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-07-26 12:20:58 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search