How to get Date Part only from DateTime in Sql Server

Many times we come across a scenario where we need to get Date Part only from DateTime in Sql Server. There are multiple ways of doing this, here I am listing out few of them:

1) Below approach works in Sql Server 2008 and above:

SELECT CONVERT (DATE, GETDATE()) 'Date Part Only'

RESULT:
Date Part Only
--------------
2013-07-14

2) Below approaches works in all the versions of Sql server

i) Get Date Part only from DateTime using CONVERT function
Example 1:

SELECT CONVERT(VARCHAR(10), GETDATE(), 112) 'Date Part Only'

RESULT:
Date Part Only
--------------
20130714

Example 2:

SELECT CONVERT(VARCHAR(10), GETDATE(), 111) 'Date Part Only'

RESULT:
Date Part Only
--------------
2013/07/14

The results of the above query is of type VARCHAR, if we want the result to be of type DATETIME we can write a query like below:

Example 1:

SELECT CONVERT(DATETIME,
			CONVERT(VARCHAR(10), GETDATE(), 112)) 'Date Part Only'

RESULT:
Date Part Only
-----------------------
2013-07-14 00:00:00.000

Example 2:

SELECT CONVERT(DATETIME,
			CONVERT(VARCHAR(10), GETDATE(), 111)) 'Date Part Only'

RESULT:
Date Part Only
-----------------------
2013-07-14 00:00:00.000

ii) Get Date Part only from DateTime using DateTime functions

From performance perspective this is the better approach instead of first converting DATETIME to VARCHAR and then VARCHAR to DATETIME.

Example 1:

SELECT DATEADD(dd, 0, 
        DATEDIFF(dd, 0, GETDATE())) 'Date Part Only'

RESULT:
Date Part Only
-----------------------
2013-07-14 00:00:00.000

Example 2:

DECLARE  @DatePartOnly DATETIME
SEt @DatePartOnly = DATEDIFF(DD, 0, GETDATE())
SELECT @DatePartOnly 'Date Part Only'

RESULT:
Date Part Only
-----------------------
2013-07-14 00:00:00.000

iii) Get Date Part only from DateTime using FLOOR and CAST functions

As we know Sql Server internally stores DATETIME as two 4-byte integers. First 4-byte stores the elapsed number days since SQL Server’s DATETIME type’s start date 19000101.The Second 4-bytes Store the Time of Day i.e. clock-ticks since midnight. Each clock-tick is equivalent to 3.33 milliseconds.

So with above said internal storgae of the DATETIME, we can first convert the DATETIME to DECIMAL, then from decimal part ignore the fractional position and get only the integer part. Finally convert the integer to DATETIME as shown below:

SELECT CAST( -- Convert the integer to DATE
         FLOOR(-- Get largest Integer less than or equal to the decimal value
                CAST(GETDATE() AS DECIMAL(12, 5)) -- Convert DATETIME to DECIMAL
              ) 
         AS DATETIME) 'Date Part Only'

RESULT:
Date Part Only
-----------------------
2013-07-14 00:00:00.000

iv) Get Date Part only from DateTime using DATEPART and CONVERT functions

DECLARE @GETDATE AS DATETIME = GETDATE()
SELECT CONVERT(VARCHAR(4),DATEPART(YEAR, @GETDATE)) 
       + '/'+ CONVERT(VARCHAR(2),DATEPART(MONTH, @GETDATE)) 
       + '/' + CONVERT(VARCHAR(2),DATEPART(DAY, @GETDATE)) 
         'Date Part Only'

RESULT:
Date Part Only
--------------
2013/7/14

You may like to read the other popular articles on Date and Time:

20 thoughts on “How to get Date Part only from DateTime in Sql Server

  1. How do you insert date into table in sql server 2012 like this:
    02-01-2014 15:39
    I cant find out how to get it to work?

    I need it to be into on field element in the table.
    when I try CURRENT_TIMESTAMP or GETDATE() I get this:
    Jan 2 2014 3:40PM

    I need it to be in DAY-Month-Year-Time

  2. What do I need to change to this to get it work:
    ————————————————————–
    strSQL = “SELECT * FROM tablename1 order by id” + convert(varchar(10), getdate(), 105) + ” ” + convert(varchar(5), getdate(), 108),”)

    And what do I need to change when I create the table from the start:
    I use this:
    ————————————–
    CREATE TABLE tablename1
    (
    id int NOT NULL IDENTITY(1,1),
    dato varchar(255), /* Dato = the Date Coll I use*/
    fornavn varchar(255),
    efternavn varchar(255),
    overskrift varchar(255),
    indhold varchar(255),
    linkoverskrift varchar(255),
    link varchar(255),
    PRIMARY KEY (id),
    );

      1. I have found the problem after some hard work Basavaraj Biradar.

        It works now like this:

        Select *, CONVERT(VARCHAR(10),Dato,105) + ‘ ‘ + CONVERT(VARCHAR(8),Dato,108) as timestamp from tabel

        Thanks for your help.

  3. Hi,
    Do u know to change 2014-01-13 05:59:00.000 to 2014-13-01 05:59:00.000?

    I tried this
    left(convert(varchar(26),[CloseTime],112),4)+’-’+RIGHT(convert(varchar(8),[CloseTime],112),2)+
    ‘-’+RIGHT(convert(varchar(8),[CloseTime],112),4)+’-’+SUBSTRING(convert(varchar(26),[CloseTime],120),10,10)

    and the result is 2014-13-0113-3 05:59:00, from this how do i remove only 13-3 the bold numbers

    1. TRY This, works in Sql 2008 and above: SELECT CAST(YEAR(GETDATE()) AS VARCHAR(4)) + ‘-’+ CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘-’ + CAST(MONTH(GETDATE()) AS VARCHAR(2)) +’ ‘ +CAST(CAST(GETDATE() AS TIME(3)) AS VARCHAR(12))

  4. Thank’s , could learn a lot. I loved the solution DATEADD(dd, 0,
    DATEDIFF(dd, 0, GETDATE())) , convertions and string concatenations are boring, this is cool.

  5. How can i get only year from datetime column in a table? and

    is it possible to get only year from nvarchar column in a table?

  6. How can i get only year from date1 datetime column in a table? and

    is it possible to get only year from date1 nvarchar column in a table?

  7. i have a problem with adding date for 10 days…
    this is the case .
    date format like this
    2014-06-30 07:23:56.680

    i want to add only day for 10 days, but the time is getdate() when i execute this query at 23:00
    and result like this..
    2014-07-7 23:00:00.000

    can you make this one for me ?

    realy confused..

    thx before

    1. Hope this will help you ……

      SELECT
      (CONVERT(VARCHAR(10),getdate(), 105)+’ ‘+Convert(VARCHAR(4),DATEPART(hh,GETDATE()))+”+’:00:00:00′) AS ModifiedDate

  8. MY SQL 2005 r2
    app_no app_creatdate
    020900000001 2014-09-04 13:55:01.170

    020900000002 2014-09-08 11:44:31.307

    This is the my applications table first two columns like have so many are there in table.I want to retrive the applicantions by baSED on app_createdate
    I tried with the querys like

    select * from applications where app_createdate=getdate()

    select * from applications WHERE date(app_createdate)=$date where $date is in 2014-09-13
    and one ar more it is giving no result.

    select * from applications where app_createdate>=’2014-09-13′ its working fine but giving wrong results give me excat format query.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>