Retrieving yyyymmdd from datetime format in SQL db in yyy-dd-mm

[Carina]

Hello,

I have a GridDateTimeColumn in a radgrid that displays the date in yyyyMMdd format. In my SQL Server db the format is in yyyy-mm-dd. 
This is my current aspx on that column:

<telerik:GridDateTimeColumn DataField="DateCode" DataType="System.DateTime" ItemStyle-HorizontalAlign="center" ItemStyle-Wrap="true" DataFormatString="{0:yyyyMMdd}" FilterControlWidth="70%" AllowSorting="true"  AutoPostBackOnFilter="true" HeaderText="DateCode" SortExpression="DateCode"  UniqueName="DateCode"  FilterListOptions="AllowAllFilters" PickerType="None" AllowFiltering="true" FilterDateFormat="yyyyMMdd">
 </telerik:GridDateTimeColumn>

This one is commented out, but I tried this as well:
<%--    <telerik:GridBoundColumn DataField="DateCode" DataType="System.Datetime" ItemStyle-HorizontalAlign="center" ItemStyle-Wrap="true" DataFormatString="{0:yyyyMMdd}"  FilterControlWidth="70%" AllowSorting="true"  AutoPostBackOnFilter="true" HeaderText="DateCode" SortExpression="DateCode" AllowFiltering="true"  UniqueName="DateCode">
             </telerik:GridBoundColumn>--%>

And when I try to filter the column, the filter control gets highlighted in red and a warning sign appear inside of it, but does not have any message. Do you know what is going on? Is it because the format is different from how it is in SQL db? If so, what else do I need to add/change/remove for it to work?

Attached is an image of what I get when I use the filter control.

[Vasil]

Hello,

You can store the dates in DATETIME column in your database instead of string column in order to use the internal filtering. The DATETIME column does not have filtering, it stores the dates in special data type.
Or you can use a GridTemplateColumn, place DateInput inside and then implement custom filtering similar to what is shown in the code behind of this demo:
http://demos.telerik.com/aspnet-ajax/grid/examples/programming/filteringtemplatecolumns/defaultcs.aspx

Regards,
Vasil
Telerik

[Carina]

Hi Vasil,

Sorry I meant to say, it is set as a DATE (yyyy-MM-dd) field in the database, not as string. I have tried custom filtering but I get an error saying:

'The datetime literal 'yyyyMMdd' value (displays actual date) is not valid.' 

When I write a query in sql database like: ...WHERE [DateCode] = '20130517' 
it does display the record with that date, even if it is displayed in the database as 2013-05-17, so I am wondering why it will not be filtered that way in the radgrid? I want to be able to filter the datecode column by entering yyyyMMdd.

[Vasil]

Hello Carina,

The DataFormatString that you are using for the column is the way that grid expect to pass the data to your database server. Not the way that it should display it client side. If you use DATE column in your datasource, don't set this property of the column.
To change only the DateFormat of the DatePicker of the column, you can handle the ItemCreated event of the grid, find the filter control, and change the DateFormat.

Regards,
Vasil
Telerik

[Carina]

Hi Vasil,

 I have my field format in the Sql database as:

DateCode(Date, null)

and it displays as: yyyy-MM-dd

In Visual Studio 2010, I have used the DataFormatString to change the way it is displayed on the grid, because the user wants it displayed as yyyyMMdd format, and they want to filter it in the same format. The reason I did not make the field a string is because in another form, where the DateCode is set, It is set by a RadDatePicker (when the user clicks a date, the date is displayed in the textbox as yyyyMMdd, and once they click 'Save' on that form. the DateCode field of the database is populated as yyyy-MM-dd because it has been selected with a RadDatePicker).

I have tried changing the format of the date displayed when saving, but it is not working either. I have not really tried the ItemCreated event on a radgrid. I followed the steps on the article, but it gives me the exact errors I was getting. Do you have an example of how I could do this? I work in vb.net not c sharp.

[Vasil]

Hi Carina,

Let me explain a bit further. If you have DATE column in your table, it does not have any format string, it is Date object. You see it in "yyyy-MM-dd", only when the database viewer (or the VS debugger) that are using is casting it to such format to display it in human readable way for you(instead of showing ones and zeroes). It could depends on your culture/regional settings.

When you use the filtering, the DateInput inside the filter has 3 different properties: .Text, .SelectedDate and .DbSelectedDate.
First is string, and keeps the value in format like: yyyy-MM-dd. You should not use this string for binding.
Second gets and sets a DateTime object, and can be used if you want to select a value from your C# code, and you have the value in proper DateTime object.
And the last one called DbSelectedDate is property that works with objects and tries to cast them the way that to work with various of data sources.

On the other hand, you have the ClientSide functionality of the DateInput. It always shows human readable string in the html <input> element. It depends on the DateFormat and DisplayDateFormat. This does not reflect any way to the server side properties, and you can use it if you want your user to see the value like yyyyMMdd, yyyy/MM/dd, dd/MM-yyyy, whenever you like. Setting these properties only changes the way that user sees the date when the input is in focus and when the input is not in focus. It does not reflects to the Server side properties, and they will always work for your filtering.

In short, do not change the FilterDateFormat, if you don't want problems (indeed it will work correct if left by default). Change only the DateFormat of the DateInput created for your filter.

Regards,
Vasil
Telerik