How to return only the Date from a SQL Server DateTime datatype

How to return only the Date from a SQL Server DateTime datatype

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


  • 5 Answer(s)

    In order to get the date in SQL Server 2008 and higher, we should convert into date,

    SELECT CONVERT(date, getdate())
    

    But in the earlier version we can do these following steps,

    SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))
    

    for eg,

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

    it will gives us,

    2008-09-22 00:00:00.000
    

    Advantages are,

         1. No varchar<->datetime conversions required
          2.No need to think about locale


    Answered on November 27, 2018.
    Add Comment
    SELECT CONVERT(date, GETDATE())
    
    

    Actually, we have to get only date by using date data type which belongs to sql 2008 server. it contains date without time component.

    Answered on November 27, 2018.
    Add Comment

    If you want to date without showing time, you should used SQL Server 2008 and above,

    select cast(getdate() as date)
    
    
    Answered on November 27, 2018.
    Add Comment
    -- Query to Return Date Part from a Datetime datatype in SQL Server
    DECLARE @DateAdd datetime2 = '2017-05-12 14:24:04.1234567'
    SELECT GETDATE() AS ToDay;
    
    -- Using Convert without Format on Variable, and GETDATE()
    SELECT CONVERT(date, GETDATE()) AS [ToDays Date 1];
    SELECT CONVERT(date, @DateAdd) AS [ToDays Date 2];
    
    -- Using Convert with Format on Variable, and GETDATE()
    SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [ToDays Date 3];
    SELECT CONVERT(VARCHAR(10), @DateAdd, 111) AS [ToDays Date 4];
    
    -- Using Cast Function on Variable, and GETDATE()
    SELECT CAST(GETDATE() as date) AS [ToDays Date 5];
    SELECT CAST(@DateAdd as date) AS [ToDays Date 6];
    
    -- Worst case Scenario  
    SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) AS [ToDays Date 7];
    SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @DateAdd)) AS [ToDays Date 8];
    Answered on January 14, 2019.
    Add Comment

    In this example we will extract the data part only from the Hire date column.

    Answered on January 14, 2019.
    Add Comment


  • Your Answer

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