Adding times together

Hi guys,

I have a field in my DB called EventDate as a DateTime field,
therefore it holds both the date and time together like this:
'2004-10-14 08:42:57.000'.

I need to add together all the times in this column for a particular
date range (BETWEEN).

Any suggestions will be great.

Thanks
Sunny:)Sunny K (sunstarwu@.yahoo.com) writes:
> I have a field in my DB called EventDate as a DateTime field,
> therefore it holds both the date and time together like this:
> '2004-10-14 08:42:57.000'.
> I need to add together all the times in this column for a particular
> date range (BETWEEN).

If I take you by the word, it sounds like the answer is:

SELECT SUM(datefiff(ss, convert(char(8), EventDate, 112), EventDate)
FROM tbl
WHERE EventDate BETWEEN ... AND ...

But it looks a little funny.

A common advice for this type of query is that you post

o CREATE TABLE statement for your table.
o INSERT statements with sample data.
o The desired result, given the sample data.

This make it easy to cut and paste and compose a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You certainly can use BETWEEN with the DATETIME datatype but if you are
querying values with times other than midnight it's often more convenient to
use use >= and < instead of BETWEEN. For example

This:

SELECT *
FROM YourTable
WHERE eventdate >= '20041014'
AND eventdate < '20041015'

Is equivalent to this:

SELECT *
FROM YourTable
WHERE eventdate
BETWEEN '2004-10-14T00:00:00.000'
AND '2004-10-14T23:59:59.997'

Hope that answers your question.

--
David Portas
SQL Server MVP
--|||> I need to add together all the times in this column

I missed that bit from my first post - maybe because I've no idea what it
means! Just what would you expect to be the result of, for example
'2004-10-14 08:42:57.000' + '2004-12-31 00:00:00.000'? Could you explain how
you want to add up a DATETIME?

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<xoudnd6FteLz-BTcRVn-1A@.giganews.com>...
> > I need to add together all the times in this column
> I missed that bit from my first post - maybe because I've no idea what it
> means! Just what would you expect to be the result of, for example
> '2004-10-14 08:42:57.000' + '2004-12-31 00:00:00.000'? Could you explain how
> you want to add up a DATETIME?

Hi Dave

Thats for the reply, and admitly i was very vague in what i meant to
say. From your above example the time result of the two times would
give me 08:42:57.000, as the time added was 00:00:00.000.

Maybe this will help explain what i mean a bit better. Here is a few
typical lines from my table:

Name EventDate EventID
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
CTWIGG-MOBL 2004-11-03 15:46:11.000 6005

My 'exact' requirements are to SUM all the 6005 EventID times together
and SUM all the 6006 EventID times together then find the difference
between the two times. The dates in the column are of no use.

Ive been banging my head over how to do this for a few days now. Any
suggestions?|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<xoudnd6FteLz-BTcRVn-1A@.giganews.com>...
> > I need to add together all the times in this column
> I missed that bit from my first post - maybe because I've no idea what it
> means! Just what would you expect to be the result of, for example
> '2004-10-14 08:42:57.000' + '2004-12-31 00:00:00.000'? Could you explain how
> you want to add up a DATETIME?

Hi Dave

Thats for the reply, and admitly i was very vague in what i meant to
say. From your above example the time result of the two times would
give me 08:42:57.000, as the time added was 00:00:00.000.

Maybe this will help explain what i mean a bit better. Here is a few
typical lines from my table:

Name EventDate EventID
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
CTWIGG-MOBL 2004-11-03 15:46:11.000 6005

My 'exact' requirements are to SUM all the 6005 EventID times together
and SUM all the 6006 EventID times together then find the difference
between the two times. The dates in the column are of no use.

Ive been banging my head over how to do this for a few days now. Any
suggestions?|||Sunny K (sunstarwu@.yahoo.com) writes:
> Thats for the reply, and admitly i was very vague in what i meant to
> say. From your above example the time result of the two times would
> give me 08:42:57.000, as the time added was 00:00:00.000.
> Maybe this will help explain what i mean a bit better. Here is a few
> typical lines from my table:
> Name EventDate EventID
> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
> CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
> CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
> CTWIGG-MOBL 2004-11-03 15:46:11.000 6005
>
> My 'exact' requirements are to SUM all the 6005 EventID times together
> and SUM all the 6006 EventID times together then find the difference
> between the two times. The dates in the column are of no use.
> Ive been banging my head over how to do this for a few days now. Any
> suggestions?

I repeat from my previous post:

A common advice for this type of query is that you post

o CREATE TABLE statement for your table.
o INSERT statements with sample data.
o The desired result, given the sample data.

This make it easy to cut and paste and compose a tested solution.

In this case, the part with the desired result is very important,
because I am not sure what result you are looking for, and I don't
feel like guessing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9597F073739C9Yazorman@.127.0.0.1>...
> Sunny K (sunstarwu@.yahoo.com) writes:
> > Thats for the reply, and admitly i was very vague in what i meant to
> > say. From your above example the time result of the two times would
> > give me 08:42:57.000, as the time added was 00:00:00.000.
> > Maybe this will help explain what i mean a bit better. Here is a few
> > typical lines from my table:
> > Name EventDate EventID
> > _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> > CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
> > CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
> > CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
> > CTWIGG-MOBL 2004-11-03 15:46:11.000 6005
> > My 'exact' requirements are to SUM all the 6005 EventID times together
> > and SUM all the 6006 EventID times together then find the difference
> > between the two times. The dates in the column are of no use.
> > Ive been banging my head over how to do this for a few days now. Any
> > suggestions?
> I repeat from my previous post:
> A common advice for this type of query is that you post
> o CREATE TABLE statement for your table.
> o INSERT statements with sample data.
> o The desired result, given the sample data.
> This make it easy to cut and paste and compose a tested solution.
> In this case, the part with the desired result is very important,
> because I am not sure what result you are looking for, and I don't
> feel like guessing.

Hi,

Here is the script to create the table with some sample data:

CREATE TABLE [dbo].[tbltemp23] (
[Machine_Name] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[EventDate] [datetime] NOT NULL ,
[EventID] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:10:54.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:12:13.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:41:42.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:42:57.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:16:45.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:19:21.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:32:56.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:34:17.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:13:21.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:14:42.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:44:55.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:46:11.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:51:43.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:53:03.000',6005)

Now I need to work out the the total time of all the 6006 EventIDs
(the date is here is not needed) which should equal: 93:32:16 then the
the total time of all the 6005 EventIDs which equals: 93:42:44. Then
finally find the difference between the two times, which should equal:
00:10:28 in this case.

I hope this is enough information.

Thanks
Sunny|||Thanks for the DDL and data.

SQL Server doesn't have a timespan data type. The query below uses
1900-01-01 as the base date from which durations are calculated, ignoring
the date component of the table data. You can format the returned values
according to your reporting requirements.

SELECT
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6006) AS EventId6006Duration,
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6005) AS EventId6005Duration,
DATEADD(s,
DATEDIFF(s,
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6006),
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6005)),
'19000101'
) AS EventDurationDifference

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sunny K" <sunstarwu@.yahoo.com> wrote in message
news:1ecdad8f.0411080132.4d6627fe@.posting.google.c om...
> Erland Sommarskog <esquel@.sommarskog.se> wrote in message
> news:<Xns9597F073739C9Yazorman@.127.0.0.1>...
>> Sunny K (sunstarwu@.yahoo.com) writes:
>> > Thats for the reply, and admitly i was very vague in what i meant to
>> > say. From your above example the time result of the two times would
>> > give me 08:42:57.000, as the time added was 00:00:00.000.
>>> > Maybe this will help explain what i mean a bit better. Here is a few
>> > typical lines from my table:
>>> > Name EventDate EventID
>> > _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
>> > CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
>> > CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
>> > CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
>> > CTWIGG-MOBL 2004-11-03 15:46:11.000 6005
>>>> > My 'exact' requirements are to SUM all the 6005 EventID times together
>> > and SUM all the 6006 EventID times together then find the difference
>> > between the two times. The dates in the column are of no use.
>>> > Ive been banging my head over how to do this for a few days now. Any
>> > suggestions?
>>
>> I repeat from my previous post:
>>
>> A common advice for this type of query is that you post
>>
>> o CREATE TABLE statement for your table.
>> o INSERT statements with sample data.
>> o The desired result, given the sample data.
>>
>> This make it easy to cut and paste and compose a tested solution.
>>
>> In this case, the part with the desired result is very important,
>> because I am not sure what result you are looking for, and I don't
>> feel like guessing.
>
> Hi,
> Here is the script to create the table with some sample data:
>
> CREATE TABLE [dbo].[tbltemp23] (
> [Machine_Name] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [EventDate] [datetime] NOT NULL ,
> [EventID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:10:54.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:12:13.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:41:42.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:42:57.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:16:45.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:19:21.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:32:56.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:34:17.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:13:21.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:14:42.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:44:55.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:46:11.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:51:43.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:53:03.000',6005)
> Now I need to work out the the total time of all the 6006 EventIDs
> (the date is here is not needed) which should equal: 93:32:16 then the
> the total time of all the 6005 EventIDs which equals: 93:42:44. Then
> finally find the difference between the two times, which should equal:
> 00:10:28 in this case.
> I hope this is enough information.
> Thanks
> Sunny|||Sunny K (sunstarwu@.yahoo.com) writes:
> Now I need to work out the the total time of all the 6006 EventIDs
> (the date is here is not needed) which should equal: 93:32:16 then the
> the total time of all the 6005 EventIDs which equals: 93:42:44. Then
> finally find the difference between the two times, which should equal:
> 00:10:28 in this case.

To be honest, this still seem very strange to me. Sure, there is enough
information to write a solution, but somehow I wonder what is the real
problem.

Looking at your data, it seems that event 6006 means start and 6005
means end, and what you really are computing is the total duration of
all start-stop sequences. Given that, I wrote this query:

SELECT convert(char(8), dateadd(ss, SUM(diff), '19000101'), 108)
FROM (select diff = datediff(ss, a.EventDate,
(SELECT MIN(EventDate)
FROM tbltemp23 b
WHERE b.EventDate > a.EventDate
AND b.EventID = 6005))
FROM tbltemp23 a
WHERE a.EventID = 6006) AS c

Of course, this query breaks down if the 6006 and 6005 can come in
any order, but in that case I have no clue of what might be going on.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||What can I say you guys, you really know your stuff. Thanks for all
the help, its given me the exact results I've needed.

I will consider you guys when I face another problem:-P

Sunny

Adding Time In DateTime Field

Hi

I'm trying to add a time from a DateTime field to provide a total. Eg:

Field1

01/02/2007 01:00:00PM

01/03/2007 01:45:00PM

01/04/2007 03:00:00PM

I want to add the time so I get a total of 05:45. The total hours could go over 24. I know I can't Sum it. I've seen several examples of how to do this but can't make any of them work. Could someone please point me in the right direction?

Thanks

set @.d1='01/02/2007 01:00:00PM'

datepart(hh,@.d1) return : 1

DATEADD ( hour, datepart(hh,@.d1), YourDate ) add 1 hour to your data

|||

Please check your objective. All of the times you list are afternoon times. The sum of the time component for all of these is the 5:45 plus an additional 36 hours. If your answer is correct, there is more to it than just summing the time components.

Code Snippet

declare @.aTable table (field1 datetime)
insert into @.aTable
select '01/02/2007 01:00:00PM' union all
select '01/03/2007 01:45:00PM' union all
select '01/04/2007 03:00:00PM'

select datediff(day, 0, sumOfTime) as Days,
convert(varchar, sumOfTime, 114) as Time
from ( select cast(sum(cast(field1 as float)
-floor(cast(field1 as float)))as datetime)
as sumOfTime
from @.aTable
) x

/*
Days Time
--
1 17:44:59:997
*/

|||

From the nature of your question, and looking at your sample data, I assume that the sample data represents 'elapsed time' on a date. And that 01:45PM means 1 hour and 45 minutes elapsed time -NOT 13:45 o'clock.

To calculate the total 'elapsed time', it would have been so much easier if you were storing the StartDateTime and EndDateTime -then it would be relatively simple date arithematic.

If my assumptions are correct, AND you cannot re-engineer the data to collect Start/End datetime values, this will be a bit more effort.

Please confirm.

|||

Sorry, I should have been clearer. The time is just a time, the date is irrelevant. It's actually a travel time, so Arnie you're correct, it is an elapsed time. I'm purely interested in adding the hours together. So in the 3 lines of sample data they travelled for 1 hour, 1 hour 45 minutes and 3 hours. AM/PM is also irrelevant. Travel time will never go over 12 hours. So the total I want is 5:45.

I'm working with someone else's data and tables here, personally I wouldn't have used a datetime field for this data but that is what I have. I also agree it would be better to have a start and end time, but I don't.

One possible way could be to extract the time, convert it to minutes, add those minutes together and then convert it back to hours and minutes...possibly? Any ideas?

|||

YOu could use this function:

Code Snippet

CREATE FUNCTION dbo.TimeDiffInHoursAndMinutes
(
@.Firstdate DATETIME,
@.Seconddate DATETIME
)
/*
Function written by Jens K. Suessmeyer, 07/22/2007
http://www.sqlserver2005.de
*/
RETURNS VARCHAR(10)
AS
BEGIN

DECLARE @.FirstdateMinutes INT
DECLARE @.SeconddateMinutes INT

SELECT @.FirstdateMinutes = DATEPART (hh,@.Firstdate)*60 + DATEPART(mi,@.Firstdate)
SELECT @.SeconddateMinutes = DATEPART (hh,@.Seconddate)*60 + DATEPART(mi,@.Seconddate)

RETURN (
SELECT
CONVERT(VARCHAR(10), FLOOR(@.SeconddateMinutes-@.FirstdateMinutes) / 60) +
':' +
RIGHT('00' + CONVERT(VARCHAR(10), (@.SeconddateMinutes-@.FirstdateMinutes) - FLOOR((@.SeconddateMinutes-@.FirstdateMinutes) / 60)*60),2))
END;

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

This 'should' move you in the direction you want. (I've added another row to the sample data.)

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
TravelTime smalldatetime
)

INSERT INTO @.MyTable VALUES ( '01/02/2007 01:00:00PM' )
INSERT INTO @.MyTable VALUES ( '01/03/2007 01:45:00PM' )
INSERT INTO @.MyTable VALUES ( '01/04/2007 03:00:00PM' )
INSERT INTO @.MyTable VALUES ( '01/04/2007 01:45:00PM' )

SELECT
Hours = sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 2 ) AS int )) +
( sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 1 ) AS int )) / 60 ) ,
Mins = ( sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 1 ) AS int )) % 60 )
FROM @.MyTable

Hours Mins
-- --
7 30

|||Thank you so much Arnie that seems perfect. |||In addition and althrough already closed, I found something on my blog (didn′t know that I wrote such a thing yet :-) )

http://www.sqlserver2005.de/sqlserver2005/MyBlog/tabid/56/EntryID/31/Default.aspx

Jens K. Suessmeyer

http://www.sqlserver2005.de

Adding Time In DateTime Field

Hi

I'm trying to add a time from a DateTime field to provide a total. Eg:

Field1

01/02/2007 01:00:00PM

01/03/2007 01:45:00PM

01/04/2007 03:00:00PM

I want to add the time so I get a total of 05:45. The total hours could go over 24. I know I can't Sum it. I've seen several examples of how to do this but can't make any of them work. Could someone please point me in the right direction?

Thanks

set @.d1='01/02/2007 01:00:00PM'

datepart(hh,@.d1) return : 1

DATEADD ( hour, datepart(hh,@.d1), YourDate ) add 1 hour to your data

|||

Please check your objective. All of the times you list are afternoon times. The sum of the time component for all of these is the 5:45 plus an additional 36 hours. If your answer is correct, there is more to it than just summing the time components.

Code Snippet

declare @.aTable table (field1 datetime)
insert into @.aTable
select '01/02/2007 01:00:00PM' union all
select '01/03/2007 01:45:00PM' union all
select '01/04/2007 03:00:00PM'

select datediff(day, 0, sumOfTime) as Days,
convert(varchar, sumOfTime, 114) as Time
from ( select cast(sum(cast(field1 as float)
-floor(cast(field1 as float)))as datetime)
as sumOfTime
from @.aTable
) x

/*
Days Time
--
1 17:44:59:997
*/

|||

From the nature of your question, and looking at your sample data, I assume that the sample data represents 'elapsed time' on a date. And that 01:45PM means 1 hour and 45 minutes elapsed time -NOT 13:45 o'clock.

To calculate the total 'elapsed time', it would have been so much easier if you were storing the StartDateTime and EndDateTime -then it would be relatively simple date arithematic.

If my assumptions are correct, AND you cannot re-engineer the data to collect Start/End datetime values, this will be a bit more effort.

Please confirm.

|||

Sorry, I should have been clearer. The time is just a time, the date is irrelevant. It's actually a travel time, so Arnie you're correct, it is an elapsed time. I'm purely interested in adding the hours together. So in the 3 lines of sample data they travelled for 1 hour, 1 hour 45 minutes and 3 hours. AM/PM is also irrelevant. Travel time will never go over 12 hours. So the total I want is 5:45.

I'm working with someone else's data and tables here, personally I wouldn't have used a datetime field for this data but that is what I have. I also agree it would be better to have a start and end time, but I don't.

