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

  • Hi sir,
    I have used this query ORDER BY cast([Regdate] as datetime) DESC but error occur was The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range
    please help me.

    Reply
  • Hi sir,
    when i use this query for sorting date wise order by CONVERT(datetime,[date],103) DESC
    Error occur was:
    The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

    Reply
  • Hi sir,
    when i use this query for sorting date wise order by CONVERT(datetime,[date],103) DESC
    Error occur was:
    The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
    Please help me urgently
    Thanks in adv….

    Reply
  • Thank you, for a quick tip.

    Reply
  • Reply
  • if the date is stored as varchar datatype and i need to extract only “year” from that date,could anyone help me out in this issue?

    Reply
    • It depends on the format. If it is YYYYMMDD you can use extract first four digits, if it is dd/mm/yyyy , extract the last four digits etc

      Reply
  • very helpful Thank You……….

    Reply
  • thanks :)

    Reply
  • Thank you so much Sir, it help me lots

    Reply
  • Sir, I am raghu i am having problem with this query

    Q. Display the orderID of the top five orders based on the Total amount due in the year 2001.

    hint:You can extract the Year part from a date using the Datepart Function

    i am not getting the proper output

    i am using SSMS 2005, Database Adworks please reply ASAP.

    Reply
  • Say big thank you for your help!!

    Reply
  • Sujendran .A
    March 8, 2013 4:27 pm

    Guys check this in SQL
    Question:”print (getdate ())”
    Solution:Mar 9 2013 5:56AM

    Reply
  • this is helpful however, I bumped into problems when I used convert instead of dateadd function to extract the datepart. when you use this column to search for a specific date, the converted datetime column will yield incorrect results since it still has the timepart in it only not visible. in cases like this, it is advisable to use the dateadd function instead as you will be sure that it will set the time part to 00:00:00.000 hence you will be able to use this to look for a specific date only.

    Reply
  • Majed Al-Jemmieh
    July 22, 2013 8:21 pm

    thank you
    I’m very joy in this site.

    Reply
  • Majed Al-Jemmieh
    July 22, 2013 8:23 pm

    thank you
    I’m very joy in this site.

    Reply
  • sachin chauhan
    August 8, 2013 2:11 pm

    what is 111,104 etc at the end.

    Reply
  • I still don’t understand.. I have 3 dates in my table configured as
    10/10/2013 1:11:00 PM

    I need to extract each piece separately but this doesn’t work

    select datepart (mm, session_start) startMM, datepart(dd, session_start) startDD, datepart(yy,session_start) startYY,
    datepart (HH, session_start) startTimeHour, datepart(MI, session_start) startTimeMin,
    datepart(SS, session_start) startTimeSec, datepart (AM, session_start)

    What am I doing wrong? Please help.

    Reply
  • sorry if its not relevant
    I want to generate date from 2010-01-01 to 2013-12-31 then find GenerateDate(Which will be 2010-01-01 for first data), Year (Which will be 2010 for 2010-01-01). Month(Which will be 01 for 2010-01-01), Day(Which will be 01 for 2010-01-01), Week(Which will be 1 for 2010-01-01), Quarter (Which will be 01 for 2010-01-01) then I want to create a Date_Table and insert all data in the SQL table. There will be a auto increment filed (ID) also. Here GenerateDate will be Primary Key Please help me.

    Reply
  • Hi All,

    I have created a stored procedure like below.

    CREATE PROCEDURE [dbo].[SP_CONSULTATION]
    @time time,
    @time1 time,
    @AGE numeric(3, 0)=5,
    @AMOUNT numeric(6, 2) OUTPUT
    AS
    BEGIN
    SET @time= LEFT(DATEADD(mi, 10, (CONVERT(TIME(0),GETDATE()))),5)
    –SET @time= CONVERT (time, SYSDATETIME())
    SET @time1 = (select @time)
    select @time1
    SET @AMOUNT= CASE
    WHEN @time1 ’18:00′ THEN
    CASE
    WHEN @AGE <= 10 THEN 200
    ELSE 500
    END
    ELSE
    CASE
    WHEN @AGE = 5 AND @AGE <= 10 THEN 100
    ELSE 200
    END
    END
    END

    when i execute this stored procedure i got error like this

    Msg 201, Level 16, State 4, Procedure SP_CONSULTATION, Line 0
    Procedure or function 'SP_CONSULTATION' expects parameter '@time', which was not supplied.

    (1 row(s) affected)

    (1 row(s) affected)

    i want the output as to meet the conditions in stored procedure and want to display the amount value. please help me out from this.

    Thanks,
    Avinash P

    Reply
  • shreyas Jawalikar
    June 5, 2014 1:19 pm

    if date is inserted in table like ‘Jan 5 1980 12:00AM’
    then how can i change the format like this 05/01/1980

    Reply

Leave a Reply