[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Datetime query - between today yesterday

Posted on 2005-05-10
12
Medium Priority
?
883 Views
Last Modified: 2010-05-18
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.

0
Comment
Question by:suran78
  • 5
  • 4
  • 3
12 Comments
 

Author Comment

by:suran78
ID: 13971999
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13972141
SELECT *
FROM someTable
WHERE timesheet BETWEEN DATEADD(HOURS, -24, GETDATE()) AND GETDATE()
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13972151
SQL Server will substitute the current ("now") time in place of GETDATE() when the query runs.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 11

Expert Comment

by:andrewbleakley
ID: 13975114
If DATEADD(HOURS, -24, GETDATE()) fails
try changing it to DATEADD(hh, -24, GETDATE())
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13977862
Yes, sorry, it should have been "HOUR" not "HOURS":

 BETWEEN DATEADD(HOUR, -24, GETDATE())
0
 

Author Comment

by:suran78
ID: 13978616
Hours gave me error, so I tried hour and it worked. Thanks.  I will try hh too
0
 
LVL 11

Expert Comment

by:andrewbleakley
ID: 13981113
HOUR and hh are the same - completley identical.
0
 

Author Comment

by:suran78
ID: 13981401
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
 

Author Comment

by:suran78
ID: 14013825
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 520 total points
ID: 14013865
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
 
LVL 11

Assisted Solution

by:andrewbleakley
andrewbleakley earned 480 total points
ID: 14013917
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
 

Author Comment

by:suran78
ID: 14121815
Thanks a lot.  All queries worked out as I wanted.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question