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).
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).
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:
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 = dataTableIs this a bug or is this an expected behaviour?
------------------------------
S Rashidi
------------------------------
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.
'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)
------------------------------
Christopher Potvin
Senior RPA Developer
SS&C Blue Prism
Canada
------------------------------
