Get date and time parts in SQL Server 2005 using Transact SQL

Datetime data type in SQL Server 2000 or SQL Server 2005 provides a way to store data in date and time format. This type of variable or field contains both date and time togather, but sometimes we need either date or time portion only separately. For that purpose we can use Transact SQL. In this article I'll explain the way we can process datetime type data and get date and time parts as string.

We'll use some variations of Convert function to get both date and time. Let say we have an AddressBook table in our database which contains an UpdateDateTime field.

First of all we'll get only the time part of this datetime field:

SELECT CONVERT(CHAR(8),UpdateDateTime,8) FROM AddressBook

Above statement will give you the time part in string or text format like 15:13:03, Sowe can use it accordingly.

Following four Transact SQL statements return date part in different formats using Convert funciton:


--Date Part 10-26-2007
SELECT CONVERT(CHAR(10),UpdateDateTime,110) FROM AddressBook
--Date Part 2007/10/26
SELECT CONVERT(CHAR(10),UpdateDateTime,111) FROM AddressBook
--Date Part 20071026
SELECT CONVERT(CHAR(10),UpdateDateTime,112) FROM AddressBook
--Date Part 26 Oct 2007
SELECT CONVERT(CHAR(11),UpdateDateTime,113) FROM AddressBook

The date format returned is given as comments above the statements.