Need assistance deciphering .NET code for TO/FROM Date range logic...?

Need assistance please in deciphering what exactly this code is doing? The result should be code needed for our SSIS Begin and End Dates for a "Nightly" SSIS Package.

I'm mainly interested in HOW its figuring out what DATE RANGE to use for our SSIS nightly run......????

Public Function FormatDateRange(ByVal DateColumn As String) As String
      
        Dim StrSQL As String
        Dim DTFromDate As Date
        Dim DTToDate As Date
        Dim StrFromDate As String
        Dim StrToDate As String

        StrSQL = ""

        If Dts.Variables("PeriodMode").Value = "Hour" Then

            If Dts.Variables("IgnoreBeginOffset").Value = "True" Then
                StrFromDate = "N/A"
            ElseIf (DatePart(DateInterval.Year, Now()) + Dts.Variables("BeginOffset").Value / 366 / 24 < 1700) Then
                StrFromDate = "N/A"
            ElseIf DatePart(DateInterval.Year, Now()) + Dts.Variables("BeginOffset").Value / 366 / 24 > 2699 Then
                StrFromDate = "NO DATA"
            Else
                DTFromDate = DateAdd(DateInterval.Hour, Dts.Variables("BeginOffset").Value, Now())
                StrFromDate = Right("000" & CStr(DTFromDate.Year - 1700), 3) & Right("00" & CStr(DTFromDate.Month), 2) & Right("00" & CStr(DTFromDate.Day), 2) & "." & Right("00" & CStr(DTFromDate.Hour), 2) & "0000"
            End If

            If Dts.Variables("IgnoreEndOffset").Value = "True" Then
                StrToDate = "N/A"
            ElseIf DatePart(DateInterval.Year, Now()) + Dts.Variables("EndOffset").Value / 366 / 24 < 1700 Then
                StrToDate = "NO DATA"
            ElseIf DatePart(DateInterval.Year, Now()) + Dts.Variables("EndOffset").Value / 366 / 24 > 2699 Then
                StrToDate = "N/A"
            Else
                DTToDate = DateAdd(DateInterval.Hour, Dts.Variables("EndOffset").Value, Now())
                StrToDate = Right("000" & CStr(DTToDate.Year - 1700), 3) & Right("00" & CStr(DTToDate.Month), 2) & Right("00" & CStr(DTToDate.Day), 2) & "." & Right("00" & CStr(DTToDate.Hour), 2) & "9999"
            End If

        ElseIf Dts.Variables("PeriodMode").Value = "Day" Then

            If Dts.Variables("IgnoreBeginOffset").Value = "True" Then
                StrFromDate = "N/A"
            ElseIf DatePart(DateInterval.Year, Now()) + Dts.Variables("BeginOffset").Value / 366 < 1700 Then
                StrFromDate = "N/A"
            ElseIf DatePart(DateInterval.Year, Now()) + Dts.Variables("BeginOffset").Value / 366 > 2699 Then
                StrFromDate = "NO DATA"
            Else
                DTFromDate = DateAdd(DateInterval.Day, Dts.Variables("BeginOffset").Value, Now())
                StrFromDate = Right("000" & CStr(DTFromDate.Year - 1700), 3) & Right("00" & CStr(DTFromDate.Month), 2) & Right("00" & CStr(DTFromDate.Day), 2) & ".000000"
            End If

            If Dts.Variables("IgnoreEndOffset").Value = "True" Then
                StrToDate = "N/A"
            ElseIf DatePart(DateInterval.Year, Now()) + Dts.Variables("EndOffset").Value / 366 < 1700 Then
                StrToDate = "NO DATA"
            ElseIf DatePart(DateInterval.Year, Now()) + Dts.Variables("EndOffset").Value / 366 > 2699 Then
                StrToDate = "N/A"
            Else
                DTToDate = DateAdd(DateInterval.Day, Dts.Variables("EndOffset").Value, Now())
                StrToDate = Right("000" & CStr(DTToDate.Year - 1700), 3) & Right("00" & CStr(DTToDate.Month), 2) & Right("00" & CStr(DTToDate.Day), 2) & ".999999"
            End If

        ElseIf Dts.Variables("PeriodMode").Value = "Month" Then

            If Dts.Variables("IgnoreBeginOffset").Value = "True" Then
                StrFromDate = "N/A"
            ElseIf DatePart(DateInterval.Year, Now()) + Dts.Variables("BeginOffset").Value / 12 < 1700 Then
                StrFromDate = "N/A"
            ElseIf DatePart(DateInterval.Year, Now()) + Dts.Variables("BeginOffset").Value / 12 > 2699 Then
                StrFromDate = "NO DATA"
            Else
                DTFromDate = DateAdd(DateInterval.Month, Dts.Variables("BeginOffset").Value, Now())
                StrFromDate = Right("000" & CStr(DTFromDate.Year - 1700), 3) & Right("00" & CStr(DTFromDate.Month), 2) & "00.000000"
            End If

            If Dts.Variables("IgnoreEndOffset").Value = "True" Then
                StrToDate = "N/A"
            ElseIf DatePart(DateInterval.Year, Now()) + Dts.Variables("EndOffset").Value / 12 < 1700 Then
                StrToDate = "NO DATA"
            ElseIf DatePart(DateInterval.Year, Now()) + Dts.Variables("EndOffset").Value / 12 > 2699 Then
                StrToDate = "N/A"
            Else
                DTToDate = DateAdd(DateInterval.Month, Dts.Variables("EndOffset").Value, Now())
                StrToDate = Right("000" & CStr(DTToDate.Year - 1700), 3) & Right("00" & CStr(DTToDate.Month), 2) & "99.999999"
            End If

        ElseIf Dts.Variables("PeriodMode").Value = "Year" Then

            If Dts.Variables("IgnoreBeginOffset").Value = "True" Then
                StrFromDate = "N/A"
            ElseIf DatePart(DateInterval.Year, Now()) + Dts.Variables("BeginOffset").Value < 1700 Then
                StrFromDate = "N/A"
            ElseIf DatePart(DateInterval.Year, Now()) + Dts.Variables("BeginOffset").Value > 2699 Then
                StrFromDate = "NO DATA"
            Else
                StrFromDate = Right("000" & CStr(DatePart(DateInterval.Year, Now()) + Dts.Variables("BeginOffset").Value - 1700), 3) & "0000.000000"
            End If

            If Dts.Variables("IgnoreEndOffset").Value = "True" Then
                StrToDate = "N/A"
            ElseIf DatePart(DateInterval.Year, Now()) + Dts.Variables("EndOffset").Value < 1700 Then
                StrToDate = "NO DATA"
            ElseIf DatePart(DateInterval.Year, Now()) + Dts.Variables("EndOffset").Value > 2699 Then
                StrToDate = "N/A"
            Else
                StrToDate = Right("000" & CStr(DatePart(DateInterval.Year, Now()) + Dts.Variables("EndOffset").Value - 1700), 3) & "9999.999999"
            End If

        End If

        If Dts.Variables("IgnoreBeginOffset").Value = "False" And Dts.Variables("IgnoreEndOffset").Value = "False" And Dts.Variables("BeginOffset").Value > Dts.Variables("EndOffset").Value Then
            StrSQL = "1=0"
        ElseIf StrFromDate = "NO DATA" Or StrToDate = "NO DATA" Then
            StrSQL = "1=0"
        ElseIf StrFromDate = "N/A" And StrToDate = "N/A" Then
            StrSQL = "1=1"
        ElseIf StrFromDate = "N/A" Then
            StrSQL = DateColumn & " <= " & StrToDate
        ElseIf StrToDate = "N/A" Then
            StrSQL = DateColumn & " >= " & StrFromDate
        Else
            StrSQL = DateColumn & " >= " & StrFromDate & " and " & DateColumn & " <= " & StrToDate
        End If

        Return StrSQL

    End Function

