adsense

Sunday, April 24, 2022

DataTable filter by date time

As per  Expression property of the DataColumn , a filter on DateTime column is created enclosing the date between the number symbol (#)

 DateTimeColumn = #dateTimeValue#

In addition the date should be formatted according to the format MM-dd-yyyy  as displayed below.


Dim strQuery = String.Format("DateExpired= #{0}#", dtExpired.ToString("MM-dd-yyyy"))    



 The query above could still fail if the columns also contain times. In such case you should change your formatting to include also output for time "HH:mm:ss" or instead  BETWEEN which is more clear.



Another workaround is use SQL CASE to check date and return a flag based ton the value as follows


SELECT 

CASE when DateExpired IS NOT NULL AND DateExpired <= Convert(date, getdate()) then 'Y' else 'N' end IsExpired



cheers

Samitha



No comments:

Post a Comment