Convert Datetime to seconds and then add

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 2000 Forums

Transact-SQL (2000)

Convert Datetime to seconds and then add

Author

Topic

Sanchit297
Starting Member

16 Posts

Posted - 2010-05-26 : 12:44:31

Hi,

I have two fields - Date1 and Date2 both are DATETIME types.
Suppose, Date1 = '2010-05-10 06:34:12'
Date2 = '2010-05-11 09:12:50'

I want to do the below actions.

1) Convert both the fields to Seconds format
2) Add date1 + date2 (after convertion to seconds is done)
3) Divide the result from Step 2) with a number say 1000
4) Convert the result from Step4) to NVARCHAR format

How can the above be achieved.

Thanks in advance,
Sanchit

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-26 : 12:53:14

Why? What is your actual requirement?

Sanchit297
Starting Member

16 Posts

Posted - 2010-05-26 : 12:58:24

Hi,

In my projects it is a requirement to have this field being calculated as the Sum of 2 date fields divided by a number.

I do not know hoe to convert the time field here into seconds so that I can add the times for Date1 and same with Date2. I tried using Datepart function, but not able to get what I want.

Hope it helps.

Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-26 : 13:16:56

So you only want to convert the time part of your datetime value to seconds?

declare @Date1 datetime
declare @Date2 datetime
select @Date1 = '2010-05-10 06:34:12'
select @Date2 = '2010-05-11 09:12:50'

SELECT 
convert(varchar(10),
(
 (
	(DATEPART(hour, @Date1) * 60 * 60) +
	(DATEPART(minute, @Date1) * 60) +
	 DATEPART(second, @Date1)
 )
 +
 (
	(DATEPART(hour, @Date2) * 60 * 60) +
	(DATEPART(minute, @Date2) * 60) +
	 DATEPART(second, @Date2)
 )
)
/ 1000
) as Result

No, you're never too old to Yak'n'Roll if you're too young to die.

Sanchit297
Starting Member

16 Posts

Posted - 2010-05-26 : 14:09:57

Thanks for your reply !

I made a annonymous block to test this particular part and got an error after execution. The block is as given below.


begin

declare @date1 nvarchar(100), @date2 nvarchar(100), @FinalDate nvarchar(100), @count int

create table #temp6 (tks int, date1 nvarchar(100), date2 nvarchar(100), FinalDate nvarchar(100))

select @count = sum(events) from import where id=13768
SELECT @date1 = Convert(nvarchar(10),sum(DatePart(hh,ScanDateTime)))+':'+ Convert(nvarchar(10),sum(DatePart(mi,ScanDateTime)))+':'+ Convert(nvarchar(10),sum(DatePart(ss,ScanDateTime))) FROM import WHERE id=13768
SELECT @date2 = Convert(nvarchar(10),sum(DatePart(hh,DataDateTime)))+':'+ Convert(nvarchar(10),sum(DatePart(mi,DataDateTime)))+':'+ Convert(nvarchar(10),sum(DatePart(ss,DataDateTime))) FROM import WHERE id=13768

SELECT @FinalDate =
convert(nvarchar(10),
(
 (
	(DATEPART(hour, @date1) * 60 * 60) +
	(DATEPART(minute,@date1) * 60) +
	 DATEPART(second, @date1)
 )
 +
 (
	(DATEPART(hour, @date2) * 60 * 60) +
	(DATEPART(minute, @date2) * 60) +
	 DATEPART(second, @date2)
 )
)
)
--select @c = @b/@count

insert into #temp6 values(@count, @date1, @date2, @FinalDate)

end

--drop table #temp6
--select * from #temp6

I got the below error after execution..

Arithmetic overflow error converting expression to data type datetime.

(1 row(s) affected)

What has gone wrong here??

Thanks.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-26 : 14:21:07

What has gone wrong is...you have defined @date1 and @date2 as varchar fields and trying to get something that wont fit in there.

Webfred has declared his fields as datetime so the DATEPART would work as expected.

Sanchit297
Starting Member

16 Posts

