Easy way to force DATEPART to output as fixed-length?

Question

When I do for example DATEPART(mm, GETDATE()) I get the result of the month being "8" for August. If i did the same thing in December I would get "12". Those are two different length results.

Is there a way to get the DATEPART results to always be a fixed length? So that for example the months would show up as 08 or 12. And days would be 05 and 30.

More Details:

I'm using a derived column transformation in SSIS to take a server datestamp, and remove all the formatting (spaces, colons, dashes, etc) in order to use it as part of a Primary Key.

My forumula that currently works is below, however it results in variable lenght results which is not ideal. I would like to get all results to be the same lenght.

((DT_STR,4,1252)DATEPART("yyyy",createDate)) + ((DT_STR,2,1252)DATEPART("mm",createDate)) + ((DT_STR,2,1252)DATEPART("dd",createDate)) + ((DT_STR,2,1252)DATEPART("hh",createDate)) + ((DT_STR,2,1252)DATEPART("mi",createDate)) + ((DT_STR,2,1252)DATEPART("ss",createDate)) + ((DT_STR,2,1252)DATEPART("ms",createDate))

Input looks like this:

9/11/2008 8:50:47:300 PM

Results look like:

20089112050473

Results should look like:

20080911205047300

Answer 1

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

EDIT based on new info - to get your timestamp to a fixed string of numbers:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(varchar(23), GETDATE(), 121),'-',''),':',''),' ',''),'.','')

Answer 2

The return type of DATEPART is int. The moment you ask for 05, is no longer and int but a string (char, varchar, nchar, nvarchar etc). As long as you understand the difference and you are OK with it, there are all sort of manipulations you can do to format the string as you whish, a good example being the one DJ showed. In truth though the proper place for such manipulations is on the client reporting, not on the server.

Answer 3

You can use CONVERT to grab a fixed-length date, for example:

SELECT CONVERT(nvarchar(30), GETDATE(), 126)

Which will show:

 2006-04-18T09:58:04.570

which every variable in a fixed position.

Answer 4

Building on Andomar's example above:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(
   CONVERT(nvarchar(30), GETDATE(), 126),'-',''),'.',''),':',''),'T','')

Which will show:

20060418095804570

Caution: using a timestamp as a primary key WILL eventually bite you in the butt. Also, a primary key that is a number will be faster than a long string like this, so consider changing your algorithm to use a numeric conversion of the timestamp rather than a string.

Solution #2 Use a .NET user-defined function that wraps DateTime.ToString() so you can pass a specific format ("yyyymmddHHMMss" or whatever). Given the number of casts and replacements, it's possible this might perform just as well as straight T-SQL.