Datepart Function ate egg

Question

Post reply

Datepart Function ate egg

karthik M

One Orange Chip

Points: 29139
More actions
May 15, 2009 at 6:52 am

#221371

Hi All,
I have one scenario.
declare @mm char(2)
declare @gdate datetime
select @gdate = '03/31/2009'
select @mm = convert(char(2),datepart(month,@gdate))
select @mm
It is showing 3. But i need the output as 03.
I modified the above code as below.
declare @mm char(2)
declare @gdate datetime
select @gdate = '03/31/2009'
select @mm = case when datepart(month,@gdate) < 10 then '0' + convert(char(1),datepart(month,@gdate))
else convert(char(2),datepart(month,@gdate))
end
select @mm
Now i got the desired output.
Is there any default method or function available to display '0' in front of '3' ?
Alternate approach also welcome!
karthik

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 1

I use the following:

right('00'+cast(datepart(month, getdate()) as varchar), 2)

means only 1 reference to the date column

george sibbald SSC Guru Points: 104210 More actions · Answer 2

simialar thing:

right('0' + CONVERT(varchar(2), datepart(mm, getdate())),2)

---------------------------------------------------------------------

Sergiy SSC Guru Points: 110208 More actions · Answer 3

karthikeyan (5/15/2009)
Hi All,
I have one scenario.
declare @mm char(2)
declare @gdate datetime
select @gdate = '03/31/2009'
select @mm = convert(char(2),datepart(month,@gdate))
select @mm
It is showing 3. But i need the output as 03.
I modified the above code as below.
declare @mm char(2)
declare @gdate datetime
select @gdate = '03/31/2009'
select @mm = case when datepart(month,@gdate) < 10 then '0' + convert(char(1),datepart(month,@gdate))
else convert(char(2),datepart(month,@gdate))
end
select @mm
Now i got the desired output.

Why you convert to integer when what you need is a specifically formatted car value?

If you need char keep it char.

declare @mm char(2)

declare @gdate datetime

select @gdate = '03/31/2009'

select @mm = convert(char(2), @gdate, 101)

select @mm

_____________
Code for TallyGenerator

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 4

One more for fun:SELECT RIGHT('0' + CAST(MONTH(CURRENT_TIMESTAMP) AS VARCHAR(2)), 2)

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

Florian Reischl

SSC-Dedicated

Points: 37301

May 15, 2009 at 5:13 pm

#995764

SELECT CONVERT(CHAR(2), GETDATE(), 1)

george sibbald SSC Guru Points: 104210 More actions · Answer 6

and SSC pays off again. I've got a couple of scripts that suffix dates to filenames I'm going to alter (anything to save typing).

SELECT CONVERT(CHAR(2), GETDATE(), 3) for day of the month

SELECT CONVERT(CHAR(2), GETDATE(), 2) for year (no century)

SELECT CONVERT(CHAR(4), GETDATE(), 102) for year with century

cheers!

(why does the states put month before day in dates?)

---------------------------------------------------------------------

karthik M One Orange Chip Points: 29139 More actions · Answer 7

Sergiy (5/15/2009)
karthikeyan (5/15/2009)
Hi All,
I have one scenario.
declare @mm char(2)
declare @gdate datetime
select @gdate = '03/31/2009'
select @mm = convert(char(2),datepart(month,@gdate))
select @mm
It is showing 3. But i need the output as 03.
I modified the above code as below.
declare @mm char(2)
declare @gdate datetime
select @gdate = '03/31/2009'
select @mm = case when datepart(month,@gdate) < 10 then '0' + convert(char(1),datepart(month,@gdate))
else convert(char(2),datepart(month,@gdate))
end
select @mm
Now i got the desired output.
Why you convert to integer when what you need is a specifically formatted car value?
If you need char keep it char.
declare @mm char(2)
declare @gdate datetime
select @gdate = '03/31/2009'
select @mm = convert(char(2), @gdate, 101)
select @mm

Yep!

karthik

Mike Mullen Hall of Fame Points: 3254 More actions · Answer 8

Here is one more solution variation.

declare @mm char(2)

declare @gdate datetime

select @gdate = '03/31/2009'

select @mm = right(100 + month(@gdate),2)

select @mm