Get only date or time from a datetime column in sql server

In Sql Server we use DateTime column but in many cases we need to get either only date or only time. So we will see different ways to get these values according to our requirements. If you are using SQL Server 2008 or newer version then luckily we have two types Date and Time Let’s see this in action with Sql Server 2008 (it will not work in older version of SQL)

SELECT Getdate() [DateTime]
, Cast(Getdate() as Date)  [DateOnly]
, Cast(Getdate() as Time) [TimeOnly]
-- resultDateTime                    DateOnly        TimeOnly
2013-09-07 15:52:46.793     2013-09-07      15:52:46.7930000

As we see it is quite easy if we are using Sql Server 2008 or latest version but what about 2005 or older version, above query will not work.

So let's write query for older versions:

SELECT Getdate() [DateTime]
, CONVERT(VARCHAR, Getdate(), 101)  [DateOnly]
, CONVERT(VARCHAR, Getdate(), 108) [TimeOnly]
-- resultDateTime                    DateOnly         TimeOnly
2013-09-07 16:06:04.683     09/07/2013      16:06:04

As you can see date formatter we used is 101 and it’s date format, we will see different formatter and their results

SELECT  CONVERT(VARCHAR, Getdate(), 100)  -- Sep  7 2013  4:11PM
SELECT  CONVERT(VARCHAR, Getdate(), 101)  -- 09/07/2013
SELECT  CONVERT(VARCHAR, Getdate(), 102)  -- 2013.09.07
SELECT  CONVERT(VARCHAR, Getdate(), 103)  -- 07/09/2013    
SELECT  CONVERT(VARCHAR, Getdate(), 104)  -- 07.09.2013
SELECT  CONVERT(VARCHAR, Getdate(), 105)  -- 07-09-2013
SELECT  CONVERT(VARCHAR, Getdate(), 106)  -- 07 Sep 2013
SELECT  CONVERT(VARCHAR, Getdate(), 107)  -- Sep 07, 2013
SELECT  CONVERT(VARCHAR, Getdate(), 108)  -- 16:15:41
SELECT  CONVERT(VARCHAR, Getdate(), 109)  -- Sep  7 2013  4:15:48:243PM
SELECT  CONVERT(VARCHAR, Getdate(), 110)  -- 09-07-2013
SELECT  CONVERT(VARCHAR, Getdate(), 111)  -- 2013/09/07
SELECT  CONVERT(VARCHAR, Getdate(), 112)  -- 20130907
SELECT  CONVERT(VARCHAR, Getdate(), 113)  -- 07 Sep 2013 16:16:15:143
SELECT  CONVERT(VARCHAR, Getdate(), 114)  -- 16:16:21:890