Date "shift" - 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 "shift"

Author

Topic

miooim
Starting Member

3 Posts

Posted - 2012-08-12 : 10:36:55

Generally when picking data based on dates, SQL will return data from 00:00 to 24:00. The question is how to make SQL return data with a time shift, let's say from 7:00 to 7:00 in the morning?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-12 : 12:04:44

you mean timeslot as 19:00 to 07:00?

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

miooim
Starting Member

3 Posts

Posted - 2012-08-12 : 13:28:30

quote:
Originally posted by visakh16

you mean timeslot as 19:00 to 07:00?

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

No, I mean following case:
I have a table where I store data about device if it is good or bad, I also have a column with time, when this devices have Passed/Failed.

Now I want to count band and good devices from 1/1/2012 7:00:00 to 3/1/2012 7:00:00, and display daily ration of passed and failed devices.

What happens now is that I will get 3 days report from:
1/1/2012 7:00 to 1/1/2012 24:00
2/1/2012 0:00 to 2/1/2012 24:00
3/1/2012 0:00 to 3/1/2012 7:00

What I need is:
1/1/2012 7:00 to 2/1/2012 7:00
2/1/2012 7:00 to 3/1/2012 7/00

Only 2 days report!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-12 : 14:54:32

24:00 is not a valid time value. it whould be 00:00

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-12 : 21:28:45

In your group by clause, shift the time by 7. As an example, I am trying to count the devices for each date, but my dates are 24 hour periods starting at 7:00 AM instead of the conventional 24 hour period starting at midnight.

CREATE TABLE #tmp(datestamp DATETIME, devices INT);
INSERT INTO #tmp VALUES 
	('2012-08-12T05:00:00.000',50),
	('2012-08-12T08:00:00.000',10),
	('2012-08-13T02:00:00.000',41),
	('2012-08-14T17:11:00.000',41);
	
DECLARE @shiftStart INT = 7; -- Day starts at 7:00 AM	
SELECT
	CAST(DATEADD(hh,-@shiftStart,datestamp) AS DATE) AS NominalDate,
	SUM(devices)
FROM 
	#tmp
GROUP BY 
	CAST(DATEADD(hh,-@shiftStart,datestamp) AS DATE);
	
	
DROP TABLE #tmp;

If you are on SQL 2005 or earlier, the CAST to DATE won't work, , in that case, replace it with:

DATEADD(dd,DATEDIFF(dd,0,(DATEADD(hh,-@shiftStart,datestamp))),0)

miooim
Starting Member

3 Posts

Posted - 2012-08-21 : 10:24:35

It looks working thanks!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-21 : 11:11:13

You are very welcome!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources