SSIS Populating Date Time Format: "hh mm ss nnn cc yy mm dd"

Question

This is probably a pretty simple issue but:

I'm trying to name a file TestSheet.hhmmssnnnccyymmdd

I set up a variable in SSIS and my expression in the expression builder is set up as :

@[User::str_Var] + Right("0" + (DT_STR,4,1252) DatePart("hh",getdate()),2) + Right("0" + (DT_STR,4,1252) DatePart("mi",getdate()),2) + Right("0" + (DT_STR,4,1252) DatePart("ss",getdate()),2) + ".txt"

and I know :

Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) + Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2)

Will get me "mm" and dd"

My question is.. I have "hh mm ss mm dd", how do I get "nnnccyy"?

Answer 1

Even though you are asking for nanosecond (one billionth), I'm assuming you meant millisecond (one thousandth) precision. DATEPART only provides slicers to millisecond precision.

Using an expression, the bits to build out your format string would look like this

Right("0" + (DT_STR,2,1252) DatePart("hh",getdate()),2) 
+ Right("0" + (DT_STR,2,1252) DatePart("mi",getdate()),2) 
+ Right("0" + (DT_STR,4,1252) DatePart("ss",getdate()),2) 
+ Right("000" + (DT_STR,3,1252) datepart("Ms", getdate()),3) 
+ (DT_STR,4,1252) datepart("yyyy", getdate()) 
+ (DT_STR,2,1252) datepart("mm", getdate()) 
+ (DT_STR,2,1252) datepart("dd", getdate())

I don't know how getdate works internally but in research I did find this question What is the best way to measure how long code takes to execute? but I presume it's basically calling DateTime.Now Money quotes from Eric Lippert in there but this one was most pertinent.

Note that "wall clock time" measured by DateTime is only accurate to something like 30 ms. DateTime is for representing things like a clock on the wall, or the time you last edited a file; it doens't have to have nanosecond accuracy, and so it does not

If you must go to nanosecond precision, happy hunting but an expression won't cut it. As @fegemo indicated, a script task can get you to one ten-millionth custom formatting but that's still two orders of magnitude off from your desired precision.

this.Dts.Variables["User::CustomFormat"].Value = DateTime.Now.ToString("HHmmssfffffyyyyMMdd");