Time Stamp - Between

Databases
1

Hi,

How do i do a query where i can only get records where the timestamp is between 2 date/time ranges. I only need to do it between dates but i thought doing it with time too would be easier. But my query below is not working and keeps showing 0 results when there are rows of data between the date range. Any help would be great please.

SELECT *
FROM  `leads`
WHERE  `ProgramID` LIKE  '1' AND `TimeStamp` BETWEEN '2013-01-22 00:00:00' AND '2013-01-22 23:59:99'
ORDER BY  `leads`.`LeadID` DESC

I’ve also tried


SELECT *
FROM  `leads`
WHERE  `ProgramID` LIKE  '1' AND `TimeStamp` BETWEEN '%2013-01-22%' AND '%2013-01-22%'
ORDER BY  `leads`.`LeadID` DESC

Any help would be much appreciated.

Thank you.

2

first, may i suggest you stop immediately using those horrid backticks*

this is what you want, instead of BETWEEN –

SELECT something
     , anything
     , just_not_the_dreaded_evil_select_star
  FROM leads 
 WHERE ProgramID = 1 
   AND `TimeStamp` >= '2013-01-22' 
   AND `TimeStamp`  < '2013-01-23'
ORDER 
    BY LeadID DESC
  • except where you have to, when the column or table name is a reserved word like TIMESTAMP, in which case it would be far far better to rename the column
3

Thank you for the help, i used your Query but it didnt work, it only worked if i did it like this but thank you for the help :slight_smile:

(btw i needed to use the * as im expoerting everything to a CSV file)


SELECT * 
FROM  `leads` 
WHERE  `ProgramID` LIKE  '$programid' AND `TimeStamp` >= '$yearfrom-$monthfrom-$dayfrom 00:00:00' 
   AND `TimeStamp`  < '$yearto-$monthto-$dayto 23:59:99'
ORDER 
    BY LeadID DESC
4

if your query worked but mine didn’t, then you did something wrong when testing my query :slight_smile:

i think you overlooked the point that i was trying to make –

don’t use <= 23:59:59, use < the next day