在MySQL中使用DATE，TIME数据类型从数据库中获取数据 [英] fetch data from database using DATE ,TIME data type in mysql

问题描述

大家好...

我有带有列的表
说明，用户，日期(DATE)，时间(TIME)

我写了Query Like:

hi all...

i have table with columns
description,user,date(DATE),time(TIME)

i written Query Like:

SELECT * FROM `tbluser` where `date`<='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and `time`<='" + DateTime.Now.ToString("hh:mm") + "' and user='" + txtuser.text + "'

但是它不会少于时间来获取记录我怎么能少然后正确的日期和时间来获取记录，任何人都可以帮助我
在此先感谢...

but it wont fetch records less than the time how can i fetch records less then the correct date and time can any one please help me
thanks in advance...

推荐答案

不要将文字(日期，文本等)连接到SQL语句中.而是使用 MySqlParameter [

Don''t concatenate literals (date, text etc) to your SQL statements. Instead use MySqlParameter[^].

When that is done, set the proper datetime to the parameter as value and you should get correct results.

Addition:

Currently you have a query like:

SELECT * FROM `tbluser` where `date`<='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and `time`<='" + DateTime.Now.ToString("hh:mm") + "' and user='" + txtuser.text + "'"

您应该做的是使用参数(请参阅我提供的链接)，以便您的查询看起来像:

What you should do is to use parameter (see the link I provided) so your query would look something like:

SELECT * FROM `tbluser` where `date`<= @date and `time`<=@time and user=@user"

修改之后，您可以通过C#为所有三个参数定义适当的值(如示例中所示).

我不确定，但是看起来您实际上可以将日期和时间存储在数据库中作为字符信息.如果是这样，那么您应该更正表结构并在表中定义单个datetime 字段，然后改用该字段.请参阅: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html [

After that modification you define proper values for all of the three parameters via C# (as in the example).

I''m not sure but it looks like you may actually store dates and times in the database as character information. If that is true, then you should correct the table structure and define a single datetime field in your table and use that instead. See: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html[^]

If that is done your query could look something like:

SELECT * FROM `tbluser` 
where datetimefield <= @datetimetosearch 
and user=@user"

这样可以消除将日期和时间信息放在单独的字段中的问题，并且如果您当前使用字符数据类型，则可以消除转换问题.

That would eliminate the problems for having the date and time information in separate fields and possibly conversion problems if you currently use character data type.

这篇关于在MySQL中使用DATE，TIME数据类型从数据库中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！