Get time remaining in an auction

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

SQL Server Development (2000)

Get time remaining in an auction

Author

Topic

fpineda101
Starting Member

7 Posts

Posted - 2006-04-26 : 17:56:01

Im trying to get time remaining in an auction. The timestamp "datecreated" has 7 days added to it for an end date. I subtract the current timedate to get the time remaining. However, I'm getting errors like it can't convert that value to integer.

Here is my code...

Select abs(datediff( day, DateCreated + 7, getDate() )) +
abs((datediff( second, DateCreated + 7, getDate() )*24) % 24) +
abs((datediff( second, DateCreated + 7, getDate() )*24*60) % 60) + abs((datediff( second, DateCreated + 7, getDate() )*24*60*60) %60)
FROM table_name

I've tried str and floor instead of abs. Still got the same error. Please help, it will be greatly appreciated!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-26 : 18:05:03

It would be helpful if you explained what you want your query to return.

CODO ERGO SUM

fpineda101
Starting Member

7 Posts

Posted - 2006-04-27 : 09:35:53

I want the query result to be something like this...

'x days x hours x minutes x seconds remaining'

Similiar to Oracle query ...

select trunc((datecreated + 7)-sysdate ) || ' days ' ||
trunc( mod( ((datecreated + 7)-sysdate )*24, 24 ) ) || ' hours ' ||
trunc( mod( ((datecreated + 7)-sysdate )*24*60, 60 ) ) || ' minutes ' ||
trunc( mod( ((datecreated + 7)-sysdate )*24*60*60, 60 ) ) || ' seconds remain'
FROM table_name

output = 1 day 4 hours 32 minutes 29 seconds remain

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-27 : 10:02:42

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-27 : 10:10:59

This shows how to calculate the time remaining.

select
	DAYS	= datediff(dd,0,TIME_REMAINING),
	HOURS	= datepart(hh,TIME_REMAINING),
	MINUTES	= datepart(mi,TIME_REMAINING),
	SECONDS	= datepart(ss,TIME_REMAINING),
	CURR_TIME = convert(varchar(19),CURR_TIME,121),
	AUCTION_END = convert(varchar(19),AUCTION_END,121),
	TIME_REMAINING = convert(varchar(19),TIME_REMAINING,121)
from
	(
	select
		-- Time remaining as offset from
		-- 1900-01-01 00:00:00
		TIME_REMAINING = 
		dateadd(ss,datediff(ss,CURR_TIME,AUCTION_END),0),
		*
	from
		(
		select
			AUCTION_END = 
			-- 7 days after auction start
			dateadd(dd,7,'20060423 09:44:55'),
			CURR_TIME = getdate()
		) aa
	) a

Results:

DAYS        HOURS       MINUTES     SECONDS     CURR_TIME           AUCTION_END         TIME_REMAINING      
----------- ----------- ----------- ----------- ------------------- ------------------- ------------------- 
2           23          36          40          2006-04-27 10:08:15 2006-04-30 09:44:55 1900-01-03 23:36:40

(1 row(s) affected)

CODO ERGO SUM

fpineda101
Starting Member

7 Posts

Posted - 2006-04-28 : 02:33:04

MVJ

I would like to do this in a .Net dataset. So I would be using the executescalar command. In other words, I need the result as one string. I tried both of the queries and I got some errors. Also, how do I select the datecreated from the database, instead of using the date in single quotes? "dateadd(dd,7,'20060423 09:44:55')"

KHTAN

How do I make the query so that the date in single quotes is dynamic, according to the datecreated which is selected from the database? "select convert(datetime, '2006-04-01') as datecreated"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-28 : 02:40:40

[code]select convert(varchar(10), sec / (24 * 60 * 60)) + ' days ' +
convert(varchar(2), sec / (60 * 60) % 24) + ' hours ' +
convert(varchar(2), (sec / 60) % 60) + ' mins ' +
convert(varchar(2), sec % 60) + ' secs remaining'
from
(
select datediff(second, dateadd(day, 7, datecreated), getdate()) as sec
from yourtable
where ( . . your condition here . . )
) s[/code]

You can also create a scalar function for the codes in red.
[code]
select dbo.F_RemainingTimes(datediff(second, dateadd(day, 7, datecreated), getdate()))
from yourtable
where . . .
[/code]

fpineda101
Starting Member

7 Posts

Posted - 2006-04-28 : 02:46:53

Silly question, I reworked the query and ended up with a solid working module for my user. Thanks for your help everyone! Here is the query with the changes...

select convert(varchar(10), sec / (24 * 60 * 60)) + ' days ' +
convert(varchar(2), sec / (60 * 60) % 24) + ' hours ' +
convert(varchar(2), (sec / 60) % 60) + ' mins ' +
convert(varchar(2), sec % 60) + ' secs remaining'
from
(
select datediff(second, getdate(), dateadd(day, 7, datecreated)) as sec
from
(
select datecreated FROM table_name WHERE this condition
) c
) s

Thanks again KHTAN and MVJ!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-28 : 02:49:41

you can remove the derived table c and simplify into

select 	convert(varchar(10), sec / (24 * 60 * 60)) + ' days ' +
	convert(varchar(2), sec / (60 * 60) % 24) + ' hours ' +
	convert(varchar(2), (sec / 60) % 60) + ' mins ' +
	convert(varchar(2), sec % 60) + ' secs remaining'
from
(
	select datediff(second, getdate(), dateadd(day, 7, datecreated)) as sec
	FROM 	table_name 
	WHERE 	this condition
) s

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources