如何设置日期字符串的格式并检查日期字符串是否为今天和之后

Question

我的当前表如下。花名册：ID(int(、花名册日期(varchar(、工作人员(varchar。

我知道我的RosterDate列没有设置为datetime，我希望有一个解决方案，我不需要更改数据类型，让SQL处理查询/解决方案。

我正在尝试检索今天及以后的所有行，以及按日期降序排列的行。我的日期条目的格式如下DD/MM/YYYY。

我遇到的问题是SQL以以下格式操作日期格式YYYY-MM-DD HH:MM:SS。

我当前的SQL语句。

SELECT * FROM rosters WHERE Worker = 'John Doe' AND FORMAT(RosterDate, 'dd-MM-yyyy') >= CURRENT_DATE() ORDER BY FORMAT(RosterDate, 'dd-MM-yyyy') DESC

此SQL语句存在两个问题

FORMAT(RosterDate, 'dd-MM-yyyy') >= CURRENT_DATE()

无法按预期工作。

---

和

FORMAT(RosterDate, 'dd-MM-yyyy') DESC

如果进行，则仅按DD值订购

SELECT FORMAT(RosterDate, 'dd-MM-yyyy') AS Test ....

它返回

27
26
25

代替

27/10/2019
26/10/2019
25/10/2019

为了检索结果，合适的SQL查询是什么？我浏览了文档，寻找解决方案，但我一直被日期的格式所困扰。

以下是我使用的大部分代码：

形式：

<?php include '../../dbsession.php'; ?>
<?php
$workers_stmt = $db->prepare("SELECT * FROM workers");
$workers_stmt->execute(); 
$workers_row_count = $workers_stmt->rowCount();
$yards_stmt = $db->prepare("SELECT * FROM yards GROUP BY YardName");
$yards_stmt->execute();
$yards_row_count = $yards_stmt->rowCount();
$rosters_stmt = $db->prepare("SELECT * FROM rosters");
$rosters_stmt->execute();
$rosters_row_count = $rosters_stmt->rowCount();
$datetime = isset($_POST['datetime']) ? date("j/n/Y", strtotime($_POST['datetime'])) : date('j/n/Y', strtotime('now'));
?>
<thead>
<tr>
<th class="darkest" style="width: 150px;"><?php echo isset($_POST['datetime']) ? date('l', strtotime($_POST['datetime'])) : date('l'); ?>
<?php echo $datetime; ?></th>
<?php
$i = 0;
$worker = array();
if($workers_row_count > 0){
while($row = $workers_stmt->fetch(PDO::FETCH_ASSOC)){
$worker[$i] = $row['FName'].' '.$row['LName'];
echo '<th class="text-center darkify" style="width: 100px !important;">'.$row['FName'].' '.$row['LName'].'</th>';
$i++;
}
}
?>
</tr>
</thead>
<tbody>
<?php
while($row = $yards_stmt->fetch(PDO::FETCH_ASSOC)){
$html = '<tr>';
$html .= '<td class="darkify" style="white-space: nowrap; overflow: hidden; text-overflow: ellipsis;">
<b>['.$row['Dealership'].']</b><br/><span class="text-primary">['.$row['YardName'].']</span>
</td>';
for($i = 0; $i < count($worker); $i++){
$variables = "'".$datetime."', '".$row['Dealership']."', '".$row['YardName']."', '".$worker[$i]."'";
$html .= '<td class="p-0 m-0" id="'.str_replace(" ", "_", '['.$datetime.']['.$row['Dealership'].']['.$row['YardName'].']['.$worker[$i]).']">
<button class="btn-dark w-100 text-success" style="height: 70px;" onClick="addRoster(event, this.childNodes[1], '.$variables.')">
<select class="form-control">
<option value="4AM">4AM</option>
<option value="5AM">5AM</option>
<option value="6AM" selected>6AM</option>
<option value="7AM">7AM</option>
<option value="8AM">8AM</option>
<option value="9AM">9AM</option>
<option value="10AM">10AM</option>
<option value="11AM">11AM</option>
<option value="12PM">12PM</option>
<option value="1PM">1PM</option>
<option value="2PM">2PM</option>
<option value="3PM">3PM</option>
<option value="4PM">4PM</option>
</select>
</button>
</td>';
}
$html .= '</tr>';
echo $html;
}
?>
</tbody>
<script type="text/javascript">
cRB = '<button class="btn-dark w-100 text-danger" style="height: 70px;" onClick="cancelRoster(this)"><b>CANCEL</b></button>';
<?php
$x = 0;
while($rosters_row = $rosters_stmt->fetch(PDO::FETCH_ASSOC)){
for($i = 0; $i < count($worker); $i++){
if($rosters_row['Worker'] == $worker[$i]){
$x++;
echo 'try{ document.getElementById("'.str_replace(" ", "_", '['.$rosters_row['RosterDate'].']['.$rosters_row['Dealership'].']['.$rosters_row['Yard'].']['.$worker[$i]).']").innerHTML = cRB; } catch { console.log("APPLYING ROSTER '.$x.'...") }';
}
}
}
?>  
</script>

Javascript函数：

function addRoster(event, element, date, dealership, yard, worker){
if(event.path[0].tagName == "SELECT"){
//DO NOTHING.
}else{
datastring = "";
for(i = 0; i < element.childNodes.length; i++){
if(element.childNodes[i].selected){
datastring += "start=" + element.childNodes[i].value + "&";
}
} 
datastring += "date=" + date + "&";
datastring += "dealership=" + dealership + "&";
datastring += "yard=" + yard + "&";
datastring += "worker=" + worker;
element.parentNode.parentNode.innerHTML = http("POST", "/admin/formfunctions/add_roster.php", datastring);
}
}

add_roster.php

<?php
if(isset($_SESSION["admin"]) && $_SESSION["admin"] == true){
$keyval = "[".$_POST['date']."][".$_POST['dealership']."][".$_POST['yard']."][".$_POST['worker']."]";
$date = $_POST['date'];
$dealership = $_POST['dealership'];
$yard = $_POST['yard'];
$worker = $_POST['worker'];
$start = $_POST['start'];
$add_roster_stmt = $db->prepare("INSERT INTO rosters (KeyVal, RosterDate, Dealership, Yard, Worker, Start) VALUES (:keyval, :date, :dealership, :yard, :worker, :start)");
$add_roster_stmt->execute(array(
"keyval" => $keyval,
"date" => $date,
"dealership" => $dealership,
"yard" => $yard,
"worker" => $worker,
"start" => $start
));
$html = '<button class="btn-dark w-100 text-danger" style="height: 70px;" onClick="cancelRoster(this)"><b>CANCEL</b></button>';
echo $html;
}
?>

Answer 1

根据您正在使用CURRENT_DATE()和从FORMAT获得的结果，您实际上使用的是MySQL服务器，在这种情况下，您需要使用的函数是STR_TO_DATE:

SELECT * 
FROM rosters
WHERE Worker = 'John Doe'
AND STR_TO_DATE(RosterDate, '%d/%m/%Y') >= CURRENT_DATE() 
ORDER BY STR_TO_DATE(RosterDate, '%d/%m/%Y') DESC

Answer 2

您应该修复您的数据模型！使用正确的格式存储日期将简化您对数据模型所做的几乎所有操作。

在SQL Server中，FORMAT()将值转换为字符串。您希望将您的值转换为日期。一种方法使用CONVERT():

SELECT r.*
FROM rosters r
WHERE Worker = 'John Doe' AND
CONVERT(DATE, RosterDate, 103) >= CONVERT(DATE, GETDATE())
ORDER BY CONVERT(DATE, RosterDate, 103) DESC;