Date and Time Manipulation in SQL Server 2000
SQL Server 2000 does not have separate data types for date and time. Instead the Microsoft SQL Server Team chose to combine both the data types into one and store it as a datetime data type. Date and time can be stored in SQL Server in datetime or smalldatetime. The datetime data type can store dates from January 1, 1753 to December 31, 9999 to an accuracy of up to 0.003 part of a second. The smalldatetime data type can store data from January 1, 1900 to June 6, 2079 with accuracy of up to the minute.
SQL Server takes into account a system reference date, which is called the base date for SQL Server. This base date is January 1st, 1900. It is from here that the main problem stems. SQL Server stores the datetime data type internally as two 4 byte integers and smalldatetime as two 2 byte integers. The first integer in both the cases stores the number of day difference from the base date. The second integer part stores the number of milliseconds/minutes since midnight.
So, the most common question that is asked is:
Q: How do I get SQL Server to return only the Date component or only the Time component from the datetime data type?
A: By using the Convert function. The syntax for using the convert function is:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
convert(varchar,DateColumn,101)
The list of styles that can be used are:
Style ID | Style Type |
0 or 100 | mon dd yyyy hh:mi AM (or PM) |
101 | mm/dd/yy |
102 | yy.mm.dd |
103 | dd/mm/yy |
104 | dd.mm.yy |
105 | dd-mm-yy |
106 | dd mon yy |
107 | Mon dd, yy |
108 | hh:mm:ss |
9 or 109 | mon dd yyyy hh:mi:ss:mmm AM (or PM) |
110 | mm-dd-yy |
111 | yy/mm/dd |
112 | yymmdd |
13 or 113 | dd mon yyyy hh:mm:ss:mmm (24h) |
114 | hh:mi:ss:mmm (24h) |
20 or 120 | yyyy-mm-dd hh:mi:ss (24h) |
21 or 121 | yyyy-mm-dd hh:mi:ss.mmm (24h) |
126 | yyyy-mm-dd Thh:mm:ss.mmm (no spaces) |
130 | dd mon yyyy hh:mi:ss:mmm AM |
131 | dd/mm/yy hh:mi:ss:mmm AM |
Reference: http://sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk