Select statement help? DATEPART...

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

Select statement help? DATEPART...

Author

Topic

thommo41
Starting Member

6 Posts

Posted - 2009-11-10 : 06:16:13

Hi,

I've not used SQL queries before really, this has come about because a product we use for Web Filtering, has no facility in its reporting to exclude or include certain time of the day. One person on the Web Filter forums said I need to add this line in the select statement...

and DatePart(hh,(DATEADD(hour,DATEDIFF(hour,getUTCDATE(),getDATE()),[DomainLog].[StartTime]))) between '8' and '18'

This does work. No problems. But, I need to do something more than simply put in 2 time ranges (8 and 18 in the above example)

- Firstly, I need the ability to specify and exact HH:MM time in the between statement.
- Secondly, I need to specify 2 "between" ranges to include in the query results.

I basically need to specify "BETWEEN 08:30 and 12:30 OR BETWEEN 13:15 AND 17:00"

Any help very much appreciated. Thanks in advance,
Alan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-10 : 07:23:48

where
(
[DomainLog].[StartTime]>=dateadd(day,datediff(day,0,getdate()),'8:30') and
[DomainLog].[StartTime]<=dateadd(day,datediff(day,0,getdate()),'12:30')
)
or
(
[DomainLog].[StartTime]>=dateadd(day,datediff(day,0,getdate()),'13:15') and
[DomainLog].[StartTime]<=dateadd(day,datediff(day,0,getdate()),'17:00') and
)

Madhivanan

Failing to plan is Planning to fail

thommo41
Starting Member

6 Posts

Posted - 2009-11-10 : 08:48:24

Hi Madhivanan, that initially looks like it worked perfect, many thanks!
I dont suppose you could explain the statement to me could you? No worries if not, It's just that I'd like to understand how and why that works!
Oh the joys of being simple...
Cheers, Alan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-10 : 08:50:39

Hint

dateadd(day,datediff(day,0,getdate()),'8:30') will add the time 8:30 hours to the starting time of today

Madhivanan

Failing to plan is Planning to fail

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-11-10 : 09:01:21

quote:
Originally posted by thommo41

Hi Madhivanan, that initially looks like it worked perfect, many thanks!
I dont suppose you could explain the statement to me could you? No worries if not, It's just that I'd like to understand how and why that works!
Oh the joys of being simple...
Cheers, Alan

best way is to tear apart the query, see what each function returns

select getdate()

select datediff(day,0,getdate())

select dateadd(day,datediff(day,0,getdate()),'8:30')

thommo41
Starting Member

6 Posts

Posted - 2009-11-10 : 09:56:12

I still don't see how it works, I was expecting 'time' to be specified somewhere, all I see is 'date'?

thommo41
Starting Member

6 Posts

Posted - 2009-11-10 : 10:08:15

Ah, I've just seen the problem.
I was testing this for todays data only, but when I test for a whole months worth of data, because of the syntax you gave me, it only displays todays data.
I didn't really point that out in my initial question - I need to look at a given period of data (month, week, 3 months worth etc) and show only the results that fall within those times, regardless of the day.
Back to the drawing board???
Thanks
Alan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-11 : 00:20:11

Try this

where
(
[DomainLog].[StartTime]>=dateadd(day,datediff(day,0,[DomainLog].[StartTime]),'8:30') and
[DomainLog].[StartTime]<=dateadd(day,datediff(day,0,[DomainLog].[StartTime]),'12:30')
)
or
(
[DomainLog].[StartTime]>=dateadd(day,datediff(day,0,[DomainLog].[StartTime]),'13:15') and
[DomainLog].[StartTime]<=dateadd(day,datediff(day,0,[DomainLog].[StartTime]),'17:00') and
)

Madhivanan

Failing to plan is Planning to fail

thommo41
Starting Member

6 Posts

Posted - 2009-11-11 : 11:21:52

Thanks for that Madhivanan, but I am now finding an issue running the reports (the WebFilter has its own reporter!)

The section of the query Im adding it to is as follows, can you tell me where I should add your code?...

select
[SessionLog].[UserGuid],
[DomainLog].[ServerName],
[DomainLog].[ServiceType],
[DomainFileLog].[StartTime],
[DomainFileLog].[UrlObject],
[DomainFileLog].[ActualLengthBytes],
[DomainFileLog].[ContentType],
[DomainFileLog].[BinaryType],
[DomainFileLog].[FileName]
from
[DomainLog]
inner join [SessionLog] on [DomainLog].[SessionLogStartTime] = [SessionLog].[StartTime] and
[DomainLog].[SessionLogId] = [SessionLog].[Id]
inner join [DomainFileLog] on [DomainFileLog].[DomainLogStartTime] = [DomainLog].[StartTime] and
[DomainFileLog].[DomainLogId] = [DomainLog].[Id]
where
[DomainLog].[StartTime] between @StartDate and @EndDate
and [ServerName] like @SitePattern
and [ServiceType] like @Protocol

Also, did your code have a typo in the form off too many "and"'s?

Thanks once more. Alan

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-11 : 12:01:54

Yes the last "and" should be deleted.
To answer where to add madhi's code:
Madhi has used GETDATE() in his example but in your code appears suddenly @StartDate and @EndDate.
What should be used now?
What are example values of these two variables and how would you like to combine them with time parts like 08:30 and so on?

No, you're never too old to Yak'n'Roll if you're too young to die.

thommo41
Starting Member

6 Posts

Posted - 2009-11-12 : 04:25:37

I'm editing a stored procedure, which feeds the Web-based reports. At the top of the stored procedure is...

@StartDate datetime,
@EndDate datetime,

Later there is...

declare @TimeZoneId int
execute @TimeZoneId = [TimeZoneAdjustReportDates] @TimeZone, @StartDate output, @EndDate output

These two values are passed via the web front end for the reports I believe, as you can report on pre-set periods (this week, this month, last month etc) or enter two dates (from and to). I am not sure of the exact format of the data being passed in though as they come from this reporting web front end.

Thanks, Alan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources