DateAdd function in Stored Procedure

I need to be able to store a value in a datefield that is 8 hours greater
than another datefield (SchedOpen) which the user updates. What happens is
the user enters local time and I need to also store that time as GMT. I trie
d
this as an alias in a stored procedure: "DATEADD(hh, 8, SchedOpen)" and it
works fine until there is a time when adding 8 hours goes beyond midnight. I
then get a date added to the field which is the year 1900. I suspect that
since the field only contains time the date is defaulted to 1900. There is a
date field in each row with the current date so I need to somehow add that t
o
the GMT field before adding the 8 hours but I just can't figure out how to
do this. Any Ideas? Thanks.Why you need to store it.
you still can access the same field and add the extra 8 hours.
I don't see a problem in adding the hours.
your problem is how to format the date field.
I tried this one
select getdate(),dateadd(hh,8,getdate())
the result was:-
2005-12-07 16:55:13.197 2005-12-08 00:55:13.197
So you can see that I passed the midnight and the and became in a new day.
I still can access the time or the date portions by formating the date.
like in this example:-
select convert(char(8),getdate(),8),convert(cha
r(8),dateadd(hh,8,getdate()),
8)
the result was
17:06:27 01:06:27
I got what I need from the date and i added the hours too.
So all about formating the date.
Thanks,
Nader Shahin
"AkAlan" wrote:

> I need to be able to store a value in a datefield that is 8 hours greater
> than another datefield (SchedOpen) which the user updates. What happens is
> the user enters local time and I need to also store that time as GMT. I tr
ied
> this as an alias in a stored procedure: "DATEADD(hh, 8, SchedOpen)" and i
t
> works fine until there is a time when adding 8 hours goes beyond midnight.
I
> then get a date added to the field which is the year 1900. I suspect that
> since the field only contains time the date is defaulted to 1900. There is
a
> date field in each row with the current date so I need to somehow add that
to
> the GMT field before adding the 8 hours but I just can't figure out how t
o
> do this. Any Ideas? Thanks.
>|||AkAlan (AkAlan@.discussions.microsoft.com) writes:
> I need to be able to store a value in a datefield that is 8 hours
> greater than another datefield (SchedOpen) which the user updates. What
> happens is the user enters local time and I need to also store that time
> as GMT. I tried this as an alias in a stored procedure: "DATEADD(hh, 8,
> SchedOpen)" and it works fine until there is a time when adding 8 hours
> goes beyond midnight. I then get a date added to the field which is the
> year 1900. I suspect that since the field only contains time the date is
> defaulted to 1900. There is a date field in each row with the current
> date so I need to somehow add that to the GMT field before adding the 8
> hours but I just can't figure out how to do this. Any Ideas? Thanks.
There is no time data type in SQL Server (not even in SQL 2005). When you
do not specify a date, the default date is 1900-01-01.
If you want to keep all times within the same date, you will have to
something like:
SELECT convert(datetime, convert(char(8), dateadd(hh, 8, SchedOpen), 108))
or
SELECT dateadd(hh, CASE WHEN SchedOpen < '16:00:00' THEN 8 ELSE -16 END,
SchedOpen)
By the way, in most interfaces you will have to explicitly remove that
date part of 1900-01-01. You may be looking in Enterprise Manager, but
EM is not an application.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks, I did figure out how to add the date field with the time field like
this:
DATEADD(hh, 9, dbo.tblMissions.DateFlown + dbo.tblMtr.StartTime)
This allows me to format the field and present it the way the customer
wants. My real problem is storing the data without have to make design
changes every time we change daylight savings time. I'm using MS Access as
a
front end. Here is the situation...I have a form which the user enters a
local date and time value. I need the report to display the local values as
well as the corresponding GMT based on the local entry. I need to store the
GMT value with each row since I never know on which day the data is being
viewed. I think I'll have to ceate a table and add the daylight savings days
(beging and end) then check the entry date to the table and update the field
with code on the after update event of the local time field. Thanks for your
reply.
"Nader Shahin" wrote:
> Why you need to store it.
> you still can access the same field and add the extra 8 hours.
> I don't see a problem in adding the hours.
> your problem is how to format the date field.
> I tried this one
> select getdate(),dateadd(hh,8,getdate())
> the result was:-
> 2005-12-07 16:55:13.197 2005-12-08 00:55:13.197
> So you can see that I passed the midnight and the and became in a new day.
> I still can access the time or the date portions by formating the date.
> like in this example:-
> select convert(char(8),getdate(),8),convert(cha
r(8),dateadd(hh,8,getdate()
),8)
> the result was
> 17:06:27 01:06:27
> I got what I need from the date and i added the hours too.
> So all about formating the date.
> Thanks,
> Nader Shahin
>
> "AkAlan" wrote:
>