doujun7161 2013-07-24 20:24
浏览 40
已采纳

MySQL查询将日期字符串解释为日期

I saw a couple of posts on this problem, but I'm not getting it to work correctly. I have a PHP variable that I initially POST as a string. To be able to handle a one week date range, I am converting this using strToTime into datetime format (e.g. July 22, 2013 echos as 1374476400).

My table has its dates stored as text in a Y-m-d format (e.g. July 22, 2013 is stored as 2013-07-22). I need to run a query comparing these two values to find dates that fall within my one week range, so I am trying to convert that text into datetime format for comparison purposes.

Here is the SQL that I am using:

$wk_begin = $_POST['wk_begin'];
$wk_begin = strToTime($wk_begin);
$wk_end = $wk_begin + 8;
$sql = "SELECT * FROM myTable WHERE (DATE(date)>=$wk_begin AND DATE(date)<$wk_end)";

I'm not getting any errors, but my query isn't picking any records up, even though i know that there are 7 matching records in the table. So I have two questions:

1) Is there a better way to go about this? 2) If I am on the right track, how can I get the sql statement to convert the text based dates into a big integer so that I can do the comparison correctly.

Any help is appreciated. Thanks!

EDIT: Thanks for all of the suggestions. I've changed the table so that the dates are stored in 'date' format (no more strings). Here is the updated code. Still not picking up any values:

$wk_begin = $_POST['wk_date'];
$wk_end = $wk_begin + 7;
$sql = "SELECT * FROM Workouts WHERE 'date' BETWEEN '$wk_begin' AND '$wk_end'";
  • 写回答

2条回答 默认 最新

  • dsxay48646 2013-07-24 20:46
    关注
    <?php
    $wkbegin = $_POST['wk_begin'];
    $wk_begin = strToTime($wkbegin);
    $d=date('j', $wk_begin)+8;
    $m=date('n', $wk_begin);
    $y=date('Y', $wk_begin);
    $wk_end = date('Y-m-d', mktime(0,0,0, $m, $d, $y));
    $wkbegin = date('Y-m-d', $wk_begin);
    $sql = "SELECT * FROM `myTable` WHERE `date`>='".$wkbegin."' AND `date`<'".$wk_end."'";
    ?>
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题