Datatable Compute Method Convert string column to

问题:

I have a column in datatable having dates with format dd/MM/yyyy HH:mm. I fill the datatable using the code below which is common for more than 1 select statements so i cannot specify column and their datatype before filling the datatable. Any manipulation after filling the data is acceptable to me.

data_adapt = New OracleDataAdapter(query, OraConn)
dt = New DataTable
data_adapt.Fill(dt)

For paging i create a copy of the datatable using skip and take as below

dtLineupCopy = New DataTable
dtLineupCopy = dtLineup.AsEnumerable().Skip(startRows).Take(pageSize)).CopyToDataTable()

Now the issue is when I use Compute method it doesn't treat the column values as date type and returns some random date value from the column instead of minimum value.

Arvdate = dtLineupCopy.Compute("Min(Arrivaldate)", "")

Is there a way to convert the datatype for the column?

Also tried adding a new column of datetime type but it throws error System.FormatException: String was not recognized as a valid DateTime

dtLineupCopy.Columns.Add("ArvDate", getType(DateTime), "CONVERT(Arrivaldate, 'System.DateTime')")

Data in Arrivaldate column of dtLineupCopy.

22/09/2012 01:02
27/09/2012 17:01
1/10/2012 1:02
13/10/2012 07:26
14/10/2012 19:47
20/10/2012 00:00
20/10/2012 00:00

回答1:

How about converting to date in the query:

Min(TO_DATE(Arrivaldate, format_mask)

http://www.techonthenet.com/oracle/functions/to_date.php

回答2:

If the query that you pass in results in Arrivaldate being brought back as a string not a date then the preferred option would be to change that query? Instead of selecting Arrivaldate, select:

to_date(Arrivaldate, 'DD/MM/YYYY HH24:Mi') as Arrivaldate

If somehow that's not an option then you parse the strings afterwards. Doing that within the confines of the DataTable.Compute expression means rolling your own parsing date into sortable format function something like ...

Arvdate = dtLineupCopy.Compute("Min(Substring(Arrivaldate,7,4) + Min(Substring(Arrivaldate,4,2) + Min(Substring(Arrivaldate,1,2) + ... etc ..... " )) )