One possible way could be to extract the time, convert it to minutes, add those minutes together and then convert it back to hours and minutes...possibly? Any ideas?

|||

YOu could use this function:

Code Snippet

CREATE FUNCTION dbo.TimeDiffInHoursAndMinutes
(
@.Firstdate DATETIME,
@.Seconddate DATETIME
)
/*
Function written by Jens K. Suessmeyer, 07/22/2007
http://www.sqlserver2005.de
*/
RETURNS VARCHAR(10)
AS
BEGIN

DECLARE @.FirstdateMinutes INT
DECLARE @.SeconddateMinutes INT

SELECT @.FirstdateMinutes = DATEPART (hh,@.Firstdate)*60 + DATEPART(mi,@.Firstdate)
SELECT @.SeconddateMinutes = DATEPART (hh,@.Seconddate)*60 + DATEPART(mi,@.Seconddate)

RETURN (
SELECT
CONVERT(VARCHAR(10), FLOOR(@.SeconddateMinutes-@.FirstdateMinutes) / 60) +
':' +
RIGHT('00' + CONVERT(VARCHAR(10), (@.SeconddateMinutes-@.FirstdateMinutes) - FLOOR((@.SeconddateMinutes-@.FirstdateMinutes) / 60)*60),2))
END;

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

This 'should' move you in the direction you want. (I've added another row to the sample data.)

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
TravelTime smalldatetime
)

INSERT INTO @.MyTable VALUES ( '01/02/2007 01:00:00PM' )
INSERT INTO @.MyTable VALUES ( '01/03/2007 01:45:00PM' )
INSERT INTO @.MyTable VALUES ( '01/04/2007 03:00:00PM' )
INSERT INTO @.MyTable VALUES ( '01/04/2007 01:45:00PM' )

SELECT
Hours = sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 2 ) AS int )) +
( sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 1 ) AS int )) / 60 ) ,
Mins = ( sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 1 ) AS int )) % 60 )
FROM @.MyTable

Hours Mins
-- --
7 30

|||Thank you so much Arnie that seems perfect. |||In addition and althrough already closed, I found something on my blog (didn′t know that I wrote such a thing yet :-) )

http://www.sqlserver2005.de/sqlserver2005/MyBlog/tabid/56/EntryID/31/Default.aspx

Jens K. Suessmeyer

http://www.sqlserver2005.de

Adding time

Hi,
I have a table with process_id and process_started_at and
process_completed_at which are both datetime datatype.
I need to calculate the total time taken for a particular process for a
given date-range.
and calculate average time per day per process_id.
How can I do it I have MS SQL 2003.
If I add the 2 datetime variables it increments the day by 1 after
every 24 hrs but that is not what I want.
Example if I have
date1 = '2005-01-01 20:20:30'
date2 = '2005-01-01 20:25:20'
then I want the result as '40:45:50' I am not concerned with the date
part.
I tried creating a function which would add the two times but then how
do I get the average? Do I have to write another function which will
convert the total time into seconds and then devide by total number of
days and then convert back to hr:min:sec or is there is easier way to
do it?
Does MS SQL have any simple methode which will convert the time to
seconds and seconds to hr:min:sec?
Thanks for your time and expertise
Ashoo> Does MS SQL have any simple methode which will convert the time to
> seconds and seconds to hr:min:sec?
For the latter, see http://www.aspfaq.com/2271

Adding new columns to all tables using a script

Hi, I'm trying to add a modified datetime and userid to all 72 tables in my
SQL 2000 database. I have the script to do one table, and a cursor, but it
won't run across all tables. Any help would be appreciated. Thanks...

DECLARE @.tName varchar(40)
DECLARE C1 CURSOR FOR
select name from sysobjects where type = 'U'
OPEN C1
FETCH NEXT FROM C1 INTO @.tName
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE @.tName ADD
ModifiedDT datetime NULL,
ModifiedUserID int NULL
GO
COMMIT
FETCH NEXT FROM C1
END
CLOSE C1
DEALLOCATE C1
GOHi

As this is not production code then you may want to check out the
undocumented sp_MSforeachtable

http://groups.google.co.uk/groups?h...2%40tkmsftngp03

http://groups.google.co.uk/groups?h...man%40127.0.0.1

John

"Paul" <psampson@.uecomm.com.au> wrote in message
news:1061944796.500758@.proxy.uecomm.net.au...
> Hi, I'm trying to add a modified datetime and userid to all 72 tables in
my
> SQL 2000 database. I have the script to do one table, and a cursor, but it
> won't run across all tables. Any help would be appreciated. Thanks...
> DECLARE @.tName varchar(40)
> DECLARE C1 CURSOR FOR
> select name from sysobjects where type = 'U'
> OPEN C1
> FETCH NEXT FROM C1 INTO @.tName
> -- Check @.@.FETCH_STATUS to see if there are any more rows to fetch
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- This is executed as long as the previous fetch succeeds
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> ALTER TABLE @.tName ADD
> ModifiedDT datetime NULL,
> ModifiedUserID int NULL
> GO
> COMMIT
> FETCH NEXT FROM C1
> END
> CLOSE C1
> DEALLOCATE C1
> GO|||Paul (psampson@.uecomm.com.au) writes:
> Hi, I'm trying to add a modified datetime and userid to all 72 tables in
> my SQL 2000 database. I have the script to do one table, and a cursor,
> but it won't run across all tables. Any help would be appreciated.

