• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1074
  • Last Modified:

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

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
rsmith1969
Asked:
rsmith1969
  • 2
1 Solution
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
This might be a tad clearer:
SELECT  *
FROM    SQLJobStatus
WHERE   Job_Date >= DATEADD(minute, 1410, DATEDIFF(day, 1, GETDATE()))

Open in new window

0
 
rsmith1969Author Commented:
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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now