Datetime query - between today yesterday

Experts,

Please help me with a query that pulls all the dates+time from a datatime field.  This dattime field is called timesheet.

I have to pulls all the datetime date from this timesheet field that fall between present(curent or right now) date and time and yesterday, that is  ---     NOW( date + time) minus 24hrs.

The data in timesheet field looks like this:

1/20/2005 9:00:00 AM

Please help me with the correct quesry to get the right data.

suran78Asked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
I suspect what you really may need is for *all* of yesterday, rather than just from the current time forward.  For example, even if it's 16 May 3:27pm now, you want everything from 15 May from 00:00 on, not from 3:27pm on.

*If* that's true, you can do this:

SELECT *
FROM someTable
WHERE timesheet BETWEEN CONVERT(CHAR(8), DATEADD(HOUR, -24, GETDATE()), 112) AND GETDATE()
0
 
suran78Author Commented:
Another thing,

Both the start datetime and end datetime are varibles tehy cannot be hard coded.  That is,  start datetime  = enddate -24 hrs, and end datetime  = now.
0
 
Scott PletcherSenior DBACommented:
SELECT *
FROM someTable
WHERE timesheet BETWEEN DATEADD(HOURS, -24, GETDATE()) AND GETDATE()
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott PletcherSenior DBACommented:
SQL Server will substitute the current ("now") time in place of GETDATE() when the query runs.
0
 
andrewbleakleyCommented:
If DATEADD(HOURS, -24, GETDATE()) fails
try changing it to DATEADD(hh, -24, GETDATE())
0
 
Scott PletcherSenior DBACommented:
Yes, sorry, it should have been "HOUR" not "HOURS":

 BETWEEN DATEADD(HOUR, -24, GETDATE())
0
 
suran78Author Commented:
Hours gave me error, so I tried hour and it worked. Thanks.  I will try hh too
0
 
andrewbleakleyCommented:
HOUR and hh are the same - completley identical.
0
 
suran78Author Commented:
Yes, hh and hour works fine.  I would like to hold this question till next week, I might have to do some changes inteh query.  Thanks
0
 
suran78Author Commented:
Looks like I need minutes or seconds.  I replaced hh or hour with second.  But it did not return anything.  And minute gave me syntax error.  Please advise what the syntax should be for minutes and second:

BETWEEN DATEADD(??, -24, GETDATE())
0
 
andrewbleakleyCommented:
Syntax (from BOL)

Datepart,Abbreviations
Year              yy, yyyy
quarter          qq, q
Month            mm, m
dayofyear       dy, y
Day                dd, d
Week              wk, ww
Hour               hh
minute            mi, n
second            ss, s
millisecond       ms
0
 
suran78Author Commented:
Thanks a lot.  All queries worked out as I wanted.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.