locked
Date Format RRS feed

  • Question

  • Hi,

    I have a Date variable called 'Date' but the output is displayed dd/mm/yyyy 

    How do I change it to show just DD/YYYY please?

    Monday, March 12, 2018 1:11 PM

Answers

  • Hi, you can use the FORMAT function in TSQL to return the date in various formats.

    DECLARE @myDate DATE 
    SELECT @myDate = GETDATE()
    SELECT FORMAT(@myDate, 'dd/yyyy')  

    Thanks,
    Sam Lester (MSFT)


    https://blogs.msdn.microsoft.com/samlester/

    Twitter - @SQLSamLester

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Dean Savović Monday, March 12, 2018 6:29 PM
    Monday, March 12, 2018 1:38 PM

All replies

  • Hi, you can use the FORMAT function in TSQL to return the date in various formats.

    DECLARE @myDate DATE 
    SELECT @myDate = GETDATE()
    SELECT FORMAT(@myDate, 'dd/yyyy')  

    Thanks,
    Sam Lester (MSFT)


    https://blogs.msdn.microsoft.com/samlester/

    Twitter - @SQLSamLester

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Dean Savović Monday, March 12, 2018 6:29 PM
    Monday, March 12, 2018 1:38 PM
  • Ideally you should be doing this in front end as formating causes column to lose its native datatype and make it character based. This will make it difficult if you want to do further manipulations with them.

    However if you want to do it in sql you need to use FORMAT or CONVERT

    DECLARE @dt datetime
    SET @dt = GETDATE()
    
    SELECT FORMAT(@dt,'dd/yyyy'),STUFF(CONVERT(varchar(18)@dt,101),1,3,'')


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, March 12, 2018 1:50 PM
    Answerer