如何在不输入时间的情况下从两个相同的日期读取 [英] How to read from the two same dates without input the time

问题描述

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

I have a query like below.

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' 返回一个值.

When I submit a date like the following patients.created_at BETWEEN '2018-05-22' AND '2018-05-22' it doesn't return anything but if I enter BETWEEN '2018-05-22' patients.created_at 'AND' 2018-05-23' it returns a value.

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

I think if I just input two of the same date without entering the time then the time will be 00:00:00 - 00:00:00.

输入两个相同的日期而不输入时间如何读取00:00:00 - 23:59:59?

How to read 00:00:00 - 23:59:59 when we input two of the same date without entering the time?

表格中的日期格式填写如下"Thursday, August 20, 20, 2020, 9:49 AM" 但是只要输入日期就可以读取，例如2020-08-20.

The date format in the table is filled as follows "Thursday, August 20, 2020, 9:49 AM" but it can be read if we just input the date, for example 2020-08-20.

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

Your help means a lot to me, thank you

推荐答案

如果你的数据类型是 datetime 那么你可以添加:

if your data type is datetime then you can add:

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

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

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

if you want to input only one date, then u can use this

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

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

or you can simply use this

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

它将为您提供所有值为 '2018-05-22' 的行

it will give you all of the row which has the value '2018-05-22' on it

这篇关于如何在不输入时间的情况下从两个相同的日期读取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！