Datatable filtering based on year part of date

Ravi Kini

I have a datatable, I want to filter that table by comparing a particular YEAR with the DateTime column of tit. How can I filter that table from its Select() method so that I can compare only Year part of date?

TnTinMn

Well this isn't pretty, but it works.

Dim dt As New DataTable
With dt
' this would be the column that contains the DateTime value from which to extract the Year
.Columns.Add(New DataColumn("mydate", GetType(DateTime)))

' convert the DT value to a string
' From: http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx
' All literal expressions must be expressed in the invariant culture locale.
' When DataSet parses and converts literal expressions, it always uses the invariant culture, not the current culture.

' DateTime to string is in the pattern Month/Day/Year Hr:mm:ss (A/P)M
.Columns.Add(New DataColumn("stringdate", GetType(String), "Convert([mydate], 'System.String')"))

' Determine the length of the month part. 1 or 2
' Note: Substring indexing begins with 1 not zero
.Columns.Add(New DataColumn("lenmonth", GetType(Int32), "IIF(SUBSTRING(stringdate,3,1) = '/', 2, 1)"))
.Columns.Add(New DataColumn("month", GetType(Int32), "SUBSTRING(stringdate, 1, lenmonth)"))

' remove the month part from stringdate
.Columns.Add(New DataColumn("temp1", GetType(String), "SUBSTRING(stringdate, lenmonth + 2, LEN(stringdate) - (lenmonth + 2))"))

' Determine the length of the day part. 1 or 2
.Columns.Add(New DataColumn("lenday", GetType(Int32), "IIF(SUBSTRING(temp1,3,1) = '/', 2, 1)"))
.Columns.Add(New DataColumn("day", GetType(Int32), "CONVERT(SUBSTRING(temp1, 1, lenday), 'System.Int32')"))

' remove the day part from temp1
.Columns.Add(New DataColumn("temp2", GetType(String), "SUBSTRING(temp1, lenday + 2, LEN(temp1) - (lenday + 2))"))

' extract the 4 digit year
.Columns.Add(New DataColumn("year", GetType(Int32), "CONVERT(SUBSTRING(temp2, 1, 4), 'System.Int32')"))

' add some data to test against
.Rows.Add(New Object() {#1/2/1999 12:30:00 PM#})
.Rows.Add(New Object() {#11/2/1999 12:30:00 PM#})
.Rows.Add(New Object() {#1/23/1999 12:30:00 PM#})
.Rows.Add(New Object() {#1/2/2000 12:30:00 PM#})
End With

' set a target year
Dim targetyear As Int32 = 1999
' select rows that match the target year
Dim rows() As DataRow = dt.Select("[year] = " & targetyear.ToString)

Ravi Kini

Thanks for your kind reply. You were compared year with datatable's column which is of type number. But I actually want to compare year with the column of type datetime. In that only year part should be compared.