Solved

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

Posted on 2014-02-18
10
332 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

832 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