locked
Need help with displaying date column in different format RRS feed

  • Question

  • Date column (dtcreated) is in format dd-mon-yy and I want it to display in month dd YYYY but get confused with which function and syntax to use.
    Wednesday, September 21, 2016 2:21 PM

Answers

  • Hello,

    You can use the convert function with format parameter 107

    SELECT CONVERT(varchar(20), GETDATE(), 107)

    or with SQL Server from Version 2012 you can use the FORMAT (Transact-SQL) function


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, September 21, 2016 2:27 PM
    Answerer
  • Your confusion already starts at some basic understandings:

    A DATE is stored as a combination of numbers. There is no format involved in this. Format comes only into play, when you SELECT a DATE column and use a tool to visualize it. Like SSMS and its result pane.

    The formatting of data in general should be done in the front-end application or consumer of the data from your database. It should be avoided to do it in the database itself.

    When you think it must be done there, then you have two options, depending on your unknown SQL Server version:

    1) SQL Server 2012+: FORMAT()

    2) before: CONVERT() and DATENAME()

    E.g.

    DECLARE @Date DATE = GETDATE();
    
    SELECT  FORMAT(@Date, 'MMMM dd yyyy') ,
            CONVERT(NVARCHAR(255), @Date, 109) ,
            DATENAME(MONTH, @Date) + ' ' + RIGHT(CONVERT(NVARCHAR(255), @Date, 109), 7);


    Wednesday, September 21, 2016 2:35 PM
    Answerer

All replies

  • Hello,

    You can use the convert function with format parameter 107

    SELECT CONVERT(varchar(20), GETDATE(), 107)

    or with SQL Server from Version 2012 you can use the FORMAT (Transact-SQL) function


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, September 21, 2016 2:27 PM
    Answerer
  • Your confusion already starts at some basic understandings:

    A DATE is stored as a combination of numbers. There is no format involved in this. Format comes only into play, when you SELECT a DATE column and use a tool to visualize it. Like SSMS and its result pane.

    The formatting of data in general should be done in the front-end application or consumer of the data from your database. It should be avoided to do it in the database itself.

    When you think it must be done there, then you have two options, depending on your unknown SQL Server version:

    1) SQL Server 2012+: FORMAT()

    2) before: CONVERT() and DATENAME()

    E.g.

    DECLARE @Date DATE = GETDATE();
    
    SELECT  FORMAT(@Date, 'MMMM dd yyyy') ,
            CONVERT(NVARCHAR(255), @Date, 109) ,
            DATENAME(MONTH, @Date) + ' ' + RIGHT(CONVERT(NVARCHAR(255), @Date, 109), 7);


    Wednesday, September 21, 2016 2:35 PM
    Answerer
  • Olaf, many thanks!  This was very helpful in clearing up the confusion.
    Wednesday, September 21, 2016 3:28 PM