SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Datetime
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shiyam198
Yak Posting Veteran

94 Posts

Posted - 01/23/2013 :  13:32:02  Show Profile  Reply with Quote
Greetings everyone:

I use a script to produce reports from performance logs our application logs. As you can see here the logs are logged in SQL server's default format and then I am converting it with 111, so I can group by that, so I can produce results for each day. (e.g. Group by on 2013-01-23)

Now I need to produce results for each hour. So I want to be able to get the datetime with the hour. e.g. 2013-01-23 12, so I can do a group by. I am not able to find the right number for it. Is this impossible to do?

Thanks for your time !!



select 
	LocalDate [date],
	count(*) [requests],
	avg(web_request_duration) [avg response time],
	min(web_request_duration) [min response time],
	max(web_request_duration) [max response time]
from
(
	select 
		*, 
		convert(varchar, LocalTime, 111) LocalDate
	from
	[WebRequest] MyApp
	where 
		MyApp.message = 'MultiQuery Web Request'
		MyApp.firmid = 16
		
) 
WebRequests
group by LocalDate
order by LocalDate desc


James K
Flowing Fount of Yak Knowledge

3699 Posts

Posted - 01/23/2013 :  13:37:21  Show Profile  Reply with Quote
You could use another format and trim-off everything to the right of the hour part - for example, CONVERT(VARCHAR(13),GETDATE(),120) will do that. I like grouping by the hour also - like shown below:
select 
	LocalDate [date],
	LocalHour,
	count(*) [requests],
	avg(web_request_duration) [avg response time],
	min(web_request_duration) [min response time],
	max(web_request_duration) [max response time]
from
(
	select 
		*, 
		convert(varchar, LocalTime, 111) LocalDate,
		DATEPART(hour,LocalTime) AS LocalHour
	from
	[WebRequest] MyApp
	where 
		MyApp.message = 'MultiQuery Web Request' AND 
		MyApp.firmid = 16
		
) 
WebRequests
group by LocalDate, LocalHour
order by LocalDate desc
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/23/2013 :  13:54:07  Show Profile  Reply with Quote
why do you need to convert to varchar for getting hour? you just need to use logic like this


DATEADD(hh,DATEDIFF(hh,0,LocalTime),0)


see

http://visakhm.blogspot.in/2012/07/generate-datetime-values-from-integers.html

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

Go to Top of Page

shiyam198
Yak Posting Veteran

94 Posts

Posted - 01/23/2013 :  13:58:41  Show Profile  Reply with Quote
THANKS A MILLION GUYS !!
I will give these a whirl.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/23/2013 :  14:02:34  Show Profile  Reply with Quote

select 
		DATEADD(hh,DATEDIFF(hh,0,LocalTime),0) AS LocalTime,
                count(*) [requests],
	        avg(web_request_duration) [avg response time],
	        min(web_request_duration) [min response time],
	        max(web_request_duration) [max response time]
	from
	[WebRequest] MyApp
	where 
		MyApp.message = 'MultiQuery Web Request' AND 
		MyApp.firmid = 16
        group by DATEADD(hh,DATEDIFF(hh,0,LocalTime),0)


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

Go to Top of Page

shiyam198
Yak Posting Veteran

94 Posts

Posted - 01/23/2013 :  14:41:28  Show Profile  Reply with Quote
Thanks again buddy. Much appreciated.

quote:
Originally posted by James K

You could use another format and trim-off everything to the right of the hour part - for example, CONVERT(VARCHAR(13),GETDATE(),120) will do that. I like grouping by the hour also - like shown below:
select 
	LocalDate [date],
	LocalHour,
	count(*) [requests],
	avg(web_request_duration) [avg response time],
	min(web_request_duration) [min response time],
	max(web_request_duration) [max response time]
from
(
	select 
		*, 
		convert(varchar, LocalTime, 111) LocalDate,
		DATEPART(hour,LocalTime) AS LocalHour
	from
	[WebRequest] MyApp
	where 
		MyApp.message = 'MultiQuery Web Request' AND 
		MyApp.firmid = 16
		
) 
WebRequests
group by LocalDate, LocalHour
order by LocalDate desc


Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3699 Posts

Posted - 01/23/2013 :  15:32:25  Show Profile  Reply with Quote
You are very welcome - glad to be of help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000