Clever Geek Handbook

Easy way to make DATEPART output as fixed length?

When I do, for example, DATEPART (mm, GETDATE ()), I get the month result "8" for August. If I did the same in December, I would get a "12". These are two different results.

Is there a way to keep DATEPART results always of a fixed length? So, for example, the months will appear as 08 or 12. And the days will be 05 and 30.

More details:

I am using SSIS derived column transform to take a datestamp server and remove all formatting (spaces, colons, dashes, etc.) in order to use it as part of the primary key.

My forum that is currently running is below, however it produces variable results that are not ideal. I would like all the results to be the same.

((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))

The input looks like this:

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

The results look like this:

20089112050473

The results should look like this:

20080911205047300

+2


source to share


4 answers 


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

EDIT based on new information - to get the timestamp with a fixed string of numbers:



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


source


The return type is DATEPART int . The moment you ask 05

, there is no longer an int, but a string (char, varchar, nchar, nvarchar, etc.). As long as you understand the difference and you're fine with it, there are all sorts of manipulations you can do to format the string the way you do it, a good example is the one that one DJ showed. In truth, the proper place for this kind of manipulation is in the client's report, not on the server.



+1


source


You can use CONVERT to get a fixed length date like:

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

What will be shown:

 2006-04-18T09:58:04.570

where each variable is at a fixed position.

+1


source


Building on the Andomar example above:

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

What will be shown:

20060418095804570

Caveat: using timestamp as primary key You will eventually get bitten 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 timestamp conversion rather than a string.

Solution # 2 Use a custom .NET function that wraps DateTime.ToString () so that you can pass in a specific format ("yyyymmddHHMMss" or whatever). Given the number of casts and replacements, perhaps this could be as easy as direct T-SQL.

+1


source






More articles:


All Articles