Convert a column of string to datetime

Help
1

hi guys i’m trying to read an excel file and sort it base on the date in a column. i initialized the datatable, it gets error when i set the column type as datetime, maybe i should just set it to string ? how do i achieve what i want to do ?

1 Like
2

@RobertoEwaldo Say for example your output data table is Dtoutput, to filter column date date after read file take an assign activity, in expression place keep as Dtoutput. Select(‘’ date = ‘place the date you want to filter’). Copy to data table. Thus expression will filter it with date. Hope this helps, plz let me know if any thing goes wrong

3

Hi @RobertoEwaldo
Follow these linK

Thanks

4

hi @balu should i initialize the datatable ? if i should, what would be the column’s type ?

5

Hi @RobertoEwaldo
Yes first read excel data into datatable then in assign activity
datatable = (From row In filteredSchemaDT Order By DateTime.ParseExact(row.Item(“RECEIPTDATE”).ToString, “dd/MM/yyyy”,
System.Globalization.CultureInfo.InvariantCulture) Descending Select row).CopyToDataTable
column type can be string

Thanks

3 Likes
6

hi @balu i tried it but i get an error saying string was not recognized as datetime,
could u pls check it for me ? Main.xaml (41.9 KB)

Thank you

7

Hi @RobertoEwaldo

may i know a sample date value from that column, actually based on that the datetime format has to be modified…it its like
02/12/2019…it must be dd/MM/yyyy
or
03/25/2019…it must be MM/dd/yyyy

like datetime format must match exactly with the string format …kindly change the dateformat accordingly in your xaml…else you were going good

Hope this would help you
Cheers…

8

Hi @RobertoEwaldo

Main (1).xaml (41.9 KB)

You have given column name as “Database Date” which should be “Database date”
please run and confirm it if works .

Thanks

9

still doesn’t work bro, same error string doesnt recognized as datetime

10

Fine may i know at which activity the error was thrown…,may be a screenshot

Cheers

11

image
this is the date from the excel, i already set the format to dd MMMM yyyy hh:mm:ss but still got the same error

12

here mate
image

13

Aah it should be like this @RobertoEwaldo

DateTime.ParseExact(Yourstringvariable,“dd MMMM yyyy HH:mm:ss”.System.Globalization.CultureInfo.InvariantCulture)

This is the difference
DateTime.ParseExact(Yourstringvariable,“dd MMMM yyyy hh:mm:ss”.System.Globalization.CultureInfo.InvariantCulture)

14

Hi @RobertoEwaldo
your datatable column type is date time can you change it to string and try.

Thanks

15

changed it to

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

still got the same error :sweat_smile:

16

it’s already a string
image

i changed it to string since i got error like this for using datetime type
image

17

Hello,

A suggest you to have a look to the Microsoft documentation about datetime format here :

So you’ll can find the format that fit to your dateTime

Try to write to console the value of DateTime.Now.ToSting(“dd MMMM yyyy HH:mm:ss”) does it produce the same output as : 06 Mei blablabla ?

18

Hi
it should Be like this @RobertoEwaldo

DateTime.ParseExact(Yourstringvariable,“dd MMMM yyyy H:mm:ss ”.System.Globalization.CultureInfo.InvariantCulture)

And use single H instead of double HH

This would work

For more reference look at this url :upside_down_face:)

Cheers…

1 Like
19

Hi @RobertoEwaldo

Hope these works
datatable = (From row In Datatable Order By Convert.todatetime(row.Item(“date”).tostring).tostring(“yyyymmdd”) Select row).CopyToDataTable

Thanks

20

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