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