DATETIME2 Data Type

Posted: November 21, 2011 in SQL Server 2008

You can now return larger fractional seconds and year range than the existing DATETIME datatype. You have the option of specifing the number of fractions. This can be between 0 and 7 and you can get the values as follows:

-- Declare variables
DECLARE @dt0 datetime2(0);
DECLARE @dt1 datetime2(1);
DECLARE @dt2 datetime2(2);
DECLARE @dt3 datetime2(3);
DECLARE @dt4 datetime2(4);
DECLARE @dt5 datetime2(5);
DECLARE @dt6 datetime2(6);
DECLARE @dt7 datetime2(7);

-- Get current date
SET @dt0 = GETDATE();
SET @dt1 = GETDATE();
SET @dt2 = GETDATE();
SET @dt3 = GETDATE();
SET @dt4 = GETDATE();
SET @dt5 = GETDATE();
SET @dt6 = GETDATE();
SET @dt7 = GETDATE();

-- Select value
SELECT @dt0;
SELECT @dt1;
SELECT @dt2;
SELECT @dt3;
SELECT @dt4;
SELECT @dt5;
SELECT @dt6;
SELECT @dt7;
Advertisements
Comments
  1. Ryan says:

    The results if anyone wants to know…. (sql server 2012)

    2012-07-29 16:17:13
    2012-07-29 16:17:12.9
    2012-07-29 16:17:12.91
    2012-07-29 16:17:12.912
    2012-07-29 16:17:12.9118
    2012-07-29 16:17:12.91176
    2012-07-29 16:17:12.911759
    2012-07-29 16:17:12.9117592

  2. Ryan says:

    I had to use “datetime()” to get a precise time. GETDATE() rounds to 100 ms.

    SET @dt0 = sysdatetime();
    SET @dt1 = sysdatetime();
    SET @dt2 = sysdatetime();
    SET @dt3 = sysdatetime();
    SET @dt4 = sysdatetime();
    SET @dt5 = sysdatetime();
    SET @dt6 = sysdatetime();
    SET @dt7 = sysdatetime();

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s