Geek Answers Handbook

SSIS Fill time format: "hh mm ss nnn cc yy mm dd"

This is probably a pretty simple problem, but:

I am trying to name the file TestSheet.hhmmssnnnccyymmdd

I set the variable in SSIS and my expression in the expression builder is set 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)

Get me "mm" and dd "

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

+4
source share
2 answers

Even if you ask for a nanosecond (one billionth), I assume that you mean millisecond (thousandth) accuracy. DATEPART provides only slicers for millisecond precision.

Using an expression, the bits to build the format string will 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 I found this question in a study. What is the best way to measure how long the code takes to execute? but I suppose he basically calls DateTime.Now Cash quotes from Eric Lippert there, but this one was the most appropriate.

Note that the “wall clock time” measured by DateTime is only accurate, something like 30ms. DateTime is designed to represent things like a clock on a wall or the time a file was last edited; he does not need to have nanosecond accuracy and therefore it does not

If you must move to nanosecond accuracy, a happy hunt, but expression will not reduce it. As pointed out by @fegemo, a script task can lead you to one ten million custom formatting , but that's two more orders of magnitude your desired accuracy.

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

According to the documentation, enter a description of the link here , GetDate() returns a date in type DT_DBTIMESTAMP , which has an accuracy of a maximum of 3 fractional digits after seconds:

A timestamp structure consisting of year, month, day, hour, minute, second, and fractional seconds. Fractional seconds have a maximum scale of 3 digits. enter link description here

You can use fractional numbers to determine part of the information you need:

 DT_DBTIMESTAMP yyyy-mm-dd hh:mm:ss[.fff]

Steps:

  • Get milliseconds using DATEPART("Ms", GetDate())
  • Divide the result by 10^6 (convert milliseconds to nanoseconds)
  • Pass the result of DT_I4 to get only the integer part.

Unfortunately, this would always return 000 due to the limited precision of DT_DBTIMESTAMP .

But I do not know how to get the current date from the system with the necessary accuracy.

0
source

More articles:


All Articles