|
I want to create an SQL syntax using VB.Net which will allow me to filter data on a date field in my table in MySql with the current Date. The issue is I have the dates saved in the Table with Type VARCHAR(). One way is to convert the current Date to String and then compare. But is there any process by which I can convert the Table String field to Date format and then compare with current Date. Basically my SQL Syntax with be something like below:
strSql = "Select * from TBL1 where TBL_Date1 >= " + Now()
This will not work since TBL_DATE1 is a VARCHAR Type and NOW() is of Type DateTime
I am pretty new to .net and hence would like some help from you all.
|
|
|
|
|
It's a really bad idea to store dates as VARCHAR in your database, you should use the DATETIME type. However, you can convert it to a date value as described here[^].
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
SPSandy wrote: This will not work since TBL_DATE1 is a VARCHAR Type
- Dates are stored as a date, not as a varchar. That's what needs to be changed, not the query; converting the varchar to a date to do a comparison is a dirty hack, trying to work around a previous mistake. (If you move the database or the culture changes, things will break.)
- It's recommended to sanitize the query; use a DbParameter, the way it's now I could destroy your database.
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
All your database are belong to us ?
My advice is free, and you may get what you paid for.
|
|
|
|
|
I understand your advice and would like to go in the right way. But the problem is being new to VB.Net I am not able to save the date Value into MySql from a Text Box. Since MySql uses the format yyyy/mm/dd I have tried to convert my date before storing by using Format(Date.Parse(txtdt.Text), "yyyy/mm/dd"). But this is not working. I have been trying to find some solution from internet but not able to get anything. This prompted me to move ahead for the time being by using VarChar. But now I am stuck since comparison in VarChar does not give the right result 
|
|
|
|
|
SPSandy wrote: But the problem is being new to VB.Net
..that's not a problem, just a matter of time. Next year you'll be explaining the concept to someone else who is new 
SPSandy wrote: I am not able to save the date Value into MySql from a Text Box. Since MySql uses the format yyyy/mm/dd I have tried to convert my date
Databases do not store date's in a specific notation, they store it as a number. Internally, it is just "n" numbers that have passed since januari 1st, 1900, (or some other date) with the time-part stored as a fraction. (Imagine the double 3693.5; that'd be 3693 days since the epoch, and .5 as the time, so probably 12:00 AM)
When you "ask" MySql for a date, it returns a Date type, a double - not a text with month-separators as a string. The problem with using a string, is that the datetime-functions do not know how to interpret it - they see a string, a text, not a datetime.
When you get a value from a textbox, that datetime is in the "users" locale; it's in a specific format, perhaps with daylight saving. You convert that to a "real" DateTime and store it in it's native format. (Otherwise we'd have to teach all the date-functions the differences between timezones and teach them how to read a date in a foreign notation!)
To sum it up; if you have the option of converting the field back to a DateTime, please do so; it'll save you from a lot of headaches in the future. Yes, I can see how it is a workaround for the problem you described, but the price for the trade-off is too high. If you're having trouble making it work, post the code and we'll have a look.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks Eddy for the comment. I have finally been able to solve the issue. MySql does accept only yyyy/mm/dd when saving data. In case your data is in any other format then it will display an error.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
