simple question - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

simple question

Author

Topic

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2003-05-21 : 04:05:57

hi,
there is a small query:
select datepart(mm,getdate())
which gives me the result as 5
is there any way that i can get the result as 05?
i.e for single digit result it should add 0 at the begining.
Regards,
Harshal.

Expect the UnExpected

mr_mist
Grunnio

1870 Posts

Posted - 2003-05-21 : 04:09:38

Datepart returns an integer, so I think probably your best bet here is to write your own UDF that pads out the result if that integer is < 10.

Or sort it in your presentation layer.

-------
Moo.

Edited by - mr_mist on 05/21/2003 04:10:16

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2003-05-21 : 05:03:35

quote:

Datepart returns an integer, so I think probably your best bet here is to write your own UDF that pads out the result if that integer is < 10.

Or sort it in your presentation layer.

-------
Moo.

Edited by - mr_mist on 05/21/2003 04:10:16

Expect the UnExpected

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-21 : 08:34:27

Leading zeros? You want leading zero's? I'll give you leading zeros!

You gotta convert to varchar

select cast(datepart(mm,getdate()) AS VARCHAR)

Still no leading zeros..

select '0' + cast(datepart(mm,getdate()) AS VARCHAR)

Leading zero, but what about '010', '011', '012'

select RIGHT(cast(datepart(mm,getdate()) AS VARCHAR), 2)

Now you're going to tell us you don't want a character type right?

Sam

dsdeming

479 Posts

Posted - 2003-05-21 : 08:39:52

If you want to do it inline without having to build a UDF, try:

select RIGHT( '00' + CAST( datepart(mm,getdate()) AS varchar( 2 )), 2 )

However, a padding UDF is a good idea if this is something you have to do often.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-21 : 08:52:39

What's wrong with
LEFT(CONVERT(datetime, GETDATE(), 101), 2)
?

mr_mist
Grunnio

1870 Posts

Posted - 2003-05-21 : 09:07:24

quote:

What's wrong with
LEFT(CONVERT(datetime, GETDATE(), 101), 2)

Well, on my system, it returns..

Ma

Though in principle I see what you mean.

-------
Moo.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-21 : 09:27:23

I can't believe I did that!
CONVERT(varchar(2), GETDATE(), 101)

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-05-21 : 09:42:27

How about this?

SELECT REPLACE(STR(datepart(mm,getdate()),2,0),' ','0')

Just change the 2 to the maximum length desired.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-21 : 09:54:35

RIGHT(DATEPART(mm,GETDATE())+100,2)

[Ed: Thank you Arnold, that's enough]

mtomeo
Starting Member

30 Posts

Posted - 2003-05-21 : 10:02:34

If you want, we wrote these UDF's (lpad and rpad) to use in most of our DB's. It's not much different from what has been previously posted, just in UDF form. They work pretty well, just pass is the string, new length, and fill character (defaults to spaces if you don't pass a fill char).


CREATE FUNCTION dbo.lpad
 (
  @mstr AS varchar(8000),
  @nofchars AS int,
  @fillchar AS varchar(8000)=' '
 )
RETURNS varchar(200)
AS
BEGIN
 RETURN
  CASE
   WHEN LEN(@mstr) >= @nofchars THEN SUBSTRING(@mstr,1,@nofchars)
   ELSE
    SUBSTRING(REPLICATE(@fillchar,@nofchars),1,@nofchars-LEN(@mstr))+@mstr
  END
END


CREATE FUNCTION dbo.rpad
 (
  @mstr AS varchar(8000),
  @nofchars AS int,
  @fillchar AS varchar(8000)=' '
 )
RETURNS varchar(200)
AS
BEGIN
 RETURN
  CASE
   WHEN LEN(@mstr) >= @nofchars THEN SUBSTRING(@mstr,1,@nofchars)
   ELSE
    @mstr+SUBSTRING(REPLICATE(@fillchar,@nofchars),1,@nofchars-LEN(@mstr))
  END
END

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2003-05-22 : 09:38:30

a few days back i remember some one asking about other sql forums ,
I had said WHY DO U REQUIRE OTHER FORUMS WHEN U HAVE SQL TEAM ??
I think this thread justifies every thing!!!!

THanks everyone.

Expect the UnExpected

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources