TIME data type

SQL Server 2008 introduced a new data type called TIME, which allow the TIME without DATE. Before SQL Server 2008 it is not possible.

In this article I am going to explain the TIME data type by creating scenario to understand it better way.

Scenario-1 [ The TIME data type ]

The Example

DECLARE @tm TIME = '16:30:12'

SELECT  @tm As [Time]

Result

Time

16:30:12.0000000

Scenario-2 [ The TIME data type Accuracy ]

The default accuracy of TIME data type is 100 nanoseconds. It also allows us to define the accuracy. This indicates how many places to the right of the decimal are stored for the second's portion. We can use 0 to 7 places to the right of the decimal.

Example

DECLARE @tm0 TIME(0) = '16:32:19.1234567',

        @tm7 TIME(7) = '16:32:19.1234567'

SELECT  @tm0 AS [Time0], @tm7 AS [Time7]

Result Set

Time0             Time7

16:32:19          16:32:19.1234567

Scenario-3 [ The TIME data type Storage ]

A TIME(0) takes three bytes to store and a TIME(7) takes five bytes to store. 

Scenario-4 [ The TIME data type Conversion ]

TIME will do the implicit conversion from DATETIME and stores only the time portion of it.

Example

DECLARE     @dt1 DATETIME = '12/29/2007 12:43:24.42',

            @tm1 TIME(2)

SELECT      @tm1 = @dt1

SELECT      @tm1 As [TimeOnly]

Result Set

TimeOnly

12:43:24.42

Scenario-5 [ The TIME data with TIME ZONE ]

The TIME() does not include any time zone information, it will accept a time with time zone information but will ignore the time zone info when displayed.

Example

DECLARE @tm TIME(0) = '12:45:11 -05:30'

SELECT  @tm AS [Time]

Result

Time

12:45:11

Hope you like it.

Posted by: MR. JOYDEEP DAS

GETDATE() Vs SYSDATETIME()

To get the current system date and time we can use GETDATE() or  SYSDATETIME() function in SQL server. 

The question is what the difference between them is.

To get the answer let execute the SQL statements mentioned bellow.

SELECT GETDATE() 'GETDATE',   SYSDATETIME() 'SYSDATETIME'

The output is mentioned bellow:

GETDATE                                 SYSDATETIME

2012-05-14 18:05:04.720        2012-05-14 18:05:04.7232705

So exactly what the difference is?

When we use GETDATE()  the precision is till milliseconds and in case of SYSDATETIME() the precision is till nanoseconds.

SYSDATETIME()  is an important when using the data type DATETIME2.

The data type DATETIME2 stores dates and times in a higher precious than the (old) data type DATETIME and therefore the difference between GETDATE() and SYSDATETIME() is important when using DATETIME2.

Conclusion

So there is really no reason to keep on using GETDATE(). And therefore the simple advice is: in SQL Server 2008 always use SYSDATETIME() when you want to retrieve the current date and/or time.

Hope you like it.

Posted by: MR. JOYDEEP DAS

Interview Question and Answer

[ New Question Added on Date: 08-MAY-2012 ]

All of my readers are invited to SQL server Interview Question and Answer. Please look at the new section and make your comments on it. 

You can find the link from my web "SQL Knowledge Bank" [Upper right Side]

Last Update Date:  08-MAY-2012 @ NEW 5 Question is Added

Posted by: MR. JOYDEEP DAS