Microsoft SQL Server supports several data types that can be used to store dates and times. Although some of them appeared in SQL Server 2008 (e.g. Date and Time data types), the Datetime and SmallDateTime data types exist since earlier versions.
The Datetime and Smalldatetime data types store date and time together in the same field. However, in several situations (like to compare fields from such types with dates or to compute totals per date or per hour), it may be necessary to split a Datetime/Smalldatetime field, removing the ‘date part’ or the ‘time part’. An easy way to do that is to convert such fields into date or time (depending on the case) using the CAST function.
Using CAST to extract dates and times
CAST is a function widely used to convert data between types in SQL Server. A complete conversion chart displaying allowed conversions (implicit and explicit) can be found here. In the following example, we declare a variable with the Datetime datatype (@DateTimeSameField). The system’s current date and time is assigned to such variable. Then, cast is used in two columns of the Select statement in order to extract the date part of datetime @DateTimeSameField (in the DatePart column) and to extract the time part of @DateTimeSameField (in TimePart column).
The result of the script execution would be as follows. It contains three columns, the first one has contains a date and time (@DateTimeSameField), while the second contains only a date and third one contains only a time representation.
Besides CAST, there are other important type conversion functions in SQL Server, like CONVERT and PARSE. A reference in CAST and CONVERT can be found here, while the transact-SQL reference for PARSE can be found here.