There are a number of errors in your script:

> DECLARE @.tName varchar(40)
> DECLARE C1 CURSOR FOR

While not an error, I recommend that you make your cursors INSENSITIVE
as a matter of routine. The default keyset-driven cursors can sometimes
give nasty surprises.

> select name from sysobjects where type = 'U'
> OPEN C1
> FETCH NEXT FROM C1 INTO @.tName
> -- Check @.@.FETCH_STATUS to see if there are any more rows to fetch
> WHILE @.@.FETCH_STATUS = 0

I recommend that you write cursor loops as

OPEN cur
WHILE 1 = 1
BEGIN
FETCH cur INTO @.var1, @.var2...
IF @.@.fetch_status <> 0
BREAK
-- Real job follows here.
END
DEALLOCATE cur

By only having one FETCH statement you make your code safer, because it's
easy to change the SELECT statement, and the new column to the first
FETCH, but forget the second, which may be the screens below.

> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT

There is no point in executing the SET statements in the loop, and
there is no point to make this a transaction. Not that it is wrong
either.

> BEGIN TRANSACTION
> ALTER TABLE @.tName ADD
> ModifiedDT datetime NULL,
> ModifiedUserID int NULL
> GO

Here are two serious flaws: ALTER TABLE does not accept a variable.
You need to use dynamic SQL for this. (Or sp_MSforeachtable.)

And the GO there is completely out of place. GO is not an SQL command,
but an instruction to the query tool to separate the commands into
different batches. Thus, this batch will fail with a compilation
error, because the BEGIN after WHILE does not have an END.

> FETCH NEXT FROM C1

And if you thought what I said about FETCH above was silly, look here!
Here you don't insert into a variable, but produce a result set.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks John, I'll check it out

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f4c5f38$0$256$ed9e5944@.reading.news.pipex.ne t...
> Hi
> As this is not production code then you may want to check out the
> undocumented sp_MSforeachtable
>
http://groups.google.co.uk/groups?h...2%40tkmsftngp03
>
http://groups.google.co.uk/groups?h...man%40127.0.0.1
> John
> "Paul" <psampson@.uecomm.com.au> wrote in message
> news:1061944796.500758@.proxy.uecomm.net.au...
> > Hi, I'm trying to add a modified datetime and userid to all 72 tables in
> my
> > SQL 2000 database. I have the script to do one table, and a cursor, but
it
> > won't run across all tables. Any help would be appreciated. Thanks...
> > DECLARE @.tName varchar(40)
> > DECLARE C1 CURSOR FOR
> > select name from sysobjects where type = 'U'
> > OPEN C1
> > FETCH NEXT FROM C1 INTO @.tName
> > -- Check @.@.FETCH_STATUS to see if there are any more rows to fetch
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > -- This is executed as long as the previous fetch succeeds
> > BEGIN TRANSACTION
> > SET QUOTED_IDENTIFIER ON
> > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> > SET ARITHABORT ON
> > SET NUMERIC_ROUNDABORT OFF
> > SET CONCAT_NULL_YIELDS_NULL ON
> > SET ANSI_NULLS ON
> > SET ANSI_PADDING ON
> > SET ANSI_WARNINGS ON
> > COMMIT
> > BEGIN TRANSACTION
> > ALTER TABLE @.tName ADD
> > ModifiedDT datetime NULL,
> > ModifiedUserID int NULL
> > GO
> > COMMIT
> > FETCH NEXT FROM C1
> > END
> > CLOSE C1
> > DEALLOCATE C1
> > GO

Adding datetime to database?

I have DateCreated with datetime datatype in my SQL Express 2005. I'd like to add the record to my Task table so I have a form in my ASPX and create a button event in my ASPX.CS here is the code

protected void Button_AddTask_Click(object sender, EventArgs e)
{
SqlDataSource newTask = new SqlDataSource();
newTask.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
newTask.InsertCommand = "INSERT INTO [Task] ([MemberAccountName], [Title], [Place], [TaskDetail],[DateCreated]) VALUES (@.MemberAccountName, @.Title, @.Place, @.TaskDetail,@.DateCreated)";
newTask.InsertParameters.Add("MemberAccountName", User.Identity.Name);
newTask.InsertParameters.Add("Title", TextBox_Title.Text);
newTask.InsertParameters.Add("Place", TextBox_Place.Text);
newTask.InsertParameters.Add("TaskDetail", TextBox_Detail.Text);
newTask.InsertParameters.Add("DateCreated",DateTime.Now.ToString());
newTask.Insert();
Response.Redirect("Default.aspx");
}

but I got this error

Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

Any idea?
Sorry your guy, I think I found the problem...

In my SQL Express 2005 use the datetime format in DD/MM/YYYY

butDateTime.Now.ToString() giveMM/DD/YYYY

I found it by hardcoding "22/12/2005 00:00:00" and it works!!

So now how can I fix this??
|||

Check the other ways to use the add, there should be one that allows you to specify the parameter type. Tell it that it is a datetime, and pass in datetime.now (NOT datetime.now.tostring).

|||You can avoid date problems by always presenting your date in YYYYMMDD format to SQL Server.

In your original code, try this instead (add a format to the ToString method):

newTask.InsertParameters.Add("DateCreated",DateTime.Now.ToString("yyyyMMdd"));

|||Thank you everyone, now I can make it work

However there is a little problem with my Thai Buddhist year !!

