Date falling between two given dates if not throw as empty datatable

Help Activities
, , , , , , , ,
1

My input S.no. date. Value
12/31/2022 24:07:08 AM. 45
03/02/2022. 56
03/28/2022. 78
03/16/2022. 56
03/08/2022. 55
My start date is 03/01/2022 and end date is 03/31/2022. Dates falling between start and end is My output should be
S.no. date. Value

    03/02/2022. 56                  
    03/28/2022.  78                        
    03/16/2022.  56            
    03/08/2022.   55

If no date is falling it should return as empty datatable

2

Hi @sruthesanju

Check the below xaml file:

Sequence8.xaml (12.8 KB)

Hope it helps!!

3

Can you pls share the image

4

Hi @sruthesanju

=> Read Range Workbook
Output-> dt
=> Use below syntax in Assign:

Rows = (From row In dt.AsEnumerable()
                              Let dateString As String = If(row.Field(Of String)("Date").Contains(" "), row.Field(Of String)("Date").Split(" "c)(0), row.Field(Of String)("Date"))
                              Let dateValue As DateTime = DateTime.ParseExact(dateString, "M/d/yyyy", CultureInfo.InvariantCulture)
                              Where dateValue >= New DateTime(2022, 3, 1) AndAlso dateValue <= New DateTime(2022, 3, 31)
                              Select row)

Rows is of DataType IEnumerable(System.Data.DataRow)
=> Use below condition in If

If
   Rows.Any()
    Assign-> dt_Output = Rows.CopyToDataTable
       If
          IsNothing(dt_Output) OrElse (dt_Output.AsEnumerable.All(function(x) x.ItemArray.All(function(y) String.IsNullOrEmpty(y.ToString))))'
      Then
             \\ Do Nothing
      Else
           Write Range Workbook `dt_Output`
      End If
Else
    Assign-> dt_Output = New DataTable()
End If

dt_Output is of DataType System.Data.DataTable

image
image794×1426 66.5 KB

Please download the workflow file for better understanding
Regards

5

17092931388382674731109447725195
170929313883826747311094477251951920×1083 70.3 KB

Ihave tried this it is working if I have dates falling between two given dates if not it is throwing an error like there is no data row

6

Hi @sruthesanju

Did you try the above workflow, for me it’s not throwing any error. It creates a New datatable according to If.

Please try running the workflow code by changing the excel file paths

Regards

7

I have tried it didn’t work can you tell me what is the error in above one

8

Hi @sruthesanju

Can you send me the syntax. As if there are no rows it can’t be directly assigned to datatable. So you need to store the that syntax of DataType IEnumerable(System.Data.DataRow) and pass in If condition like if Rows.Any then write it into datatable or create a new datatable.

Regards

9

12/31/2022 24:07:08 AM. 45
04/02/2022. 56
04/28/2022. 78
04/16/2022. 56
04/08/2022. 55
My start date is 03/01/2022 and end date is 03/31/2022. There is no Dates falling between start and end date.my datatable should return as empty row

10

Hi @sruthesanju

Will you be using that datatable variable anywhere?

Regards

11

Yes I am assign it to datatable

12

Hi @sruthesanju

Sequence8.xaml (12.8 KB)

Please follow the steps from above xaml file. It will not throw any error. I have checked with that.

Hope it helps!!

13

@sruthesanju

filteredDataTable = (From row In yourDataTable.AsEnumerable()
                     Let dateValue = DateTime.ParseExact(row("date").ToString(), {"MM/dd/yyyy","MM/dd/yyyy HH:mm:ss tt"}, System.Globalization.CultureInfo.InvariantCulture).ToString("MM/dd/yyyy")
					 Let dateval=DateTime.ParseExact(dateValue,"MM/dd/yyyy",System.Globalization.CultureInfo.InvariantCulture, DateTimeStyles.None)
                     Where dateval >= DateTime.ParseExact("03/01/2022", "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture) AndAlso
                           dateval <= DateTime.ParseExact("03/31/2022", "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture)
                     Select row).CopyToDataTable()

image
image1231×679 32.5 KB