Posted - 2010-05-26 : 14:25:31

Thanks Vijay.

How could I resolve the issue as if im trying to set the dates to DateTime type, its still giving me error of Overflow.

Thanks in adnavce.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-26 : 14:33:26

What is the value of @date1 and @date2 after you run this?

declare @date1 nvarchar(100), @date2 nvarchar(100), @FinalDate nvarchar(100), @count int

select @count = sum(events) from import where id=13768
SELECT @date1 = Convert(nvarchar(10),sum(DatePart(hh,ScanDateTime)))+':'+ Convert(nvarchar(10),sum(DatePart(mi,ScanDateTime)))+':'+ Convert(nvarchar(10),sum(DatePart(ss,ScanDateTime))) FROM import WHERE id=13768
SELECT @date2 = Convert(nvarchar(10),sum(DatePart(hh,DataDateTime)))+':'+ Convert(nvarchar(10),sum(DatePart(mi,DataDateTime)))+':'+ Convert(nvarchar(10),sum(DatePart(ss,DataDateTime))) FROM import WHERE id=13768

Sanchit297
Starting Member

16 Posts

Posted - 2010-05-26 : 14:41:31

The value of date1 and date2 fields is NVARCHAR(100) in hh:mm:ss format after running the select queries..

Sanchit297
Starting Member

16 Posts

Posted - 2010-05-27 : 02:14:19

I used the below block to get the AverageTime field by adding the 2 date columns by first converting them to seconds and then dividing it with a number field.


begin

declare @date1 nvarchar(100), @date2 nvarchar(100), @FinalDate nvarchar(100), @count int

create table #temp6 (tks int, date1 nvarchar(100), date2 nvarchar(100), FinalDate nvarchar(100))

select @count = count(eventcode) from trackedplusimport_temp where deviceid=13768
SELECT @date1 = Convert(nvarchar(10),sum(DatePart(hh,ScanDateTime)))+':'+ Convert(nvarchar(10),sum(DatePart(mi,ScanDateTime)))+':'+ Convert(nvarchar(10),sum(DatePart(ss,ScanDateTime))) FROM trackedplusimport_temp WHERE deviceid=13768
SELECT @date2 = Convert(nvarchar(10),sum(DatePart(hh,DataDateTime)))+':'+ Convert(nvarchar(10),sum(DatePart(mi,DataDateTime)))+':'+ Convert(nvarchar(10),sum(DatePart(ss,DataDateTime))) FROM trackedplusimport_temp WHERE deviceid=13768

SELECT @FinalDate =
convert(nvarchar(10),
(
 (
	(DATEPART(hour, convert(nvarchar,@date1,108)) * 60 * 60) +
	(DATEPART(minute,convert(nvarchar,@date1,108)) * 60) +
	 DATEPART(second, convert(nvarchar,@date1,108))
 )
 +
 (
	(DATEPART(hour, convert(nvarchar,@date2,108)) * 60 * 60) +
	(DATEPART(minute, convert(nvarchar,@date2,108)) * 60) +
	 DATEPART(second, convert(nvarchar,@date2,108))
 )
)
)
--select @c = @b/@count

insert into #temp6 values(@count, @date1, @date2, @FinalDate)

end

--drop table #temp6
--select * from #temp6

When Executing the above, Im getting the below error..

Arithmetic overflow error converting expression to data type datetime

How can I overcome this error?

Thanks in advance

Sanchit297
Starting Member

16 Posts

Posted - 2010-05-27 : 03:14:09

Just some more info..

When Date1 and Date2 are upto : '23:59:59', the query is working fine. But when it becomes '24:00:00' - I am getting Overflow error.

Abhishek_1987
Starting Member

3 Posts

Posted - 2012-05-28 : 03:38:12

I want to convert Date time or nvarchar of format hh:mm:ss to seconds. Where my date1 and date2 are not pre-defined. I have a table with two columns Start date and End Date. I want the difference of this time in seconds. I have nearly 15k records in my table. How can i acheieve that?

abhi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources