Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Datepart Function

Answered Datepart Function

  • Monday, January 21, 2013 6:34 PM
     
     
    Sign In to Vote
    0

    Datepart is dropping the leading zero for the month of Jan 01. 

    Sample: select convert(varchar,datepart(mm,getdate()-14))

    Returns value of 1 not 01.

    What is the proper edit to return the full 01?

     

     

All Replies

  • Monday, January 21, 2013 6:38 PM
     
     Answered Has Code
    Sign In to Vote
    0

    Here, this works:

    select replace(str(datepart(mm, getdate() - 14), 2), ' ', '0')


    Edit:

    Stolen from:  http://dbaspot.com/sqlserver-server/400332-how-get-two-digit-month-value.html

    Also, you know, just google it sometimes: https://www.google.com/search?q=datepart+two+digit+month&oq=datepart+two+digit+month&aqs=chrome.0.57j0j60j0j62l2.6533&sourceid=chrome&ie=UTF-8



    • Edited by dgjohnson Monday, January 21, 2013 6:42 PM
    • Marked As Answer by CIWorker Monday, January 21, 2013 6:49 PM
    •  
     
  • Monday, January 21, 2013 6:40 PM
     
      Has Code
    Sign In to Vote
    0
     
    SELECT LEFT(CONVERT(VARCHAR(11),GETDATE()-14,106),2)

    Narsimha

     
  • Monday, January 21, 2013 6:46 PM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    The return from datepart with month parameter is integer. You can try this:

    select 
--convert(varchar,datepart(mm,getdate()-14)), 
RIGHT('0'+CAST(datepart(mm,getdate()-14) AS varchar(2)),2)

     
  • Monday, January 21, 2013 7:15 PM
     
     Proposed
    Sign In to Vote
    1

    What is the first rule of any tiered architecture? We do not do display formatting in the database! We also use CAST() and not the old 1970's Sybase CONVERT() string function today. Since temporal data types are a real, complete abstract data type; it does not have "leading zeros" any more than a INTEGER does.  You are writing SQL as if it were 1960's COBOL.

    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]-[0-3][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

    • Proposed As Answer by Ahsan Kabir Monday, January 21, 2013 7:56 PM
    •