Problem with datetime format

Help
1

hey guys i’m trying to sort an excel file base on a column which are dates like this
image
i’m using (From row In dtFilter Order By DateTime.ParseExact(row.Item(“Database date”).ToString, “dd MMMM yyyy HH:mm:ss”,System.Globalization.CultureInfo.InvariantCulture) Ascending Select row).CopyToDataTable
i always get error when i sort it saying “string was not recognized as datetime”. i suspected it was because of the date format, so i changed all the format in the excel to dd/MM/yyyy HH:mm:ss manually(which is a lot of work) and try again with that format, and it worked perfectly.

anyone knows what date format shoud i use ? or is there another solution ?
Thank you

1 Like
2

@RobertoEwaldo

Before doing all those things, Just try to print one of the cell value in Date Column and see how it is printing. Based on that you write expression.

3

Buddy your format is correct…May the string might have some issue, Lets check whether any space is there after the date in your column

(From row In dtFilter Order By DateTime.ParseExact(row.Item(“Database date”).ToString.Trim, “dd MMMM yyyy HH:mm:ss”,System.Globalization.CultureInfo.InvariantCulture) Ascending Select row).CopyToDataTable

Cheers

4

it prints exactly the same
image

5

there’s no space unfortunately, here maybe u could check what’s wrong
excel.xls (1.0 MB)

6

I think the language used for date is Dutch. In that case you need to provide Dutch culture info.

new System.Globalization.CultureInfo("nl-NL")

1 Like
7

still didn’t work, actually it’s Indonesian, i tried “id-ID” too but also didn’t work

8

As @KannanSuresh suggested it’s working, please try again

DateTime.ParseExact(date1, “dd MMM yyyy HH:mm:ss”,new System.Globalization.CultureInfo(“id-ID”))

9

In that case it should be

DateTime.ParseExact(date1, “dd MMM yyyy H:mm:ss”,new System.Globalization.CultureInfo(“id-ID”))

Hour does not have leading 0 so you should use H instead of HH. There could be other mismatches too. Please check and update.

2 Likes
10

hi @Sreelatha278,
it didn’t work tho, is there anything wrong with this ?
(From row In dt Order By DateTime.ParseExact(row.Item(“Database date”).ToString, “dd MMMM yyyy HH:mm:ss”, new System.Globalization.CultureInfo(“id-ID”)) Ascending Select row).CopyToDataTable

i also tried this
dt.AsEnumerable.OrderBy(Function(d) DateTime.ParseExact(d(“Database date”).ToString,“dd MMMM yyyy HH:mm:ss”,new System.Globalization.CultureInfo(“id-ID”))).CopyToDataTable

thank you

12

it works with 1 H !
thanks buddy :+1:

1 Like
13

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.