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]

-- result
DateTime                    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]

-- result
DateTime                    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
Hamden Process manager with a reputed organization, Fond of learning new features and technology related to C#, ASP.Net, SQL Server, MVC etc.I like to help others, if I can
  • sql server
By Hamden On 07 Sep, 13  Viewed: 392

Other blogs you may like

Adding identity to an existing column in SQL Server

We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column 1. Create a new... By Ali Adravi   On 02 May 2013  Viewed: 328

some useful sql tricks

There are many ways which can help us to save our time to write some repeated code or to do some repeated action, in this post we will see some very common code which we need very frequently in our development life. I am not saying that after this post you will save your half say work every day but... By Ali Adravi   On 31 Mar 2013  Viewed: 443

Get precision and decimal value in SQL Server by using PARSENAME

In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty   On 26 Mar 2013  Viewed: 1,026

Available wildcards in sql server with examples

To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D   On 25 Feb 2013  Viewed: 299

Concatenate multiple columns in SQL Server with NULL value

When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi   On 13 Feb 2013  Viewed: 4,364