How to select only date or time part from DateTime in SQL Server

When you run following command:

SELECT GETDATE()

you will get a result similar to this:

2012-05-25 20:24:17.590

with time included along with date.

SQL Server 2008 and later versions:

If you are using SQL Server 2008 or later, you can convert datetime to new data types:

SELECT CONVERT(DATE, GETDATE())

SELECT CONVERT(TIME, GETDATE())

You can convert datetime to varchar (supported also in SQL Server 2005)
To select only date part from DateTime in SQL Server run one of these commands, depending of the output format needed (for date example May 25th 2012):

COMMAND: RESULT:
select convert(varchar, getdate(), 1)
05/25/12
select convert(varchar, getdate(), 2)
12.05.25
select convert(varchar, getdate(), 3)
25/05/12
select convert(varchar, getdate(), 4)
25.05.12
select convert(varchar, getdate(), 5)
25-05-12
select convert(varchar, getdate(), 6)
25 May 12
select convert(varchar, getdate(), 7)
May 25, 12
select convert(varchar, getdate(), 10)
05-25-12
select convert(varchar, getdate(), 11)
12/05/25
select convert(varchar, getdate(), 12)
120525
select convert(varchar, getdate(), 101)
05/25/2012
select convert(varchar, getdate(), 102)
2012.05.25
select convert(varchar, getdate(), 103)
25/05/2012
select convert(varchar, getdate(), 104)
25.05.2012
select convert(varchar, getdate(), 105)
25-05-2012
select convert(varchar, getdate(), 106)
25 May 2012
select convert(varchar, getdate(), 107)
May 25, 2012
select convert(varchar, getdate(), 110)
05-25-2012
select convert(varchar, getdate(), 111)
2012/05/25

To select only time part from DateTime in SQL Server run one of these commands, depending of the output format needed:

COMMAND: RESULT:
select convert(varchar, getdate(), 8)
20:48:03
select convert(varchar, getdate(), 108)
20:48:03
select convert(varchar, getdate(), 14)
20:48:03:603
select convert(varchar, getdate(), 114)
20:48:03:603