asked on

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

SOLUTION

Nasir Razzaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Vadim Rapp

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

JimFive

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Marcus Aurelius

ASKER

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....??

ASKER CERTIFIED SOLUTION

Vadim Rapp

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Marcus Aurelius

ASKER

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...

SOLUTION

Vadim Rapp

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

JimFive

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial