Date time query - SQL Server Forums

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

Date time query

Author

Topic

cocofuc25
Starting Member

14 Posts

Posted - 2014-06-09 : 04:55:53

hi guys

i have following sample query

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)
SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))

select  Datetime 
from InterfaceTraffic
where (DateTime between @StartDate and @EndDate)
and
(DATEPART(weekday, DateTime) <> 1) AND 
(DATEPART(weekday, DateTime) <> 7) and
(Convert(Char,DateTime,108) >= '07:00') and 
 (Convert(Char,DateTime,108) <= '17:00')
 
 
group by DateTime

those query would get me a return of value inside of datetime coloumn, which is, date of (assume i run the query today) 2014-05-12 07:00:00 until 2014-05-30 16:00:00, my question is where is the date of 2014-05-01 07:00:00 until 2014-05-11 16:00:00 goes ??

oya the wierd things if i commented the some of query like following

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)
SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))

select  Datetime 
from InterfaceTraffic
where (DateTime between @StartDate and @EndDate)
and
(DATEPART(weekday, DateTime) <> 1) AND 
(DATEPART(weekday, DateTime) <> 7)/* and
(Convert(Char,DateTime,108) >= '07:00') and 
 (Convert(Char,DateTime,108) <= '17:00')*/
 
 
group by DateTime

it would get me a return of all dates of last month with all hours

and what i want is to have a return of all dates of last month but with specific hour only (the hour between 7 am until 5 pm)

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2014-06-09 : 09:04:47

Instead of using getdate() when you set you Start and End dates, give them a hard coded value (i.e. '2014-05-01 07:00:00' and '2014-05-30 16:00:00').
Then you can get exactly what you are looking for.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-09 : 09:05:22

Please post a CREATE TABLE script and an INSERT INTO script to build and populate some sample data. Then post the expected results from a successful query

cocofuc25
Starting Member

14 Posts

Posted - 2014-06-18 : 07:26:58

quote:
Originally posted by DonAtWork

Instead of using getdate() when you set you Start and End dates, give them a hard coded value (i.e. '2014-05-01 07:00:00' and '2014-05-30 16:00:00').
Then you can get exactly what you are looking for.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

thanks for the reply

so by using your suggestion, i need to change the hard coded everytime i want to pull out a report, for instance, if today i pull out a report so the hard coded would be '2014-05-01 07:00:00' and '2014-05-30 16:00:00', but if pull out the report on let say, august, i need to change the hard coded value to '2014-07-01 07:00:00' and '2014-07-30 16:00:00', right ?

if that so, is there any query that could give me a report of all days on previous month without need to alter any line on the query ?
but still using "office hour time" to filter the result

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-06-20 : 08:25:42

Try this


DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

SET @StartDate = dateadd(month,datediff(month,0,getdate())-1,0)+'07:00:00'
SET @EndDate = dateadd(month,datediff(month,0,getdate()),0)-1+'17:00:00'
select  Datetime 
from InterfaceTraffic
where DateTime >= @StartDate and DateTime <=@EndDate

Also refer this post for more examples
http://beyondrelational.com/modules/2/blogs/70/posts/10899/understanding-datetime-column-part-iii.aspx

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-21 : 05:10:23

[code]DECLARE @StartDate DATETIME,
@EndDate DATETIME;

SELECT @StartDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101'),
@EndDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101');

SELECT [DateTime]
FROM dbo.InterfaceTraffic
WHERE [DateTime] >= @StartDate
AND [DateTime] < @EndDate
AND DATEDIFF(DAY, '19000101', [DateTime]) % 7 NOT IN (5, 6)
AND DATEPART(HOUR, [DateTime]) >= 7
AND DATEPART(HOUR, [DateTime]) < 17;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources