Obtain information from previous hour

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Obtain information from previous hour

Author

Topic

Dmh188
Starting Member

37 Posts

Posted - 2011-01-12 : 12:39:43

Hi, i am trying to change a sql to display the last hour of shipped orders. but in the format of between 9 and 10. or 1 and 2.. not if i run the query at 12:40 that it will display date between 12:40 and 11:40. So if i run it at 2:15 then it will display the total number of orders shipped between 1 and 2.This is the code i am working off of that will show total for the day so far:

set nocount on
select count(*) from customerordermaster with (nolock)
where shipdate >=
(cast(datepart (mm, getdate()) as nvarchar) +'/'+
cast (datepart( dd, getdate()) as nvarchar) +'/'+
cast(datepart(yyyy, getdate()) as nvarchar))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 12:42:18

where shipdate >=dateadd(hh,-1,getdate()) and shipdate <= getdate()

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dmh188
Starting Member

37 Posts

Posted - 2011-01-12 : 12:47:41

Visakh16

Ive tried that but say i just ran it now 12:45 (my time) it will display what has shipped between 11:45 and 12:45. I need it so if i ran it now (12:45), that it will display the result from time of 11-12

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 12:48:49

where shipdate >=dateadd(hh,datediff(hh,0,getdate())-1,0) and shipdate <= dateadd(hh,datediff(hh,0,getdate())1,0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dmh188
Starting Member

37 Posts

Posted - 2011-01-12 : 12:58:31

Visakh16

i really appreciate the help on this. but i received a incorrect syntax error
set nocount on
select count(*) from customerordermaster with (nolock)
where shipdate >=dateadd(hh,datediff(hh,0,getdate())-1,0)
and shipdate <= dateadd(hh,datediff(hh,0,getdate())1,0)

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '1'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 13:13:34

oops..a typo

where shipdate >=dateadd(hh,datediff(hh,0,getdate())-1,0) and shipdate <= dateadd(hh,datediff(hh,0,getdate()),0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dmh188
Starting Member

37 Posts

Posted - 2011-01-12 : 13:19:57

Visakh16

you are the man! thank you so much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 13:20:51

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources