Monday, January 21, 2013 6:34 PM
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?
Monday, January 21, 2013 6:38 PM
Here, this works:
select replace(str(datepart(mm, getdate() - 14), 2), ' ', '0')
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
Monday, January 21, 2013 6:40 PM
Monday, January 21, 2013 6:46 PMModerator
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
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,
CHECK (something_report_start_date <= something_report_end_date),
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 '[0-9][0-9][0-9]-00-00' and '[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