CODEWITHSUNDEEP
mysqlsqldateselecttime
user3800924
user3800924

Reputation: 407

Select from table where date and time values are in separate columns

I have a dateColumn with value "2014-10-10" and startTimeColumn with value "10:10:44". How can I select everything from the table where dateColumn is between 'startDate' and 'endDate', and where the startTimeColumn is greater than or equal to 'startTime' on the 'startDate'?

This is what I mean: (I know this will not work but its just to show what I mean)

SELECT * FROM table1 WHERE dateColumn BETWEEN 'startDate' AND 'endDate' AND IF TRUE(
    IF(dataColumn == 'startDate')
        THEN IF(startTimeColumn >= 'startTime')
            RETURN TRUE
        ELSE
            RETURN FALSE
    ELSE
        RETURN TRUE
);

I am using MySQL and I can't use an SQL function.

Upvotes: 1

Views: 122

Answers (3)

Uueerdo
Uueerdo

Reputation: 15941

This might make better use of any indexes you might have on those fields (the OR might interfere with that though)

WHERE (dateColumn > startDate AND dateColumn <= endDate) 
   OR (dateColumn = startDate AND startTimeColumn >= startTime)

Upvotes: 1

M.Ali
M.Ali

Reputation: 69494

SELECT *
FROM Table 
WHERE CAST(DateColumn AS DATETIME) + CAST(TimeColumn AS DATETIME) 
       BETWEEN 'StartDateTime' AND 'EndDatetime'

Upvotes: 1

juergen d
juergen d

Reputation: 204746

Combine the date and time columns

SELECT * FROM table1 
WHERE timestamp(dateColumn, startTimeColumn) BETWEEN 'startDateAndTime' 
                                                 AND 'endDateAndTime'

Upvotes: 6

Related Questions