如何在不输入时间的情况下从两个相同的日期读取

Question

我有一个类似下面的查询。

SELECT
occupation AS 'Contact occupation',
sum(total) AS 'Quantity'
FROM
(
SELECT
CASE
WHEN contacts.occupation IS NULL THEN 'Other'
WHEN trim(contacts.occupation) = '' THEN 'Other'
ELSE contacts.occupation
END AS occupation, count(DISTINCT(concat(patients.id, '-', individual_appointments.practitioner_id))) AS total
FROM
individual_appointments
JOIN patients ON
patients.id = individual_appointments.patient_id
JOIN practitioners ON
practitioners.id = individual_appointments.practitioner_id
JOIN businesses ON
businesses.id = individual_appointments.business_id
JOIN referral_sources ON
referral_sources.patient_id = individual_appointments.patient_id
JOIN referral_source_types ON
referral_source_types.id = referral_sources.referral_source_type_id
LEFT JOIN contacts ON
referral_sources.referring_contact_id = contacts.id
WHERE
patients.created_at BETWEEN '2018-05-22' AND '2018-05-22'
AND CONVERT(NVARCHAR(100), referral_source_types.name) = 'Contact' [[
AND {{practitioner}}]] [[
AND {{clinic}}]]
AND isnull(individual_appointments.cancelled_at, '') = ''
AND individual_appointments.did_not_arrive <> 1
GROUP BY
contacts.occupation ) marketing_referrers
GROUP BY
occupation,
marketing_referrers.total
ORDER BY
total DESC;

当我提交如下所示的日期时，patients.created_at BETWEEN '2018-05-22' AND '2018-05-22'它不返回任何内容，但是如果输入，BETWEEN '2018-05-22' patients.created_at 'AND' 2018-05-23'则返回一个值。

我想如果我只输入两个相同的日期而不输入时间，那么时间将会是00:00:00 - 00:00:00。

00:00:00 - 23:59:59当我们输入两个相同的日期而不输入时间时，如何阅读？

表格中的日期格式按以下方式填充，"Thursday, August 20, 2020, 9:49 AM"但是，例如，如果我们仅输入日期，则可以读取该日期格式2020-08-20。

您的帮助对我很重要，谢谢

Answer 1

如果您的数据类型是，datetime则可以添加：

WHERE patients.createdAt >= '2018-05-22' and patients.createdAt < '2018-05-23'

因为2018-05-22=2018-05-22 00:00:00

如果您只想输入一个日期，那么您可以使用此日期

 WHERE patients.createdAt >= '2018-05-22 00:00:00'
       AND patients.createdAt <= '2018-05-22 23:59:59'

或者你可以简单地使用这个

 WHERE patients.createdAt LIKE '2018-05-22%'

它将为您提供所有具有值‘2018-05-22’的行