Mysql日期函数 [英] Mysql Date Functions

问题描述

我在这里度过美好的一天，因为我尝试在谷歌搜索，但仍然没有任何反应。



截至目前我正在用VB.NET制作一个程序我在一个54字段表中持有22,000条MySQL和MySQLyog的记录。



现在这些都是我的问题。



i有一个字段名称LicExp，它是一个varchar，我知道它必须是日期。这些字段包含大量数据。



Q.

1.）此字段中的示例数据为10/10/1010 和10-JUN-10如何将其转换为可接受日期字段的数据类型，我的意思是更新它。

2.）如何将Varchar转换为日期而不用影响数据。

3.）如果发生错误，我怎么能备份这些数据，因为它有这么多的数据





i希望有人给我一个简短的解释。





:) Cheeers

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

推荐答案

1。要将包含日期的VARCHAR转换为DATETIME，请使用 STR_TO_DATE [ ^ ]。

1. To convert a VARCHAR containing a date to a DATETIME, use STR_TO_DATE[^].

/* 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;

如果您不知道哪一天，一个月或一年，例如12/12/12然后你运气不好。



解析字符串很乱，所以如果你必须将日期存储为字符串，使用国际标准 [ ^ ]，而不是您选择的任意格式，例如创建数据集的人。

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.

这篇关于Mysql日期函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！