End Class

Open in new window

LVL 17
MIKESoftware Solutions ConsultantAsked:
Who is Participating?
 
Vadim RappCommented:
You need to look at the dts variables "periodmode", "beginoffset", and "endoffset" mentioned above. The article referenced by CodeCruiser tells where to find them.
0
 
CodeCruiserCommented:
Its using DTS variables to pick up values like EndOffset, PeriodType etc

http://technet.microsoft.com/en-us/library/ms135941.aspx
0
 
Vadim RappCommented:
It takes the current time and shifts it using dts variables "periodmode", "beginoffset", and "endoffset", defined somewhere else.

For example:

DTFromDate = DateAdd(DateInterval.Hour, Dts.Variables("BeginOffset").Value, Now())
StrFromDate = Right("000" & CStr(DTFromDate.Year - 1700), 3) & Right("00" & 
CStr(DTFromDate.Month), 2) & Right("00" & CStr(DTFromDate.Day), 2) & "." & Right("00" & CStr(DTFromDate.Hour), 2) & "0000"

Here it takes current time, adds the number of hours defined by the variable "BeginOffset", then shapes the result.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
JimFiveCommented:
Looks pretty straight forward,
It is adding an offset to the current date/time based on the Period defined  in the variable PeriodMode (If PeriodMode = "Hour" then it adds hours))  If the Offsets are invalid then it ignores that value.
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
So, does it select PREVIOUS "DAY",...or PREVIOUS "MONTH"...or....what?

I"m mainly seeking a summary on specifically what DATES is using to gather DATA for my nightly ETL Run....??
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Ok I know I can decipher it,.. but, I was hoping that someone here who is more well versed in this CODE can simply tell me what the specific DATE RANGE being called is...???

I certainly can decode this myself,.. just will take longer.

NOTE: based on today's date,...if someone can provide me with something like this:

FROM DATE= ????     TO DATE= ????

I'd be greatly appreciated....and award points immediately...many thanks...!

If not, no worries...
0
 
Vadim RappCommented:
If you tell what are the values of the variables we mentioned above, we will certainly decipher.
0
 
JimFiveCommented:
It is
FROM Now()-"BeginOffset"
TO Now() + "EndOffset"

WHERE "BeginOffset" and "EndOffset" are a number of Periods of length "PeriodMode"

UNLESS "IgnoreBeginOffset" or "IgnoreEndOffset" are checked in which case the appropriate end of your range is ignored.

If the calculation of the date ends up being out of bounds(before 1700-01-01 or after 2699-12-31) then that date calculation is also ignored.

Everything I have "quoted" above is a variable that is referenced but not defined in what you posted.  You will need to look at the calling entity and possibly previous items in the package to determine the actual values.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.