DATEPART with timezoneoffsert - 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

SQL Server 2008 Forums

Transact-SQL (2008)

DATEPART with timezoneoffsert

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-06-02 : 03:23:50

Hi,

I have an sql statement that need to use timezoneoffset.
but running the query i got an error. My requirements is tomidentify the data if it is in Shift1 or Shift2. 1st shift is between 6:ooAM to 16:00PM while 2nd shit is between 22:00PM to 6:00AM of the next day. THank you in advance.

Here is the DDL and SQL Codes:

Create table #sample
(ID nvarchar(5), DateandTime datetime, qty int)
Insert into #sample(ID, dateandtime,qty) values('XXXX','2014-05-30 00:33:18.000',1)
Insert into #sample(ID, dateandtime,qty) values('TTTT','2014-05-30 05:20:57.000',1)
Insert into #sample(ID, dateandtime,qty) values('XXXX','2014-05-29 23:09:33.000',1)
Insert into #sample(ID, dateandtime,qty) values('XXXX','2014-05-30 03:45:08.000',1)
Insert into #sample(ID, dateandtime,qty) values('TTTT','2014-05-30 04:53:55.000',1)
Insert into #sample(ID, dateandtime,qty) values('XXXX','2014-05-30 06:00:06.000',1)
Insert into #sample(ID, dateandtime,qty) values('XXXX','2014-05-30 06:15:41.000',1)
Insert into #sample(ID, dateandtime,qty) values('TTTT','2014-05-30 06:07:08.000',1)

Declare @timezoneOffset int, @D1 datetime, @D2 datetime, @Stime nvarchar(8), @ETime nvarchar(8),@FromDatetime datetime, @ToDatetime datetime
Set @timezoneOffset=8
Set @D1='2014-05-30'
Set @D2='2014-05-31'


select *, 
CASE when DATEPART(hour,DateandTime) between 6 and 16 then 1 else 2 end as shiftID, --THis is working
CASE WHEN DATEPART(HOUR,convert(int,@timezoneOffset), DateandTime) between 6 and 16 then 1 --not working (need this portion with timeezone)
     WHEN DATEPART(HOUR,convert(int,@timezoneOffset), DateandTime) between 22 and 6 then 2
ELSE 0 end as shiftID2 --got an error 
from @sample
WHERE DATEADD(HOUR,convert(int,@timezoneOffset), dateandtime) BETWEEN (@D1) AND  (@D2)

Result gathered using the query: all those with #2 should be the 1st shift while with #1 are 2ns shift.

DatandTime w/out timezone-----with timezone--need this to validate the shift
2014-05-30 00:33:18.000---2014-05-30 08:33:18.000-- 2
2014-05-30 05:20:57.000---2014-05-30 13:20:57.000-- 2
2014-05-29 23:09:33.000---2014-05-30 07:09:33.000-- 2
2014-05-30 03:45:08.000---2014-05-30 11:45:08.000-- 2
2014-05-30 04:53:55.000---2014-05-30 12:53:55.000-- 2
2014-05-30 06:00:06.000---2014-05-30 14:00:06.000-- 1
2014-05-30 06:15:41.000---2014-05-30 14:15:41.000-- 1
2014-05-30 06:07:08.000---2014-05-30 14:07:08.000-- 1

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-02 : 11:41:40

There are a couple different ways to do that math. You can either add the offset to the HOUR or you can add the offset to the date and get the HOUR. I suggest using the latter so you don't end up with an invalid hour (20 + 8 for example):

Declare @timezoneOffset int, @D1 datetime, @D2 datetime, @Stime nvarchar(8), @ETime nvarchar(8),@FromDatetime datetime, @ToDatetime datetime
Set @timezoneOffset=8
Set @D1='2014-05-30'
Set @D2='2014-05-31'


select *, 
CASE WHEN DATEPART(hour,DateandTime) between 6 and 16 then 1 else 2 end as shiftID, --THis is working
CASE WHEN DATEPART(HOUR, DATEADD(HOUR, @TimeZoneOffset, DateandTime)) between 6 and 16 then 1 --not working (need this portion with timeezone)
     WHEN DATEPART(HOUR, DATEADD(HOUR, @TimeZoneOffset, DateandTime)) between 22 and 6 then 2
ELSE 0 end as shiftID2 --got an error 
from #sample
WHERE DATEADD(HOUR, @TimeZoneOffset, DateandTime) BETWEEN @D1 AND @D2

I'm not sure that gets the results you want, but it shows how to apply the offset.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-06-02 : 20:42:44

Thank you Lampley.
BTw, I have an issue on this code "between 6 and 16 then 1"
I wanted to get the exact time 6:00AM and "16:00" if more than 16:00 it would fall in 2 or (2nd shift).

Trying this code is not working, any idea.

CASE WHEN DATEPART(HOUR, DATEADD(HOUR, @TimeZoneOffset, DateandTime)) between '6:00' and '16:00' then 1 --not working (need this portion with timeezone)
WHEN DATEPART(HOUR, DATEADD(HOUR, @TimeZoneOffset, DateandTime)) between '22:00' and '6:00' then 2

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-06-02 : 21:03:34

Hi Lampley.

Can i have your idea or inputs. this is related on this thread. i have a visual dashboard requirements.
On the dashboard it shows the production output for the day. it has 2 shifts.
the first shift start 6:00am TO 16:00pm while the second shift start at 22:00pm to 6:00am of next day.

I'll give you an example:Today is June 3 2014 8:58AM
I will capture all the data that start at 6:00AM to 16:00PM --this is for 1st shift
while if the time falls at 22:00pm to 6:00AM(next day) --2nd shift

my question is what should i do on my WHERE statement, the value of @D1 and @D2? This should be '2014-06-03' and '2014-06-04'
The Visual Dashboard will displayed both 1st shift and 2nd shift.
if 1st shift process the second shift has no data to be displayed but if the time falls on the 2nd shift both 1st and 2nd shift will be displayed.

--My Query
select *,
CASE when DATEPART(hour,DateandTime) between 6 and 16 then 1 else 2 end as shiftID,
CASE WHEN DATEPART(HOUR,convert(int,@timezoneOffset), DateandTime) between 6 and 16 then 1
WHEN DATEPART(HOUR,convert(int,@timezoneOffset), DateandTime) between 22 and 6 then 2
ELSE 0 end as shiftID2
from @sample
WHERE DATEADD(HOUR,convert(int,@timezoneOffset), dateandtime) BETWEEN (@D1) AND (@D2)

THank you.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-03 : 11:12:04

quote:
Originally posted by Villanuev

Thank you Lampley.
BTw, I have an issue on this code "between 6 and 16 then 1"
I wanted to get the exact time 6:00AM and "16:00" if more than 16:00 it would fall in 2 or (2nd shift).

Trying this code is not working, any idea.

CASE WHEN DATEPART(HOUR, DATEADD(HOUR, @TimeZoneOffset, DateandTime)) between '6:00' and '16:00' then 1 --not working (need this portion with timeezone)
WHEN DATEPART(HOUR, DATEADD(HOUR, @TimeZoneOffset, DateandTime)) between '22:00' and '6:00' then 2

DARTPART HOUR returns an INT. '6:00' is a string. You'll need to use the proper data type for comparison (i.e. 6) or you'll need to cast one of the values.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-06-03 : 21:12:26

Hi Lampley,

I tried this codes and it's working.

CASE WHEN CONVERT(VARCHAR(5),DateandTime,108)  BETWEEN '06:00' AND '16:00' THEN 1 ELSE 2 END AS SHIFTID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources