1

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;

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.

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.

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

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

ryan
  • 53
  • 6
  • Why oh why won't you indent anything??? – FoggyDay Jan 14 '21 at 04:57
  • Use `patients.created_at BETWEEN '2018-05-22 00:00:00' AND '2018-05-22 23:59:59'`. If `fsp` of the column is set then add according decimal part to the upper limit, for example, `'2018-05-22 23:59:59.999'`. – Akina Jan 14 '21 at 05:43
  • `The date format in the table is filled as follows "Thursday, August 20, 2020, 9:49 AM"` I don't understand this statement;at least, I hope that I don't) – Strawberry Jan 14 '21 at 09:07
  • @Strawberry sorry i mean "Thursday, August 20, 2020, 9:49 AM" is data values in table. Data type is datetime – ryan Jan 18 '21 at 07:30
  • @Akina is there any other way if we input the two same dates without the time? Like two same date but can read 00:00:00 - 23:59:59 without needing to input the time? – ryan Jan 18 '21 at 07:36
  • This depends on data datatype, not on parameters value. – Akina Jan 18 '21 at 09:18

2 Answers2

1

if your data type is datetime then you can add:

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

because 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%'

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

18Man
  • 572
  • 5
  • 17
  • hi @18Man Those are two different dates, I mean what if you only input the same two dates without inputting the time it will produce 00:00:00 - 23:59:59. Example: I input patients.created_at> = '2018-05-22' and patients.created_at <'2018-05-22' then it will not generate a value because the default time is 00:00:00 - 00:00:00. How to change the time format so that when we input only two of the same date without the time it will read 00:00:00 - 23:59:59 – ryan Jan 18 '21 at 02:47
0

Try using ...WHERE DATE(patients.created_at) = '2018-05-22'...

farhodius
  • 510
  • 3
  • 8
  • based on this answer https://stackoverflow.com/a/14104364/12582712, this query is a performance killer – 18Man Jan 18 '21 at 08:20