0

I want to filter the gridview by comparing a datetime column of type datetime in sql server with a date textbox.


The datetime column in sql server is being stored/displayed (as default) in the yyyy-mm-dd hh:mm:ss.sss format.

However the date textbox is in the format dd/mm/yyyy.

<asp:TextBox ID="date_tb" runat="server" TextMode="Date"></asp:TextBox>


I tried using FilterExpression="CONVERT(VARCHAR, [ScheduledDateTime], 105) LIKE '%{0}%'"> but it didn't work.

FilterExpression="CONVERT([ScheduledDateTime], 'System.String', 105) LIKE '%{0}%'"> also did not work.


I then tried using

SelectCommand="SELECT [ScheduledDateTime], CONVERT(VARCHAR, [ScheduledDateTime], 105) as d ... FROM table1

FilterExpression="d LIKE '%{0}%'">

but it didn't work as well.



edit:

I have changed the ControlParameter into <asp:ControlParameter Name="ScheduledDateTime" ControlID="date_tb" PropertyName="Text" type="DateTime"/> so I no longer have to be concerned about the date format.

Although now there is a problem to comparing date with datetime.

j_t_fusion
  • 223
  • 2
  • 7
  • 20
  • DateTimes are stored in binary - they are not stored in any "format" – D Stanley Jan 14 '14 at 22:22
  • Just try this: `FilterExpression="Cast([ScheduledDateTime] as Date)='{0}'"`. But the reason this is not so cool is because (I believe) it will need your SQL Server operate under British rules. This is why CAST is better in the sense that it will work in any locality – T.S. Jan 14 '14 at 22:58
  • @T.S. The expression contains undefined function call cast(). – j_t_fusion Jan 15 '14 at 13:00
  • @j_t_fusion: Sorry, in previous post I meant Convert better because no worry about localization of Sql Server. On my server this works: `where cast(a as date) = '03/24/2013'`. – T.S. Jan 15 '14 at 13:42
  • @j_t_fusion: You're right. What we do here will not work for you because you use SqlDataSource. It is limited to certain set of filtering. You can't call Sql Server functions here - it goes agains datarow collection, not Sql Server Db. Try this: `FilterExpression="[ScheduledDateTime] > And [ScheduledDateTime] < `. That should work for sure – T.S. Jan 15 '14 at 13:50
  • @T.S. i tried `FilterExpression="ScheduledDateTime > #{0}-1# AND ScheduledDateTime < #{0}#+1"` and i got a `Cannot perform '+' operation on System.DateTime and System.Int32.` error. the `-1` portion works but the `+1` does not. – j_t_fusion Jan 15 '14 at 20:55
  • Sorry, I confused you. I didn't mean write in code `date-l` - that was a logical thing. Just hard code something to test. Lets say, `FilterExpression="[ScheduledDateTime] > #01/01/2014# And [ScheduledDateTime] < #01/03/2013#` - if in US. – T.S. Jan 15 '14 at 21:15
  • Here is good reference of what you can use in filter expression http://www.csharp-examples.net/dataview-rowfilter/ – T.S. Jan 15 '14 at 21:19
  • @T.S. your logic is only partially right, it should be `[ScheduledDateTime] >= ` and not `[ScheduledDateTime] > `. – j_t_fusion Jan 16 '14 at 18:42
  • @T.S. not really. i still haven't found a way to `date + 1` in 'FilterExpression', so in the mean time i am not using 'FilterExpression'. currently i am manipulating the `SelectCommand` string in the back end coding based on which textbox(s) are empty. from the user's perspective the page is filtering the `gridview` table but technically it is sending an sql query to the db. – j_t_fusion Jan 16 '14 at 22:53
  • @j_t_fusion: You limiting yourself by using SqlDataSource control.This why you running into troubles when more flexible functionality needed. Can you set your date programmatically before it is forwarded to {0} in filter expression – T.S. Jan 16 '14 at 23:02

3 Answers3

1

This is what you can do

select * from MyTable where convert(varchar, a, 103) = '03/24/2013'

You probably don't want to compare date to the seconds. The right side of equasion comes from the textbox

If you want it really good, then do this

Dim sql as String = "select * from MyTable where convert(varchar, a, 103) = @1"

@1 is a parameter that you want to add to Command Object and it will have value of your textbox.

T.S.
  • 18,195
  • 11
  • 58
  • 78
  • I am using `FilterExpression` so I don't see how this is going to work. – j_t_fusion Jan 14 '14 at 22:12
  • My answer shows how SQL server reacts to date comparison. If you notice, I have US format- 101. I just noticed, you need 103 for dd/mm/yyyy – T.S. Jan 14 '14 at 22:16
  • Don't compare dates by converting them to strings. Compare them natively as dates. – Anon Jan 14 '14 at 22:17
  • Wrong Anon!! On UI you have dd/mm/yyyy. In Db you have long number with hours , minutes, seconds, milliseconds. YOu need to convert them to common data. You can't convert UI stuff to milliseconds. – T.S. Jan 14 '14 at 22:19
  • as far as i am aware of, `FilterExpression` only works with string which is why i am converting them to string. – j_t_fusion Jan 14 '14 at 22:23
  • @T.S., you are confusing a date with a string representation of a date. Compare dates as dates, i.e: `CAST(a AS date) = CONVERT(date,@1,103)` – Anon Jan 14 '14 at 22:40
  • Well, when I cast to `date`, I am already stripping hours etc and can compare to string, `cast(a as date) = '03/24/2013'`, which must be implicitly converted. So, what is the difference really? It is especially clear on Oracle. Really, there are situations where converting date to string is nice. – T.S. Jan 14 '14 at 22:53
0

http://msdn.microsoft.com/en-us/library/ms187928.aspx

Use format 103 to convert a 'dd/mm/yyyy' string to a date. You are using 105, which is 'dd-mm-yyyy'

Anon
  • 10,660
  • 1
  • 29
  • 31
  • i am sorry but i just don't see how using format 103 is going to work. – j_t_fusion Jan 14 '14 at 22:27
  • Can you explain what you mean when you claim "The datetime in sql server is being stored (as default) in the yyyy-mm-dd hh:mm:ss.sss format."? Is the database schema using the `datetime` type or the `varchar` type? – Anon Jan 14 '14 at 22:31
  • the datetime column in db is a `datetime` type. by default the values are stored/displayed in the db in the yyyy-mm-dd hh:mm:ss.sss format. so if i directly convert from `datetime` to `varchar`, the output will be something like `2014-12-31 18:22:33:234`. – j_t_fusion Jan 15 '14 at 13:05
0

You should not need to do any string conversion to compare DateTimes in SQL. When you use LIKE, SQL will convert the DateTime into a string value and you need to make sure that you're using the same format on both ends. If your source data does not include any time component, then you should just be able to do:

FilterExpression="ScheduledDateTime = {0}"

If it does include time components and you want to truncate the time, you can try:

FilterExpression="dateadd(dd, datediff(dd,0, ScheduledDateTime ), 0) = {0}"

or

FilterExpression="ScheduledDateTime BETWEEN {0} AND dateadd(dd, 1, {0})"
D Stanley
  • 149,601
  • 11
  • 178
  • 240
  • I believe `BETWEEN {0} AND dateadd(dd, 1, {0})` logically is incorrect. You probably want to get date between date minus all minutes hours, etc and date + maximum hours, minutes, etc – T.S. Jan 14 '14 at 22:41
  • The expression contains undefined function call dateadd(). – j_t_fusion Jan 15 '14 at 13:13