Removing Time from DateTime column in Sql Server 2005

Well this is most common question I answered many times on Sql Server MSDN forums.

How to remove Time from Datetime column in Sql Server 2005 and 2000?

Actually this is one of the biggest drawbacks in Sql server till the Sql Server 2008 happened; that we don’t have only Date datatype. In Sql server 2000 and 2005 we have Datetime datatype, which always come with time part attached with Date. Thankfully in Sql Server 2008 we have simple Date datatype.

Let it be, we’ll see how to remove Time part while querying the Datetime column. Actually it is quite easy with simple CONVERT function. The Sql syntax for it is as follows –

SELECT CONVERT ( varchar(12), DateColumn[, Style] ) as Date

FROM TableName.

Ahh, actually I wanted to create a chart out of it, but with Blogger you can’t create one, so I ended up writing this long list. Here I used getdate function; you can put the Datetime Column name in place of getdate() of your table in following examples.

• Format: mm/dd/yy
  SELECT CONVERT( Varchar(12), GetDate(),1)
  Output: 12/27/08

• Format: mm/dd/yyyy
  SELECT CONVERT(Varchar(12),GetDate(),101)
  Output: 12/27/2008

• Format: yy.mm.dd
  SELECT CONVERT(Varchar(12),GetDate(),2)
  Output: 08.12.27

• Format: yyyy.mm.dd
  SELECT CONVERT(Varchar(12),GetDate(),102)
  Output: 2008.12.27

• Format: dd/mm/yy
  SELECT CONVERT(Varchar(12),GetDate(), 3)
  Output: 27/12/08

• Format: dd/mm/yyyy
  SELECT CONVERT(Varchar(12),GetDate(), 103)
  Output: 27/12/2008

• Format: dd.mm.yy
  SELECT CONVERT(Varchar(12),GetDate(), 4)
  Output: 27.12.08

• Format: dd.mm.yyyy
  SELECT CONVERT(Varchar(12),GetDate(),104)
  Output: 27.12.2008

• Format: dd-mm-yy
  SELECT CONVERT(Varchar(12),GetDate(), 5)
  Output: 27-12-08

• Format: dd-mm-yyyy
  SELECT CONVERT(Varchar(12),GetDate(),105)
  Output: 27-12-2008

• Format: dd mon yy
  SELECT CONVERT(Varchar(12),GetDate(), 6)
  Output: 27 Dec 08

• Format: dd mon yyyy
  SELECT CONVERT(Varchar(12),GetDate(),106)
  Output: 27 Dec 2008

• Format: mon dd, yy
  SELECT CONVERT(Varchar(12),GetDate(),7)
  Output: Dec 27, 08

• Format: mon dd, yyyy
  SELECT CONVERT(Varchar(12),GetDate(),107)
  Output: Dec 27, 2008

• Format: mm-dd-yy
  SELECT CONVERT(Varchar(12),GetDate(),10)
  Output: 12-27-08

• Format: mm-dd-yyyy
  SELECT CONVERT(Varchar(12),GetDate(),110)
  Output: 12-27-2008

• Format: yy/mm/dd
  SELECT CONVERT(Varchar(12),GetDate(),11)
  Output: 08/12/27

• Format: yyyy/mm/dd
  SELECT CONVERT(Varchar(12),GetDate(),111)
  Output: 2008/12/27

• Format: yymmdd
  SELECT CONVERT(Varchar(12),GetDate(),12)
  Output: 081227

• Format: yyyymmdd
  SELECT CONVERT(Varchar(12),GetDate(),112)
  Output: 20081227
  
  Well I already wrote one blog entry on how to convert date in required format.
  Date Formats In SQL SERVER.
  
  For more on CONVERT see
  CAST and CONVERT (Transact-SQL) Books Online
  
  - Mangal Pardeshi.