There is 543 year different between Christ year and Buddhist year.

I can get the Datetime.Now show correctly in the page but when inserting into the database the system will add 543 automatically to the year !!! So when I retrieve the data back from the database it get the year 3091 !!

So what I have to do is
.
DateTime dt = DateTime.Now;
.
.
newTask.InsertParameters.Add("DateCreated", dt.AddYears(-543).ToString("yyyyMMdd HH:mm:ss"));
.

It's kind of weird, right??|||See my post 3 posts up.

Adding datetime fields

Hi

I'm learning SQL, stuck on a problem, and would be very grateful if someone could point me in the right direction please.

I have a table that contains employee overtime data. The table contains the employee ID number, the work week ID, basic hours, and overtime hours worked.
What i want to do is SUM(OThrs) for a particular employee to get the total OT hours worked in a given workweek.

However, as I understand it the datetime datatype stores its value as a value measured from a base date of Dec-30-1899. As it wouldn't make sense to add the datetime fields due to this, is there any way around it?

The OThrs is brought in from a csv file through a DTS package and is in the format of.... eg 07:45, 13:20, 02:12, 08:10

So if those times above were all for the same employee in the same work week, it would total 31:27

I'd be grateful for some poiters on this problem.
Thanks & Regards
MartyT

If you did not specify any date part in the datetime value, then the date portion will default to 1900-01-01. Confirm that this is the case for those values. Assuming this condition, you can do the following:

select t.EmployeeId, t.WorkWeekId,
convert(varchar(5), dateadd(minute, sum(datediff(minute, '', t.Othrs)), ''), 114) as total_ot_hours
from tbl as t
group by t.EmployeeId, t.WorkWeekId

Note that the above query only has resolution less than 24 hrs. If you need more than that, then take the minute value directly and generate the hour/minutes part yourself.|||That's a big help. Thanks for your time - much appreciated

Adding an int to a datetime type as minutes pleasse help!

Hi,
I'm trying to add an int type to a datetime type to produce a
datatime type that interprets the int as *minutes*, for example:
2008-03-20 15:36:09.920 + 10 = 2008-03-20 15:46:09.920
Problem is though - I'm stuck. Whenever I try to do this SQL Server
2005 interprets the "int" as days and I get the wrong answer. Can
ayone help me please? Any comments/suggestions/code-samples much, much
appreciated.
Thank,
Al.
Use the DATEADD function. Implicit DATETIME math is always in number of
days.
SELECT DATEADD(MINUTE, 10, '2008-03-20 15:36:09.920');
While the default is not entirely intuitive, why would you assume that SQL
Server will know that when you typed "10" you meant minutes? What if I did
the same, and expected seconds, and my co-worker expects months? SQL Server
can't be psychic...
A
<almurph@.altavista.com> wrote in message
news:36124fcf-6486-43cc-8297-90972b9d9817@.h11g2000prf.googlegroups.com...
> Hi,
> I'm trying to add an int type to a datetime type to produce a
> datatime type that interprets the int as *minutes*, for example:
> 2008-03-20 15:36:09.920 + 10 = 2008-03-20 15:46:09.920
>
> Problem is though - I'm stuck. Whenever I try to do this SQL Server
> 2005 interprets the "int" as days and I get the wrong answer. Can
> ayone help me please? Any comments/suggestions/code-samples much, much
> appreciated.
> Thank,
> Al.
|||On Mar 20, 3:55Xpm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Use the DATEADD function. XImplicit DATETIME math is always in number of
> days.
> SELECT DATEADD(MINUTE, 10, '2008-03-20 15:36:09.920');
> While the default is not entirely intuitive, why would you assume that SQL
> Server will know that when you typed "10" you meant minutes? XWhat if I did
> the same, and expected seconds, and my co-worker expects months? XSQL Server
> can't be psychic...
> A
> <almu...@.altavista.com> wrote in message
> news:36124fcf-6486-43cc-8297-90972b9d9817@.h11g2000prf.googlegroups.com...
>
>
>
>
> - Show quoted text -
Aaron,
Thank you very much.
Al.

Adding an int to a datetime type as minutes pleasse help!

Hi,
I'm trying to add an int type to a datetime type to produce a
datatime type that interprets the int as *minutes*, for example:
2008-03-20 15:36:09.920 + 10 = 2008-03-20 15:46:09.920
Problem is though - I'm stuck. Whenever I try to do this SQL Server
2005 interprets the "int" as days and I get the wrong answer. Can
ayone help me please? Any comments/suggestions/code-samples much, much
appreciated.
Thank,
Al.Use the DATEADD function. Implicit DATETIME math is always in number of
days.
SELECT DATEADD(MINUTE, 10, '2008-03-20 15:36:09.920');
While the default is not entirely intuitive, why would you assume that SQL
Server will know that when you typed "10" you meant minutes? What if I did
the same, and expected seconds, and my co-worker expects months? SQL Server
can't be psychic...
A
<almurph@.altavista.com> wrote in message
news:36124fcf-6486-43cc-8297-90972b9d9817@.h11g2000prf.googlegroups.com...
> Hi,
> I'm trying to add an int type to a datetime type to produce a
> datatime type that interprets the int as *minutes*, for example:
> 2008-03-20 15:36:09.920 + 10 = 2008-03-20 15:46:09.920
>
> Problem is though - I'm stuck. Whenever I try to do this SQL Server
> 2005 interprets the "int" as days and I get the wrong answer. Can
> ayone help me please? Any comments/suggestions/code-samples much, much
> appreciated.
> Thank,
> Al.|||On Mar 20, 3:55=A0pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Use the DATEADD function. =A0Implicit DATETIME math is always in number of=
> days.
> SELECT DATEADD(MINUTE, 10, '2008-03-20 15:36:09.920');
> While the default is not entirely intuitive, why would you assume that SQL=
> Server will know that when you typed "10" you meant minutes? =A0What if I =did
> the same, and expected seconds, and my co-worker expects months? =A0SQL Se=rver
> can't be psychic...
> A
> <almu...@.altavista.com> wrote in message
> news:36124fcf-6486-43cc-8297-90972b9d9817@.h11g2000prf.googlegroups.com...
>
> > Hi,
> > I'm trying to add an int type to a datetime type to produce a
> > datatime type that interprets the int as *minutes*, for example:
> > 2008-03-20 15:36:09.920 + 10 =3D 2008-03-20 15:46:09.920
> > Problem is though - I'm stuck. Whenever I try to do this SQL Server
> > 2005 interprets the "int" as days and I get the wrong answer. Can
> > ayone help me please? Any comments/suggestions/code-samples much, much
> > appreciated.
> > Thank,
> > Al.- Hide quoted text -
> - Show quoted text -
Aaron,
Thank you very much.
Al.

