suran78
asked on
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.
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.
SELECT *
FROM someTable
WHERE timesheet BETWEEN DATEADD(HOURS, -24, GETDATE()) AND GETDATE()
FROM someTable
WHERE timesheet BETWEEN DATEADD(HOURS, -24, GETDATE()) AND GETDATE()
SQL Server will substitute the current ("now") time in place of GETDATE() when the query runs.
If DATEADD(HOURS, -24, GETDATE()) fails
try changing it to DATEADD(hh, -24, GETDATE())
try changing it to DATEADD(hh, -24, GETDATE())
Yes, sorry, it should have been "HOUR" not "HOURS":
BETWEEN DATEADD(HOUR, -24, GETDATE())
BETWEEN DATEADD(HOUR, -24, GETDATE())
ASKER
Hours gave me error, so I tried hour and it worked. Thanks. I will try hh too
HOUR and hh are the same - completley identical.
ASKER
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
ASKER
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())
BETWEEN DATEADD(??, -24, GETDATE())
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot. All queries worked out as I wanted.
ASKER
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.