Unable to perform Date Filter in MySQL Database when date stored in VARCHAR column

Last Reply on Apr 16, 2014 02:53 AM By Mudassar

Posted on Apr 15, 2014 11:34 PM

MySQL's default DATE field format is "YYYY-MM-DD".

But in my one of the MYSQL DB Table (in which Data is coming from other service) Date is stored in Format "DD/MM/YYYY"

i.e., 24/03/2014

which creates problem when I Filter the data of that Table using DATE filter in My Web page.


There is a web page in which a Textbox , Button , and Grid is there.
When I choose Data in Textbox(using ajax calander) and click on button, related DATE data should show in Grid.

Since the Date format is not correct in DB table, it do not filter any data.

HTML code:

 

<asp:TextBox ID="TFDate" runat="server" Width="40%"></asp:TextBox>

<cc1:CalendarExtender ID="CalendarExtender1" runat="server" Enabled="True" Format="dd-MM-yyyy" TargetControlID="TFDate"></cc1:CalendarExtender>

<asp:Button ID="Bshow" runat="Server" Text="Show Report" OnClick="Bshow_Click1"/>

//Also, Gridview

Please suggest some way how to solve this problem while doing DATE filter. Because I can not change the date format which is coming in DB Table.

Please reply.

Posted on Apr 15, 2014 11:35 PM Modified on on Apr 16, 2014 09:52 PM

You are storing date as string in MySQL table and hence filter will not work.

You need to convert the Date returned from service to C# DateTime object and then insert in MySQL DateTime column

That's the correct way

 


Posted on Apr 16, 2014 02:53 AM

You cannot store date in Varchar column, if you need to work on Date functions. Thus there is no option other than storing in MySQL Data Type and if you need you can format and display on page