Saturday, December 27, 2008

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:
    SELECT CONVERT(Varchar(12),GetDate(),2)
    Output: 08.12.27

  • Format:
    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:
    SELECT CONVERT(Varchar(12),GetDate(), 4)
    Output: 27.12.08

  • Format:
    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

1 comment:

  1. Go to Layout, Right Click on the Field, hit Properties, click on Format, click "..." under Format Code, click Date and select from there. MUCH EASIER!