There are about 26 data types defined in Microsoft SQL
Server 2005. In general, they are categorized as given in the table below.
|
Numeric data type
|
Integers, Decimal, Real, Currency
|
|
String
|
Collection of characters
|
|
Temporal (Date/Time)
|
Dates, Times, or both
|
|
Other types
|
XML, Binary, Image, etc.
|
As mentioned above - Date/Time or Date data types are also
called "Temporal" data type.
Temporal (Date/Time) Date Type
Types of Date/Time data type
|
1. datetime
|
2x4Bytes
|
January 1,1900 - December 31, 9999
|
|
2. smalldatetime
|
2x2Bytes
|
January 1, 1900 - June 6, 2079.
|
Facts to know about datetime data type
·
It has size of 8 bytes
·
SQL Server does not store datetime value in string format, it
stores date time as a number.
·
SQL Server stores number for datetime value which is
calculated as the number of days before or after January
1, 1900 and number of milliseconds after midnight of the date.
·
In SQL Query we can operate DATETIME values as string as well as
using numbers.
To get the date
value by resetting time value to noon time from datetime in SQL Server
·
SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))
·
SELECT CONVERT(DATETIME, CONVERT(INT, GETDATE()))
To get only the
date part from the datetime in mm/dd/yyyy format string
·
SELECT CONVERT(VARCHAR, GetDate(),101)
To get only the time value of the datetime
·
SELECT GETDATE() - DATEADD(DD,0, DATEDIFF(DD,0, GETDATE())) AS
TIME_AT_BASE_DATE
Examples to know the facts about SMALLDATETIME and
DATETIME
Query
SELECT CONVERT(SMALLDATETIME,0)
Result
1900-01-01 00:00:00
Reason
Converting an integer value 0 into SMALLDATETIME type
results as the base (starting) date
SMALLDATETIME
range. It does not fractional part of a second.
Query
SELECT CONVERT(DATETIME,0)
Result
1900-01-01 00:00:00.000
Reason
Converting an integer value 0 into DATETIME type results as
the base starting date of DATETIME range. It does show the fractional part of a
second too.
Query
SELECT CONVERT(SMALLDATETIME,-1)
Result (Error)
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting
expression to data type smalldatetime.
Reason
A SMALLDATETIME cannot go back to January 1, 1900.
Query
SELECT CONVERT(DATETIME,-1)
Result
1899-12-31 00:00:00.000
Reason
DATETIME can go back to January 1, 1900. Integer value -1 in
DATETIME is equal to 1 day before its base date January 1, 1900 which is
December 31, 1899.
Query
SELECT CONVERT(SMALLDATETIME,'2008-09-20 01:26:29.998')
Result
2008-09-20 01:26:00
Reason
A DATETIME value (with fractional part of a second) when
converted to a SMALLDATETIME value it rounds off the second value with its fractional
part into the minute value. A second value containing its fractional part of 29.998 or less gets rounded down
to floor value of minutes. It means that second value 01:26:29.998 = 01:26:00, 01:26:29.997
= 01:26:00 in case of SMALLDATETIME.
Query
SELECT CONVERT(SMALLDATETIME,'2008-09-20 01:26:29.999')
Result
2008-09-20 01:27:00
Reason
A DATETIME value (with fractional part of a second) when
converted to a SMALLDATETIME value it rounds off the second value with its
fractional part in the minute value. A second value containing its fractional
part of 29:999 or greater gets rounded up to ceiling value. It means that second value 01:26:29.999
= 01:27:00, 01:26:30.000 = 01:27:00 in the case of SMALLDATETIME.
