Solved

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

Posted on 2014-02-18
10
330 Views
Last Modified: 2016-02-10
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

0
Comment
Question by:MIKE
  • 3
  • 2
  • 2
  • +1
10 Comments
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 84 total points
ID: 39870255
Its using DTS variables to pick up values like EndOffset, PeriodType etc

http://technet.microsoft.com/en-us/library/ms135941.aspx
0
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 250 total points
ID: 39870256
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
 
LVL 15

Assisted Solution

by:JimFive
JimFive earned 166 total points
ID: 39870262
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
 
LVL 17

Author Comment

by:MIKE
ID: 39870321
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 250 total points
ID: 39870547
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
 
LVL 17

Author Comment

by:MIKE
ID: 39870626
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
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 250 total points
ID: 39870657
If you tell what are the values of the variables we mentioned above, we will certainly decipher.
0
 
LVL 15

Assisted Solution

by:JimFive
JimFive earned 166 total points
ID: 39870742
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

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now