none
Get Month and Year

    Question

  • Hi all,

    How would I get the following

    June 2013 from the GETDATE()

    I need the month in name and the full year...

    Monday, June 24, 2013 11:14 PM

Answers

  • Use DATENAME function, e.g.

    select datename(month, CURRENT_TIMESTAMP) + ' ' + STR(datename(year, CURRENT_TIMESTAMP),4)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Milli_22 Monday, June 24, 2013 11:26 PM
    Monday, June 24, 2013 11:20 PM
    Moderator

All replies

  • Use DATENAME function, e.g.

    select datename(month, CURRENT_TIMESTAMP) + ' ' + STR(datename(year, CURRENT_TIMESTAMP),4)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Milli_22 Monday, June 24, 2013 11:26 PM
    Monday, June 24, 2013 11:20 PM
    Moderator
  • Report Period Table

    Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise. 

    CREATE TABLE Something_Report_Periods
    (something_report_name CHAR(10) NOT NULL PRIMARY KEY
       CHECK (something_report_name LIKE <pattern>),
     something_report_start_date DATE NOT NULL,
     something_report_end_date DATE NOT NULL,
      CONSTRAINT date_ordering
        CHECK (something_report_start_date <= something_report_end_date),
    etc);

    These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[01][0-9]-00'


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, June 25, 2013 1:48 AM
  • Try as below:

    SELECT DATENAME(MM,GETDATE()) +SPACE(1)+CAST(YEAR(GETDATE()) AS VARCHAR) AS NEW_DATE

    Thanks.


    bala krishna

    Tuesday, June 25, 2013 6:33 AM