Adding a value to a 'datetime' column caused overflow.

Hi,
When I use dateadd function to a table containing around 10000 values,
it gave the following msg. Adding a value to a 'datetime' column caused
overflow. What does it mean?
Thanks,
Mike
You are exceeding the valid datetime range differs if you use datetime
or smalldateimte, which command did you use ? could you please post the
commandtext you are using ? What is the datatype of you are doing the
dateadd operation.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks for your reply.
I am using float type date to convert to a datetime. Please see the
following codes.
select dateadd(dd, Def_Date, '1/1/1960') from one;
Thanks,
Mike
Jens wrote:
> You are exceeding the valid datetime range differs if you use datetime
> or smalldateimte, which command did you use ? could you please post the
> commandtext you are using ? What is the datatype of you are doing the
> dateadd operation.
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
|||Thanks a lot! This is exactly the problem!
Thanks,
Mike
Gert-Jan Strik wrote:[vbcol=seagreen]
> Note that when you use dateadd, the second parameter should be a number
> representing the number of ... (in your case days) that should be added.
> If Def_Date is a float "representing" a date, then this value is likely
> to be too high. If it exceeds 2936549 you will get an out or range error
> (or similar error). If it represents a date, you should cast it to a
> datetime.
> Gert-Jan
>
> Michael wrote:

Adding a value to a 'datetime' column caused overflow.

Hi,
When I use dateadd function to a table containing around 10000 values,
it gave the following msg. Adding a value to a 'datetime' column caused
overflow. What does it mean?
Thanks,
MikeYou are exceeding the valid datetime range differs if you use datetime
or smalldateimte, which command did you use ? could you please post the
commandtext you are using ? What is the datatype of you are doing the
dateadd operation.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Thanks for your reply.
I am using float type date to convert to a datetime. Please see the
following codes.
select dateadd(dd, Def_Date, '1/1/1960') from one;
Thanks,
Mike
Jens wrote:
> You are exceeding the valid datetime range differs if you use datetime
> or smalldateimte, which command did you use ? could you please post the
> commandtext you are using ? What is the datatype of you are doing the
> dateadd operation.
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --|||Note that when you use dateadd, the second parameter should be a number
representing the number of ... (in your case days) that should be added.
If Def_Date is a float "representing" a date, then this value is likely
to be too high. If it exceeds 2936549 you will get an out or range error
(or similar error). If it represents a date, you should cast it to a
datetime.
Gert-Jan
Michael wrote:
> Thanks for your reply.
> I am using float type date to convert to a datetime. Please see the
> following codes.
> select dateadd(dd, Def_Date, '1/1/1960') from one;
> Thanks,
> Mike
> Jens wrote:
> > You are exceeding the valid datetime range differs if you use datetime
> > or smalldateimte, which command did you use ? could you please post the
> > commandtext you are using ? What is the datatype of you are doing the
> > dateadd operation.
> >
> > HTH, Jens K. Suessmeyer.
> >
> > --
> > http://www.sqlserver2005.de
> > --|||Thanks a lot! This is exactly the problem!
Thanks,
Mike
Gert-Jan Strik wrote:
> Note that when you use dateadd, the second parameter should be a number
> representing the number of ... (in your case days) that should be added.
> If Def_Date is a float "representing" a date, then this value is likely
> to be too high. If it exceeds 2936549 you will get an out or range error
> (or similar error). If it represents a date, you should cast it to a
> datetime.
> Gert-Jan
>
> Michael wrote:
> >
> > Thanks for your reply.
> >
> > I am using float type date to convert to a datetime. Please see the
> > following codes.
> >
> > select dateadd(dd, Def_Date, '1/1/1960') from one;
> >
> > Thanks,
> > Mike
> >
> > Jens wrote:
> > > You are exceeding the valid datetime range differs if you use datetime
> > > or smalldateimte, which command did you use ? could you please post the
> > > commandtext you are using ? What is the datatype of you are doing the
> > > dateadd operation.
> > >
> > > HTH, Jens K. Suessmeyer.
> > >
> > > --
> > > http://www.sqlserver2005.de
> > > --

Adding a value to a 'datetime' column caused overflow.

