sql query date time

views:

117

answers:

sql query date time

i have a datestamp field in my table structure. using sql, i want to find any user who registered in yesterdays date using these time range.

eg: 2010-02-06 14:00:00 2010-02-07 10:00:00

i will be running this query once a day to grab users.

so tomorrow will be: 2010-02-07 14:00:00 2010-02-08 10:00:00

the day after tomorrow will be: 2010-02-08 14:00:00 2010-02-09 10:00:00

etc, etc.

select distinct * from users where loggedTime...

not sure how to query the date range? any ideas thanks

+2 A:

SQL Server

where loggedTime between
   DATEADD(hour, 14, DATEADD(DAY, 0, DATEDIFF(DAY,0,getdate()))) and
   DATEADD(hour, 34, DATEADD(DAY, 0, DATEDIFF(DAY,0,getdate())))

MySQL

where loggedTime between
   curdate() + interval 14 hour and
   curdate() + interval 34 hour

Adam Ruth 2010-02-08 00:38:00

i dont want to change the query all the time for the actual day as these are changing; the time doesn't change so this wouldn't work.

Menew 2010-02-08 00:41:59

Oh, right, that makes more sense. I fixed it.

Adam Ruth 2010-02-08 00:44:48

i got an error when i run it. #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'getdate()))) and DATEADD(hour, 34, DATEADD(DAY, 0, DATEDIFF(DAY,0,getdate()))' at line 2

Menew 2010-02-08 00:49:24

@Chocho: please, next time, let us know what DBMS you're using from the start. `curdate()` will work in MySQL: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_curdate

Adam Bernier 2010-02-08 00:55:48

ok will do, still i am getting mysql error:SELECT DISTINCT *FROM usersWHERE loggedTimeBETWEEN DATEADD(HOUR , 14, DATEADD(DAY , 0, DATEDIFF(DAY , 0, curdate( ) )))AND DATEADD(HOUR , 34, DATEADD(DAY , 0, DATEDIFF(DAY , 0, curdate( ) )))

Menew 2010-02-08 00:58:46

That's because it's SQL Server syntax. I added MySQL syntax.

Adam Ruth 2010-02-08 00:59:37

ansaurus

tags:

views:

answers:

sql query date time

related questions