Solved

MSSQL - Select data from now to yesterday at a specific hour

Posted on 2014-02-08
3
834 Views
Last Modified: 2014-02-08
I have a table (SQLJobStatus) with a column (Job_Date) that has date/time records.  (2014-02-07 23:30:00.000)

I am trying to get all records from last night at 11:30pm  (23:30:00) until now.

select * from SQLJobStatus where ((Job_Date >= (DATEADD(d,-1,GETDATE()))) and ((convert(varchar, job_date, 108) >= '23:30:00')))

I tried this query however, I only get yesterday jobs from 23:30.  The >= on selecting the date is only giving me yesterday.  I see that is caused by the time portion being selected as > 23:30 which would only select the jobs on the next date that were greater than this.

Not sure how to write the correct syntax.

Would appreciate help writing a clean query to give me all records as needed.

Thanks
0
Comment
Question by:rsmith1969
  • 2
3 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39844737
Something like this perhaps:

SELECT  *
FROM    SQLJobStatus
WHERE   Job_Date >= DATEADD(minute, 1410, DATEADD(DAY, -1, DATEDIFF(DAY, 0, GETDATE())))

Open in new window

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 100 total points
ID: 39844740
This might be a tad clearer:
SELECT  *
FROM    SQLJobStatus
WHERE   Job_Date >= DATEADD(minute, 1410, DATEDIFF(day, 1, GETDATE()))

Open in new window

0
 

Author Comment

by:rsmith1969
ID: 39844741
Thanks...  For some reason I couldn't wrap by head around this... I kept running into a roadblock.  Looks like I need to research datedff.

Now any time I run my program it will give me all of the jobs from last night instead of the last 6 hours.

Thanks again....
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now