Click here to Skip to main content
15,868,440 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Good Day to all Im here because I have tried searching in Google but still nothing happens.

As of now i am making a program in VB.NET and i am holding 22,000 records which is in MySQL and MySQLyog in a 54 field table.

Now these are my set of questions.

i have a field name LicExp and it is a varchar, I know it must be date. these field holds a lot of data.

Q.
1.)The sample data in this fields are "10/10/1010" and "10-JUN-10" how can i convert it in a type of data that can accept a date field i mean update it.
2.)how can i convert a Varchar to date without affecting the data.
3.)how can i have a backup of this data in case an error occurs because it has so many data's


i hope someone give me a brief explanation about this.


:) Cheeers
Posted

1 solution

1. To convert a VARCHAR containing a date to a DATETIME, use STR_TO_DATE[^].
SQL
/* Convert 31/12/2014 to a DATETIME */
SELECT STR_TO_DATE(LicExp, '%d/%m%Y') FROM foo;

/* Convert 2014-DEC-31 to a DATETIME */
SELECT STR_TO_DATE(LicExp, '%Y-%b-%d') FROM foo;


If you don't know which is the day, month, or year e.g. 12/12/12 then you're out of luck.

Parsing strings is messy, so if you ever have to store a date as a string, use an international standard[^], not an arbitrary format of your choice like the person who created your dataset did.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900