Get Min / Max Date from DataTable

[Shilpa_Mohanty]

I have a column of data which is in format dd/MM/yyyy but type is String. I want to fetch the max and min value in format dd/MM/yyyy.
As in column it is in text format, i tried Cdate(row(“date”).toString) and also DateTime.ParseExact but getting error :- Conversion from String “27/03/2021” to Date is not valid

[ppr (Peter Preuss)]

we can do:
DateTime.ParseExact(“27/03/2021”, “dd/MM/yyyy”, CultureInfo.InvariantCulture)
[03/27/2021 00:00:00]

Edited: fixed minor bug in format string

[Palaniyappan (Forum Leader)]

@Shilpa_Mohanty

Cdate(row(“date”).toString

Hi

Try with this expression for DateTime version

DateTime.ParseExact(row(“date”).ToString.Trim.SubString(0,10), “dd/MM/yyyy”, system.Globalization.CultureInfo.InvariantCulture)

This will ensure that only first ten letters are considered because sometimes when reading from excel it might give in this format

dd/MM/yyyy hh:mm:ss

So here we are taking only this

dd/MM/yyyy. By using substring(0,10)

Cheers @Shilpa_Mohanty

[Manzar.Ali (Manzar Ali Sayyed)]

Hi,

Please try the following steps. 3 images are combined in 1

[ppr (Peter Preuss)]

@Shilpa_Mohanty

I want to fetch the max and min value in format dd/MM/yyyy.

Variables:

LINQ:

(From d In dtData.AsEnumerable
Where DateTime.TryParseExact(d("Column1").toString.trim, "dd/MM/yyyy", CultureInfo.InvariantCulture,DateTimeStyles.None, Nothing)
Select dp = DateTime.ParseExact(d("Column1").toString.trim, "dd/MM/yyyy", CultureInfo.InvariantCulture)
Order By dp).toarray

Min = arrDates.FirstOrDefault()
Max = arrDates.LastOrDefault()

Ensure following:

in case of the data column value cannot be parsed it will be filtered out. We can adopt as needed

• the substringing as mentioned by Palani
• handling multiple format patterns

Find starter help here:
FromDT_GetDate_MinMax.xaml (7.7 KB)

[Shilpa_Mohanty]

Tried this but its giving output with time and the format here in output is MM/dd/yyyy instead of dd/MM/yyyy

[Palaniyappan (Forum Leader)]

No worries

I got to see a custom component in this

Have a look on this

BalaReva.DataTable.Activities

Hi Friends. I have included some activities in the package of BalaReva.Datatable.Activities You can see in the below namespace [image] The key advantages are below 1.ConvertInputColumn - When you read the data from excel , it is always string format. Using this property can able change the data type and do the aggregation . 2.NullValue - If you have empty of null value in the data. can able to assign the default value for it. 3.Select- It is place to keep the condition(Where) Sample Fil…

Cheers @Shilpa_Mohanty

[Shilpa_Mohanty]

We cant use BalaReva activities

[Palaniyappan (Forum Leader)]

Hmm
Fine

You can use direct expression like this

Select(find) max date from datatable

I have a datatable with a few strings of dates: 9/30/2018 00:00:00 1/6/2018 00:00:00 2/15/2018 00:00:00 2/28/2018 00:00:00 3/15/2018 00:00:00 3/15/2018 00:00:00 12/15/2017 00:00:00 12/15/2017 00:00:00 12/15/2017 00:00:00 3/8/2018 00:00:00 2/28/2018 00:00:00 2/28/2018 00:00:00 I need to find max date value from this datatable. Can some one help me to achieve this? Thanks in advance Alexey