Best approach to remove time part of datetime in SQL Server

Best approach to remove time part of datetime in SQL Server

Asked on November 30, 2018 in SQL Server.
Add Comment


  • 5 Answer(s)

    Always method a is the least resource intensive.

    a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
    

    remember one thing , the proof are less that CPU intensive for same total duration a million rows by some one with way too much time on their hands:

    The similar test elsewhere with similar results too.There are many benefits when using DATEADD/DATEDIFF because:

    • For dateformat issues, use varchar is subject to language.
    • Always, float relies on internal storage.
    • By changing “0” base DATEADD/DATEDIFF extends to work out first day of month, tomorrow etc.
    •  

    Edit, Oct 2011:

    In SQL Server 2008 and above , you can CAST to date. Or just use date so no time to remove.

    Edit, May 2012:

    Adding a function or CAST to a column invalidates index usage,click for number 2:

    http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/

    Edit, Sep 2018, for datetime2

    DECLARE @datetime2value datetime2 = '02180912 11:45' --this is deliberately within datetime2, year 0218
     
    DECLARE @datetime2epoch datetime2 = '19000101'
    select DATEADD(dd, DATEDIFF(dd, @datetime2epoch, @datetime2value), @datetime2epoch)
    

     

    Answered on November 30, 2018.
    Add Comment

    The bulletproof solution is the following:

    CONVERT(DATE, getdate(), 101);
    
    Answered on November 30, 2018.
    Add Comment

    This should works fine,

    For SQL 2008 and later versions, you can use DATE datatype.

    SELECT CONVERT(DATE,GETDATE());
    
    
    Answered on November 30, 2018.
    Add Comment
    SELECT CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)
    Answered on January 14, 2019.
    Add Comment

    First thing’s first, if your dates are in varchar format change that, store dates as dates it will save you a lot of headaches and it is something that is best done sooner rather than later. The problem will only get worse.

    Secondly, once you have a date DO NOT convert the date to a varchar! Keep it in date format and use formatting on the application side to get the required date format.

    There are various methods to do this depending on your DBMS:


    SQL-Server 2008 and later:

    SELECT  CAST(CURRENT_TIMESTAMP AS DATE)

    SQL-Server 2005 and Earlier

    SELECT  DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0)

    SQLite

    SELECT  DATE(NOW())

    Oracle

    SELECT  TRUNC(CURRENT_TIMESTAMP)

    Postgresql

    SELECT  CURRENT_TIMESTAMP::DATE

    If you need to use culture specific formatting in your report you can either explicitly state the format of the receiving text box (e.g. dd/MM/yyyy), or you can set the language so that it shows the relevant date format for that language.

    Either way this is much better handled outside of SQL as converting to varchar within SQL will impact any sorting you may do in your report.

    If you cannot/will not change the datatype to DATETIME, then still convert it to a date within SQL (e.g. CONVERT(DATETIME, yourField))

     

    Answered on January 14, 2019.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.