DATE Data Type
In SQL Server 2005, there is no data specific datatype to store only a Date. You must use the DATETIME or SMALLDATETIME data types. In addition to the date you have entered, you will see a time component, which will appear as 12:00 AM. You then need to format your output to display only the date component. Most of the time you can use the getdate() function to store the current date. If you save the getdate() value in a SMALLDATETIME or DATETIME column in SQL Server 2005, you will also store the current time, which may lead many issues. For example, if you want to search records for given date and you use
SELECT * FROM tblDate Where [Date] = '2007-10-01'
It will not work properly because of the existing time component in Date column. Therefore, you need to use following query.
SELECT * FROM tblDate Where datediff(d,[Date],‘2007-10-01’) =0
While the above query will work, there is a high chance that the index that is existing for the Date column will not be used. Still you can use the above query for a small number of records.
Although there are workarounds, it is very clear that there is a need for a DATE data type to reduce time and potential errors.
DECLARE @dt as DATE
SET @dt = getdate()
The output of the above script is 2007-10-27. As you can see, there is no time component. The range for the DATE datatype is from 0001-01-01 through 9999-12-31.
Unfortunately, the color of the DATE text is not blue, which is the default for all other datatypes. This may be a bug that needs to be fixed in coming CTPs.
Similar to the Date datatype, there is a TIME datatype in cases where you need to store only the time.
The following is a sample query for using the TIME datatype.
DECLARE @dt as TIME
SET @dt = getdate()
The output of the above script is 23:48:04.0570000. The range for the TIME data type is 00:00:00.0000000 through 23:59:59.9999999.
DATETIME2 Data Type
The new DATETIME2 datetype is a date/time datatype with larger fractional seconds and year range than the existing DATETIME datatype. You have the option of specifing the number of fractions that you need. The maximum fraction you can specify is 7 while the minimum fraction is 0. The following is an example of using DATETIME2.
DECLARE @dt7 datetime2(7)
SET @dt7 = Getdate()
The result of above script is 2007-10-28 22:11:19.7030000.
The following is a list of outputs you get for each of the fractions.
Currently when saving the date and time in a column, it will not indicate what time zone that date and time belongs to. This can be especially important when you are dealing with data including several different countries with different time zones. The new datatype DATETIMEOFFSET defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock. The following script illustrates the usage of the DATETIMEOFFSET datatype.
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'
Currently we have the GETDATE function in SQL Server 2005 and SQL Server 2000 to retrieve the current date and time. Additionally, there are several other functions in SQL Server 2005, namely CURRENT_TIMESTAMP, DATEADD, DATEDIFF, DATENAME, DATEPART, DAY, GETUTCDATE, MONTH and YEAR. Apart from these functions, there are five new functions included in SQL Server 2008: SYSDATETIME, SYSDATETIMEOFFSET, SYSUTCDATETIME SWITCHOFFSET and TODATETIMEOFFSET. The SYSDATETIME function returns the current system timestamp without the time zone, with an accuracy of 10 milliseconds. The SYSDATETIMEOFFSET function is the same is the SYSDATETIME function, however includes the time zone.
SYSUTCDATETIME returns the Universal Coordinated Time (same as Greenwich Mean Time) date and time within an accuracy of 10 milliseconds. This is derived from the current local time and the time zone setting of the server where SQL Server is running. Both SYSDATETIME and SYSUTCDATETIME return DATETIME2 data type, where SYSDATETIMEOFFSET returns the DATETIMEOFFSET datatype. Following is an example of the above datatypes. SELECT SYSDATETIME()
SYSDATETIME() 2007-10-31 22:14:05.7131792
SYSDATETIMEOFFSET()2007-10-31 22:14:05.7131792 +05:45
SYSUTCDATETIME() 2007-10-31 16:29:05.7131792
CURRENT_TIMESTAMP 2007-10-31 22:14:05.710
GETDATE() 2007-10-31 22:14:05.710
GETUTCDATE() 2007-10-31 16:29:05.710