cancel
Showing results for 
Search instead for 
Did you mean: 

Bug in calculating date in code stage?

SRashidi
Level 3

I've got a simple code that performs the following:

Creates a collection with three columns:

  • dateValue (type datetime)
    • Contains all the dates of current month. For example: 1st October 2023 to 31st October 2023
  • dayOfWeek  (type text)
    • Shows the day for the given date. Example: 'Sunday' for 1st October.
  • numberOfOccurance (type number)
    • Shows the number of times a day appeared. Example: 1st October 2023 is the first Sunday of October, hence the value for this column will be 1

Note that the code run from Visual Studio gives a different result to the code run from the code stage in Blue Prism. I've shared the codes for Visual Studio as well as code stage at the bottom of this thread. Both visual studio code and the code stage were executed/ran from the same machine.

Result from Visual Studio: (date starts from 1st October and ends in 31st October). The day of the week associated with the date is also correct (1st October 2023 is indeed a Sunday, and the first Sunday of the month).

12747.png

Result of the code stage:

  • Date range starts from 30th September (instead of 1st October).
  • 29th October is missing
  • The day associated with the date is wrong (for instance: 1st October is not a Monday).

12748.png

As promised, here is the codes I've used to get the results in the screenshots above:

Imports System
Imports System.Data

Module Program
    Sub Main(args As String())
        ' Create a DataTable
        Dim dataTable As New DataTable()
        dataTable.Columns.Add("dateValue", GetType(Date))
        dataTable.Columns.Add("dayOfWeek", GetType(String))
        dataTable.Columns.Add("numberOfOccurrence", GetType(Integer))

        ' Get the first day of the current month in UTC
        Dim currentDate As Date = Date.UtcNow ' Use UTC time
        Dim firstDayOfMonth As Date = New Date(currentDate.Year, currentDate.Month, 1)

        ' Get the last day of the current month in UTC
        Dim lastDayOfMonth As Date = firstDayOfMonth.AddMonths(1).AddDays(-1)

        ' Iterate through the days of the month and add them to the DataTable
        Dim currentDateOfWeek As Date = firstDayOfMonth
        Dim occurrenceCounter As Integer = 1

        While currentDateOfWeek <= lastDayOfMonth
            Dim row As DataRow = dataTable.NewRow()
            row("dateValue") = currentDateOfWeek.Date
            row("dayOfWeek") = currentDateOfWeek.ToString("dddd")
            row("numberOfOccurrence") = occurrenceCounter
            dataTable.Rows.Add(row)

            ' Move to the next day of the same day of the week
            currentDateOfWeek = currentDateOfWeek.AddDays(1)
            If currentDateOfWeek.DayOfWeek = firstDayOfMonth.DayOfWeek Then
                occurrenceCounter += 1
            End If
        End While

        ' Print the result to the console
        For Each row As DataRow In dataTable.Rows
            Console.WriteLine("Date (UTC): " & row("dateValue") & ", Day of the Week: " & row("dayOfWeek") & ", Occurrence: " & row("numberOfOccurrence"))
        Next

    End Sub
End Module

Code stage details:

An output named resultData as collection:

12749.png

Value in the code tab:

' Create a DataTable
Dim dataTable As New DataTable()
dataTable.Columns.Add("dateValue", GetType(Date))
dataTable.Columns.Add("dayOfWeek", GetType(String))
dataTable.Columns.Add("numberOfOccurrence", GetType(Integer))

' Get the first day of the current month in UTC
Dim currentDate As Date = Date.UtcNow ' Use UTC time
Dim firstDayOfMonth As Date = New Date(currentDate.Year, currentDate.Month, 1)

' Get the last day of the current month in UTC
Dim lastDayOfMonth As Date = firstDayOfMonth.AddMonths(1).AddDays(-1)

' Iterate through the days of the month and add them to the DataTable
Dim currentDateOfWeek As Date = firstDayOfMonth
Dim occurrenceCounter As Integer = 1

While currentDateOfWeek <= lastDayOfMonth
    Dim row As DataRow = dataTable.NewRow()
    row("dateValue") = currentDateOfWeek.Date
    row("dayOfWeek") = currentDateOfWeek.ToString("dddd")
    row("numberOfOccurrence") = occurrenceCounter
    dataTable.Rows.Add(row)

    ' Move to the next day of the same day of the week
    currentDateOfWeek = currentDateOfWeek.AddDays(1)
    If currentDateOfWeek.DayOfWeek = firstDayOfMonth.DayOfWeek Then
        occurrenceCounter += 1
    End If
End While
' Now, 'dataTable' contains the desired information.

resultData = dataTable

Is this a bug or is this an expected behaviour?



------------------------------
S Rashidi
------------------------------
2 REPLIES 2

Hi Rashidi, 

Strange one. I just ran your codes on my BP, 7.1.1 and I got the anticipated result for November. I think Blue Prism uses your local OS time to do DateTime variables. If you convert the UTC back to Local Time, I wonder if that resolves the bug. I don't know enough about your code to hardcode the month of October into the code stage. I have added an action in a loop to convert UTC back to Local, now everything is being set to the top of the hour for every row. I have attached the code for converting UTC back to Local and its input/outputs. You could add it to your existing code or do as you please with it. Let me know if this works.

12733.jpg
'Declare and set variables for code use
Dim utcDateTime = DateTime.SpecifyKind(UTC, DateTimeKind.Utc)
Dim tz As TimeZoneInfo

'If given time zone then find a system time zone associated to then convert
If Time_Zone_ID <> "" Then
	
	tz = TimeZoneInfo.FindSystemTimeZoneById(Time_Zone_ID)
	local = TimeZoneInfo.ConvertTimeFromUtc(utcDateTime, tz)

'If not then just use local time as UTC
Else
	local = utcDateTime.ToLocalTime()

End If

' BP converts local dates back to UTC for its
' internal representation; so we have to convince it that
' this is UTC already so it doesn't convert it further.
local = DateTime.SpecifyKind(local, DateTimeKind.UTC)
12734.jpg
12735.jpg
12736.jpg


------------------------------
Christopher Potvin
Senior RPA Developer
SS&C Blue Prism
Canada
------------------------------

Right Chris,

This is a problem I learnt the hard way.

It is better to convert the date to text (Using FormatDate) and the convert back to Date (using ToDate). This removes the impact of timezone.

I'd also try to fetch the start and end date before the code stage if the issue persists.

Hope this help!



------------------------------
WeitghtRPAMatey
------------------------------

WeitghtRPAMatey