SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice

Just a week ago, my Database Team member asked me what is the best way to only select date part from datetime. When ran following command it also provide the time along with the date.

SQL SERVER - Retrieve - Select Only Date Part From DateTime - Best Practice datepart-800x258

SELECT GETDATE()
ResultSet: 2007-06-10 7:00:56.107

The required outcome was only 2007/06/10.

I asked him to come up with solution by using date functions. The method he suggested was to use

SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))

I approved his method though, I finally suggested my method using function CONVERT.

SELECT CONVERT(VARCHAR(10),GETDATE(),111)

The reason I use this because it is very convenient as well as provides quick support to convert the date in any format. The table which suggests many formats are displayed on MSDN.

Some claims that using CONVERT is slower than using DATE functions, but it is extremely negligible. I prefer to use CONVERT.

Here is the video discussing the same concepts.

Let me know what is your favorite method to select only date part from datetime. I would like to know what are the different methods and if any other method is interesting I will be happy to blog about that in the future with due credit.

Here is Part 2 of the blog post: SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice – Part 2.

In SQL Server 2008 we also have a new datatype of Date and Time, which can be very handy if you are using SQL Server 2008 or later versions. You can read about that in this blog post SQL SERVER – DATE and TIME in SQL Server 2008.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQL SERVER – Fix : Error : An error has occurred while establishing a connect to the server. Solution with Images.
Next Post
SQL SERVER – Cannot Resolve Collation Conflict For Equal to Operation

Related Posts

405 Comments. Leave new

  • Jawalikar, as has been said, you should only use these functions for query related issues. Your application is more likely to be portable and break less if you use the end-user application for date formatting. I know this doesn’t answer your question, but as a developer I’ve had to clean up messes by past devs who used SQL for date formatting, it will work, but there are much better ways.

    Reply
  • Hi How are you? I need a help in converting BIGINT to DATETIME. Sample BIGINT Value 201411132245 i into 2014-11-13 22:45 (Date Hour Minute). Could you please help me . Thanks in advance…

    Reply
    • Here is the way

      declare @i bigint= 201411132245 
      select CAST(stuff(stuff(@i,9,0,' '),12,0,':') as DATETIME)
      
      Reply
  • Want only the date part in the format (dd-mm-yyyy) stored in table as datetime value which contains date and time.

    Reply
    • DECLARE @datetime DATETIME
      SET @datetime = GETDATE()

      SELECT @datetime AS [original], convert(VARCHAR(11), @datetime, 105) AS [short]
      GO
      –OUTPUT
      original short
      ———————– ———–
      2015-03-26 06:43:56.290 26-03-2015

      Reply
  • I have DateTime column in my Trans table such as this one ‘2015-01-16 12:12:01.000’
    I want to pass in only datetime = ‘2015-01-16’
    when I use this sql it pulls 0 rows
    select top 10 * from Trans with (nolock)
    where datetime = ‘2015-05-14’
    any help on this please.

    Reply
  • I have DateTime column in my Trans table such as this one ‘2015-01-16 12:12:01.000′
    I want to pass in only datetime = ‘2015-01-16′
    when I use this sql it pulls 0 rows
    select top 10 * from Trans with (nolock)
    where datetime = ‘2015-05-16′
    any help on this please.

    Reply
  • sorry about two wrong information above:
    I have DateTime column in my Trans table such as this one ‘2015-01-16 12:12:01.000′
    I want to pass in only datetime = ‘2015-01-16′
    when I use this sql it pulls 0 rows
    select top 10 * from Trans with (nolock)
    where datetime = ‘2015-01-16′
    any help on this please.

    Reply
    • Syed – ‘2015-01-16 12:12:01.000′ and ‘2015-01-16′ is not same that’s why zero rows. Please use convert.

      Reply
  • select VALUE FROM TABLE WHERE DATE=DTPICKER1.VALUE DOESNT RETURN A VALUE

    Reply
  • The left,11 changes the format of the date to conform to LocalLongDate

    Reply
  • selection criteria in both date format yyyy-MM-dd or yyyy-dd-MM in sql server

    SELECT CONVERT(VARCHAR(10),GETDATE(),120)
    SELECT CONVERT(VARCHAR(10),GETDATE(),110)

    Reply
  • — =============================================
    — Author: Frans Habekotté
    — Create date: 16-12-2008
    — Description: FRH_DateSetTimeZero
    –select getdate(),dbo.FRH_DateSetTimeZero(getdate())
    — =============================================
    CREATE FUNCTION FRH_DateSetTimeZero
    (
    — Add the parameters for the function here
    @MyDate datetime
    )
    RETURNS datetime
    AS
    BEGIN
    — Declare the return variable here
    DECLARE @DateWithoutTime datetime

    — Add the T-SQL statements to compute the return value here
    SELECT @DateWithoutTime = cast(cast(cast(@MyDate as float) as int) as datetime )
    — Return the result of the function
    RETURN @DateWithoutTime

    END
    — GO
    GO

    Reply
  • for the SQL Server 2014 or later, it is simplest to use:

    select cast(GETDATE() as date)

    Reply
  • 111- Means yyyy-mmm-dd

    We are Using Left function in that statement i.e select left(getdate(),11)
    So the result is mmm-dd-yyyy

    Reply
  • HARSHIT RATHORE
    March 3, 2017 12:41 pm

    You can use Format in this case it also gives the same result :

    select format(getdate(),’yyyy-MM-dd’)

    Reply

Leave a Reply

Menu