Hi,
When I use dateadd function to a table containing around 10000 values,
it gave the following msg. Adding a value to a 'datetime' column caused
overflow. What does it mean?
Thanks,
MikeThanks a lot! This is exactly the problem!
Thanks,
Mike
Gert-Jan Strik wrote:[vbcol=seagreen]
> Note that when you use dateadd, the second parameter should be a number
> representing the number of ... (in your case days) that should be added.
> If Def_Date is a float "representing" a date, then this value is likely
> to be too high. If it exceeds 2936549 you will get an out or range error
> (or similar error). If it represents a date, you should cast it to a
> datetime.
> Gert-Jan
>
> Michael wrote:|||You are exceeding the valid datetime range differs if you use datetime
or smalldateimte, which command did you use ? could you please post the
commandtext you are using ? What is the datatype of you are doing the
dateadd operation.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Thanks for your reply.
I am using float type date to convert to a datetime. Please see the
following codes.
select dateadd(dd, Def_Date, '1/1/1960') from one;
Thanks,
Mike
Jens wrote:
> You are exceeding the valid datetime range differs if you use datetime
> or smalldateimte, which command did you use ? could you please post the
> commandtext you are using ? What is the datatype of you are doing the
> dateadd operation.
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --|||Note that when you use dateadd, the second parameter should be a number
representing the number of ... (in your case days) that should be added.
If Def_Date is a float "representing" a date, then this value is likely
to be too high. If it exceeds 2936549 you will get an out or range error
(or similar error). If it represents a date, you should cast it to a
datetime.
Gert-Jan
Michael wrote:[vbcol=seagreen]
> Thanks for your reply.
> I am using float type date to convert to a datetime. Please see the
> following codes.
> select dateadd(dd, Def_Date, '1/1/1960') from one;
> Thanks,
> Mike
> Jens wrote:

Adding a time column to a date column

I have two columns in a table:
StartDate DateTime and StartTime DateTime.
The StartDate column holds a value such as 07/16/2004
The StartTime column holds a value such as 3:00:00 PM

I want to be able to add them in a stored procedure.
When I use StartDate + StartTime I get a date two days earlier than expected.
For example, instead of 7/16/2004 3:00:00 PM StartDate + StartTime returns
7/14/2004 3:00:00 PM.

Can anyone point out wht I'm doing wrong with this one?

Thanks,
lqLauren,

It sounds like you are using Enterprise Manager. All datetime columns in SQL Server hold both a date and a time, and if you view the
data in Query Analyzer, you should find that your StartDate column holds something like 2004-07-16 12:00:00AM and your StartTime column
holds a value like 1899-12-30 03:00:00PM.

Tools that allows data input will attach a date when a time only is entered into a SQL Server database column, and unfortunately some
tools will attach 1900-01-01 and others will attach 1899-12-30. SQL Server stores the first of these as its zero date, but it stores the
second as -2 (plus whatever fraction of a date the time portion represents, in each case). Enterprise Manager thinks that 1899-12-30 is the
base date, and both attaches it when a bare time is entered and suppresses it when it appears in a datetime to be displayed.

You are doing nothing wrong, but to be safe, you can calculate the time portion explicitly before you add. One way to do this is

StartDate + (StartTime - datediff(day,0,StartTime))

Storing time-only values in SQL Server is tricky, since there is no appropriate type. You need to be careful, and you might want to
consider alternatives, such as storing only the StartDateTime in the database, in which case you can make StartDate and StartTime computed
columns, or calculate them on the fly when you need them.

Steve Kass
Drew University

Lauren Quantrell wrote:

> I have two columns in a table:
> StartDate DateTime and StartTime DateTime.
> The StartDate column holds a value such as 07/16/2004
> The StartTime column holds a value such as 3:00:00 PM
> I want to be able to add them in a stored procedure.
> When I use StartDate + StartTime I get a date two days earlier than expected.
> For example, instead of 7/16/2004 3:00:00 PM StartDate + StartTime returns
> 7/14/2004 3:00:00 PM.
> Can anyone point out wht I'm doing wrong with this one?
> Thanks,
> lq|||Steve,
Thanks a million for that. I was trying all manner of cast, convert
and datepart functions but yours is quick and simple. Thanks!
lq

Steve Kass <skass@.drew.edu> wrote in message news:<8hdLc.8319$mL5.4812@.newsread1.news.pas.earthlink.n et>...
> Lauren,
> It sounds like you are using Enterprise Manager. All datetime columns in SQL Server hold both a date and a time, and if you view the
> data in Query Analyzer, you should find that your StartDate column holds something like 2004-07-16 12:00:00AM and your StartTime column
> holds a value like 1899-12-30 03:00:00PM.
> Tools that allows data input will attach a date when a time only is entered into a SQL Server database column, and unfortunately some
> tools will attach 1900-01-01 and others will attach 1899-12-30. SQL Server stores the first of these as its zero date, but it stores the
> second as -2 (plus whatever fraction of a date the time portion represents, in each case). Enterprise Manager thinks that 1899-12-30 is the
> base date, and both attaches it when a bare time is entered and suppresses it when it appears in a datetime to be displayed.
> You are doing nothing wrong, but to be safe, you can calculate the time portion explicitly before you add. One way to do this is
> StartDate + (StartTime - datediff(day,0,StartTime))
> Storing time-only values in SQL Server is tricky, since there is no appropriate type. You need to be careful, and you might want to
> consider alternatives, such as storing only the StartDateTime in the database, in which case you can make StartDate and StartTime computed
> columns, or calculate them on the fly when you need them.
> Steve Kass
> Drew University
> Lauren Quantrell wrote:
> > I have two columns in a table:
> > StartDate DateTime and StartTime DateTime.
> > The StartDate column holds a value such as 07/16/2004
> > The StartTime column holds a value such as 3:00:00 PM
> > I want to be able to add them in a stored procedure.
> > When I use StartDate + StartTime I get a date two days earlier than expected.
> > For example, instead of 7/16/2004 3:00:00 PM StartDate + StartTime returns
> > 7/14/2004 3:00:00 PM.
> > Can anyone point out wht I'm doing wrong with this one?
> > Thanks,
> > lq