Isolating the date data from a date time column-CodePudding

I’m a beginner web developer and have recently been working on my first database. I’ve run into an issue with an SQL function I’m writing that requires taking a distinct count of a column that uses the DATETIME datatype. The function requires a count of all the different days a person has worked, but the time information is irrelevant and causes my count to return the wrong result. What would be the best way to isolate the date information before I count the column?

This is my current progress on the query I will eventually put into a function:

SELECT COUNT(task_name) / COUNT(DISTINCT time_worked) FROM `completed_tasks` WHERE volunteer_id = x;

CodePudding user response：

You can change the datatype using the CAST() function.

Try it like this:

SELECT COUNT(task_name) / COUNT(DISTINCT CAST(time_worked AS DATE)) 
FROM completed_tasks 
WHERE volunteer_id = x;

CodePudding user response：

Use a simple DATE function

CREATE TABLE completed_tasks(volunteer_id int,task_name varchar(50), time_worked datetime)

INSERT INTO completed_tasks VALUES (1,'A',Now()),(1,'A',Now() - INTERVAL 1 DAY ),(1,'B',Now())

SELECT COUNT(task_name) / COUNT(DISTINCT DATE(time_worked)) FROM `completed_tasks` WHERE volunteer_id = 1;

| COUNT(task_name) / COUNT(DISTINCT DATE(time_worked)) |
| ---------------------------------------------------: |
|                                               1.5000 |

db<>fiddle here

CodePudding user response：

Try this using Date it will eliminate the time part of your timestamp but will keep the date

   SELECT COUNT(task_name) / 
    COUNT(DISTINCT 
    Date(time_worked)) FROM `completed_tasks` 
     WHERE 
   volunteer_id = x;