Solved

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

Posted on 2014-02-18
10
328 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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 set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

744 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

12 Experts available now in Live!

Get 1:1 Help Now