Grabbing results between business hours

Databases
1

I have a DB with some times in a column called startime

i.e

01/10/2015 14:34:30
05/10/2015 12:25:28
06/10/2015 12:19:26

What I want to do is query this column for all results that are between 8am and 6pm.

I can easily do a between for today’s date but how would i do a query for all between the times i wish for the month.

the column is a varchar type of column as it’s a CSV import that generated the table.

Any help would be great.

2

which dbms, please?

as you can imagine, mysql’s date functions won’t work in sql server, and sql server’s date functions won’t work in oracle, and oracle’s date functions won’t work in db2, and so on…

3

ah sorry

mysql

4

okay, use STR_TO_DATE() function to convert the VARCHAR to a DATETIME value

i’ll leave it to you to figure out the format string (http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format)

then apply TIME() function like so –

WHERE TIME(STR_TO_DATE(...)) BETWEEN '08:00' AND '18:00"
5

as always a genius for helping with mysql nice one!

6

struggling with this a bit.

SELECT * FROM `Reception_Overflow2_Month` WHERE `Start Time`(STR_TO_DATE(`Start Time`,'%d/%m/%Y %k:%i:%s')) BETWEEN '08:00' AND '18:00'

but reading up the STR_TO_DATE might not be what will work.

it’s that or cause it doesn’t like the space in the col.

i keep getting #1305 - FUNCTION phonestats.Start Time does not exist

7

change this –

WHERE `Start Time`(STR_TO_DATE(`Start Time`,'%d/%m/%Y %k:%i:%s')) BETWEEN '08:00' AND '18:00'

to this –

WHERE TIME(STR_TO_DATE(`Start Time`,'%d/%m/%Y %k:%i:%s')) BETWEEN '08:00' AND '18:00'
1 Like
8

that has worked thank you for all your help once again!

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.