asked on

sql server 2008 cast

How can I use the CAST function to put 1 digit months as a 1 digit with leading zero's

such as January should be 01
Feburary should be 02
March should be 03 etc.

here is the code I am using:
cast( datepart (MM,XHR_Applicant.ApplicationDate) as varchar (2)) castMM,

Thanks for your help

Jerry

Aneesh

RIGHT( '00'+cast( datepart (MM,XHR_Applicant.ApplicationDate) as varchar (2)) ,2) as castMM

lcohan

You mean something like this?

declare @datein sysname
declare @date datetime
set @datein = '03252013'
set @date = substring(@datein,1,2)+'/'+substring(@datein,3,2)+'/'+substring(@datein,5,4)

SELECT Convert(VarChar(8), Cast(@date As DateTime), 112)

COANetwork

SELECT RIGHT('0'+ CONVERT(VARCHAR,datepart (MM,XHR_Applicant.ApplicationDate)),2) AS castMM

Open in new window

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MohitPandit

Hello,

Please check below code with CAST with sample data

SELECT
	castMM =
		CASE
			WHEN LEN([Month]) = 1 THEN '0'+ CAST([Month] AS VarChar(2))
		ELSE
			CAST([Month] AS VarChar(2))
		END
FROM
(
	SELECT [Month] = DATEPART(MM,'2013-1-25 14:45:14.293')
	UNION ALL
	SELECT [Month] = DATEPART(MM,'2013-2-25 14:45:14.293')
	UNION ALL
	SELECT [Month] = DATEPART(MM,'2013-3-25 14:45:14.293')
	UNION ALL
	SELECT [Month] = DATEPART(MM,'2013-4-25 14:45:14.293')
	UNION ALL
	SELECT [Month] = DATEPART(MM,'2013-5-25 14:45:14.293')
	UNION ALL
	SELECT [Month] = DATEPART(MM,'2013-6-25 14:45:14.293')
	UNION ALL
	SELECT [Month] = DATEPART(MM,'2013-7-25 14:45:14.293')
	UNION ALL
	SELECT [Month] = DATEPART(MM,'2013-8-25 14:45:14.293')
	UNION ALL
	SELECT [Month] = DATEPART(MM,'2013-9-25 14:45:14.293')
	UNION ALL
	SELECT [Month] = DATEPART(MM,'2013-10-25 14:45:14.293')
	UNION ALL
	SELECT [Month] = DATEPART(MM,'2013-11-25 14:45:14.293')
	UNION ALL
	SELECT [Month] = DATEPART(MM,'2013-12-25 14:45:14.293')
) T1 -- Your input table

Open in new window

Please note, you may use CONVERT function as well with below code:

SELECT
	castMM =
		CASE
			WHEN LEN([Month]) = 1 THEN '0'+ CONVERT(VarChar(2),[Month])
		ELSE
			CONVERT(VarChar(2), [Month])
		END
FROM
(
	SELECT [Month] = DATEPART(MM,'2013-1-25 14:45:14.293')
	UNION ALL
	SELECT [Month] = DATEPART(MM,'2013-2-25 14:45:14.293')
	UNION ALL
	SELECT [Month] = DATEPART(MM,'2013-3-25 14:45:14.293')
	UNION ALL
	SELECT [Month] = DATEPART(MM,'2013-4-25 14:45:14.293')
	UNION ALL
	SELECT [Month] = DATEPART(MM,'2013-5-25 14:45:14.293')
	UNION ALL
	SELECT [Month] = DATEPART(MM,'2013-6-25 14:45:14.293')
	UNION ALL
	SELECT [Month] = DATEPART(MM,'2013-7-25 14:45:14.293')
	UNION ALL
	SELECT [Month] = DATEPART(MM,'2013-8-25 14:45:14.293')
	UNION ALL
	SELECT [Month] = DATEPART(MM,'2013-9-25 14:45:14.293')
	UNION ALL
	SELECT [Month] = DATEPART(MM,'2013-10-25 14:45:14.293')
	UNION ALL
	SELECT [Month] = DATEPART(MM,'2013-11-25 14:45:14.293')
	UNION ALL
	SELECT [Month] = DATEPART(MM,'2013-12-25 14:45:14.293')
) T1 -- Your input table

Open in new window

Kindly take a look over it and let me know in case you have any concern.

Best Regards,
Mohit Pandit

jvonhendy

ASKER

It worked PERFECTLY!! And excellent explanation of why 1 is used.