Format a number to '00'

Hi,
I am pulling the month from a date like this:
Cast(DatePart(m,[bhDate]) as varchar)
if it comes back 12, I get 12, if it comes back as the 7th month, I want to
get '07'.
I normally use the FORMAT function in VBA, but having trouble finding how to
do it in T-SQL
Any thoughts,
SteveTry:
select
replace (str (DatePart(m,[bhDate]), 2), ' ', '0')
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:CA25F627-E9E8-4147-8D3F-EAABF2D71441@.microsoft.com...
Hi,
I am pulling the month from a date like this:
Cast(DatePart(m,[bhDate]) as varchar)
if it comes back 12, I get 12, if it comes back as the 7th month, I want to
get '07'.
I normally use the FORMAT function in VBA, but having trouble finding how to
do it in T-SQL
Any thoughts,
Steve|||Try this REPLICATE('0',2-LEN(Cast(DatePart(m,[bhDate]) as varchar)))+
Cast(DatePart(m,[bhDate]) as varchar)
--
Thanks & Rate the Postings.
-Ravi-
"SteveInBeloit" wrote:

> Hi,
> I am pulling the month from a date like this:
> Cast(DatePart(m,[bhDate]) as varchar)
> if it comes back 12, I get 12, if it comes back as the 7th month, I want t
o
> get '07'.
> I normally use the FORMAT function in VBA, but having trouble finding how
to
> do it in T-SQL
> Any thoughts,
> Steve
>|||Here is another approch which could be shaved down to smaller set of command
s
but I thought the long version would help with concept.
DECLARE @.v_Month VARCHAR(2),
@.V_Length VARCHAR(10),
@.v_DisMonth VARCHAR(2)
-- Get Month
SELECT @.v_Month =Cast(DatePart(m,GETDATE()) as varchar)-- Feb
-- See Month
SELECT @.v_Month-- before conversion
-- Get Length of Month Return
SELECT @.v_Length = DATALENGTH(@.v_Month)
-- Make two digits if needed
SELECT @.v_DisMonth = CASE WHEN @.v_Length = 1
THEN '0'+@.v_Month
ELSE @.v_Month
END
-- See Month Correctly
SELECT @.v_DisMonth -- After Conversion
"SteveInBeloit" wrote:

> Hi,
> I am pulling the month from a date like this:
> Cast(DatePart(m,[bhDate]) as varchar)
> if it comes back 12, I get 12, if it comes back as the 7th month, I want t
o
> get '07'.
> I normally use the FORMAT function in VBA, but having trouble finding how
to
> do it in T-SQL
> Any thoughts,
> Steve
>|||For some reason, Microsoft hasn't implemented a generic string format
function in T-SQL.
To get 2-digit month, you can also use convert() function and let Microsoft
truncates for you.
convert(char(2), getdate(), 101)
"Ravi" <ravishankart@.hotmail.com> wrote in message
news:A68D4075-7FB0-49B9-B598-7CF06485B21C@.microsoft.com...
> Try this REPLICATE('0',2-LEN(Cast(DatePart(m,[bhDate]) as varchar)))+
> Cast(DatePart(m,[bhDate]) as varchar)
> --
> Thanks & Rate the Postings.
> -Ravi-
>
> "SteveInBeloit" wrote:
>