Explain TIME data type, datetime2, datetimeoffset data type in sql server 2008?
Submitted by: AdministratorTIME Data type:
TIME data type of SQL Server 2008 allows to exclusively storing the time.
The following is an example of using TIME data type:
DECLARE @dot as TIME
SET @dot = get date()
PRINT @dt
The above script displays the output as HH:MM:SS.SSSSSSS format. The TIME has the data range from 00:00:00.0000000 through 23:59:59.9999999.
DATETIME2 Data Type:
DATETIME2 is a data type which returns date/time. It provides larger factional seconds and the year compared to DATETIME data type. There are options to specify the number fractions as per the need. The maximum fraction is 7 and the minimum fraction is 0.
The following is an example of using DATETIME2 data type:
DECLARE @dt7 datetime2(7)
SET @dt7 = Getdate()
PRINT @dt7
The above script displays the date as YYYY-MM-DD HH:MM:SS.SSSSSSS format.
DATETIMEOFFSET Data type:
To store date, time along with time zone, the DATETIMEOFFSET is used. This is important when dealing with date of several countries with various time zones. The clock is based on 24-hour clock.
The following is an example of using DATETIMEOFFSET data type:
DECLARE @dt DATETIMEOFFSET(0)
SET @dt = '2007-10-29 22:50:55 -1:00'
DECLARE @dt1 DATETIMEOFFSET(0)
SET @dt1 = '2007-10-29 22:50:55 +5:00'
SELECT DATEDIFF(hh,@dt,@Dt1)
Submitted by: Administrator
TIME data type of SQL Server 2008 allows to exclusively storing the time.
The following is an example of using TIME data type:
DECLARE @dot as TIME
SET @dot = get date()
PRINT @dt
The above script displays the output as HH:MM:SS.SSSSSSS format. The TIME has the data range from 00:00:00.0000000 through 23:59:59.9999999.
DATETIME2 Data Type:
DATETIME2 is a data type which returns date/time. It provides larger factional seconds and the year compared to DATETIME data type. There are options to specify the number fractions as per the need. The maximum fraction is 7 and the minimum fraction is 0.
The following is an example of using DATETIME2 data type:
DECLARE @dt7 datetime2(7)
SET @dt7 = Getdate()
PRINT @dt7
The above script displays the date as YYYY-MM-DD HH:MM:SS.SSSSSSS format.
DATETIMEOFFSET Data type:
To store date, time along with time zone, the DATETIMEOFFSET is used. This is important when dealing with date of several countries with various time zones. The clock is based on 24-hour clock.
The following is an example of using DATETIMEOFFSET data type:
DECLARE @dt DATETIMEOFFSET(0)
SET @dt = '2007-10-29 22:50:55 -1:00'
DECLARE @dt1 DATETIMEOFFSET(0)
SET @dt1 = '2007-10-29 22:50:55 +5:00'
SELECT DATEDIFF(hh,@dt,@Dt1)
Submitted by: Administrator
Read Online SQL server 2008 Job Interview Questions And Answers
Top SQL server 2008 Questions
☺ | Explain Sparse Columns of sql server 2008? |
☺ | Do you know PowerShell included in SQL Server 2008? |
☺ | Explain how to store and query Spatial Data? |
☺ | What is Change Data Capture (CDC) feature in sql server 2008? |
☺ | Do you know Enhanced database mirroring in sql server 2008? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Sybase Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |