December 23, 2004 at 6:40 am
I have a web based time clock program I'm creating. I want to store the time of the in and out punches of the employees in a format that is determined based on the location the person is punching in and out at. We have locations in three different time zones.
Thanks, John
John C Marx
December 23, 2004 at 7:21 am
If you want to store time accurate to the minute then use SMALLDATETIME otherwise use DATETIME.
If the various locations are in the same country as the server then use the server time but make sure that the server time is accurate, otherwise use the time from the punching device. Again make sure the punching device is accurate.
The need to store in GMT time depends on your requirements. If you have some convoluted overtime system then I would store the punching date time and the timezone of the punch. This will let you convert the datetime to any format you need.
December 27, 2004 at 2:15 pm
I agree on smalldatetime for this.
And I would use GMT in your tables. Does not matter what your server time or timezone is, as long as it is accurate. You can use getutcdate() to get current GMT datetime at time of insert/update.
Only remaining problem is how to convert from GMT to local time when displaying it. For this you need to know GMT offset required to convert to a location's local time.
Very simple design is add GMTOffset and UsesDST columns to your location table, and manually populate these with correct info. Example of this is below.
More general-purpose design can be based on zipcode-based timezone data. Purchase such a table, then map from location zipcode to this new table in your UDF. UDF params remain the same. Purchased data relieves you of having to maintain large qty of varied GMT and DST information. I have purchased such data cheaply before, not sure if it can be found for free or not. You could also build & populate your own table that is granular to the state/province, but this will not account for within-state DST exceptions, and there are some.
All these simple approaches are only good for US (and maybe Canada)--most countries have different rules for when and if DST applies. If you need to address more countries, the algorithm in the UDF needs to grow considerably. They also only applies to recent and future data; US DST criteria have changed a couple times in my lifetime.
PS - In general I am opposed to UDFs in SQL2K just because they perform so poorly. But here I do not see good alternative.
create table Location
(
ID int not null identity(1, 1) primary key clustered,
[Name] varchar(30) not null,
State char(2) not null,
GmtOffset smallint not null,
UsesDST tinyint not null
)
go
insert Location([Name], State, GmtOffset, UsesDST) values ('Loc1', 'FL', -5, 1)
insert Location([Name], State, GmtOffset, UsesDST) values ('Loc1', 'KS', -6, 1)
insert Location([Name], State, GmtOffset, UsesDST) values ('Loc1', 'AZ', -7, 0)
insert Location([Name], State, GmtOffset, UsesDST) values ('Loc1', 'CA', -8, 1)
go
create function dbo.fn_ConvertToLocalTime(@ID int, @dtGMT smalldatetime)
returns smalldatetime
as
begin
declare @GmtOffset smallint,
@UsesDST tinyint,
@dtApr01 datetime,
@dtOct25 datetime,
@dtFirstAprSun datetime,
@dtLastOctSun datetime
--lookup info from location table
select @GmtOffset = GmtOffset,
@UsesDST = UsesDST
from Location
where ID = @ID
--at this point either need to abort or set default value if above lookup fails
if (@@rowcount = 0)
return null
--determine 1st Sunday in April and last Sunday in Oct for the specified year
select @dtApr01 = '04/01/' + datename(year, @dtGMT) + ' 02:00',
@dtOct25 = '10/25/' + datename(year, @dtGMT) + ' 02:00'
select @dtFirstAprSun = dateadd(dd, (8 - datepart(weekday, @dtApr01)) % 7, @dtApr01),
@dtLastOctSun = dateadd(dd, (8 - datepart(weekday, @dtOct25)) % 7, @dtOct25)
--if summertime & location uses GMT, add one
if (@dtGMT between @dtFirstAprSun and @dtLastOctSun)
set @GmtOffset = @GmtOffset + @UsesDST
--now we have GMT offset, add that & return local time
return dateadd(hh, @GmtOffset, @dtGMT)
end
go
--show location GMT data
select * from Location
go
--test DST
declare @GMT datetime
set @GMT = '2004-07-04 11:00'
select convert(char(16), @GMT, 120) as "GMT",
convert(char(16), dbo.fn_ConvertToLocalTime(1, @GMT), 120) as "Local(1)",
convert(char(16), dbo.fn_ConvertToLocalTime(2, @GMT), 120) as "Local(2)",
convert(char(16), dbo.fn_ConvertToLocalTime(3, @GMT), 120) as "Local(3)",
convert(char(16), dbo.fn_ConvertToLocalTime(4, @GMT), 120) as "Local(4)"
go
--test normal time
declare @GMT datetime
set @GMT = '2004-12-04 11:00'
select convert(char(16), @GMT, 120) as "GMT",
convert(char(16), dbo.fn_ConvertToLocalTime(1, @GMT), 120) as "Local(1)",
convert(char(16), dbo.fn_ConvertToLocalTime(2, @GMT), 120) as "Local(2)",
convert(char(16), dbo.fn_ConvertToLocalTime(3, @GMT), 120) as "Local(3)",
convert(char(16), dbo.fn_ConvertToLocalTime(4, @GMT), 120) as "Local(4)"
go
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply