Add a zero to any single digit month? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Add a zero to any single digit month?

Author

Topic

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-02-29 : 13:37:03

How?

I have a query that extracts the month number and year and then uses a CAST statement to join the two (concatenate them if you're familiar with Excel).

How do I add a leading zero to months January (1) thru September (9) to make them two digits (01 and 09 in this example).

Thanks in advance.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 13:43:37

care to post what you've written so far?

However

DECLARE @Month varchar(2)
SET @Month = '1'
SELECT @Month, RIGHT('0'+@Month,2)
SET @Month = '12'
SELECT @Month, RIGHT('0'+@Month,2)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-03-01 : 06:07:32

The relevant bit is


SELECT MONTH (ORDER_DATE) AS MONTH_NO
	  ,YEAR (ORDER_DATE) AS YEAR_ID 
	  ,CAST (YEAR(ORDER_DATE) AS VARCHAR(4)) + CAST(MONTH(ORDER_DATE) AS VARCHAR(2)) AS ID

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-01 : 07:49:06

You can use this:

	  CAST (YEAR(ORDER_DATE) AS VARCHAR(4)) + RIGHT('0'+CAST(MONTH(ORDER_DATE) AS VARCHAR(2)),2) AS ID,

But, I prefer to use one of the following - more compact and fewer conversions


	  LEFT(CONVERT(VARCHAR(8),ORDER_DATE,112),6)
or
	  CONVERT(VARCHAR(6),ORDER_DATE,112)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-03-01 : 07:50:12

SELECT CONVERT(char(2), ORDER_DATE, 101) -- month with 2 digits
SELECT CONVERT(char(6), ORDER_DATE, 112) -- year and month

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-01 : 10:16:01

I can ONLY work with what you give me....

Good Luck

Is this for a feed or a display on a front end application?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-03-03 : 01:19:04

Thanks for the replies, much appreciated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources