asked on

countdown - subtract date times

this is a toughy...

so when ever the sproc runs on user submit...it takes the current date - whatever go_hour is set to...
-----------
if the user presses button at 9:30PM...and go_hour is '1:00 AM' then
@countdown = '2:30'

as it will be 2 and half hours till process runs as scheduled at 1:00AM...
-----------
if the user presses button at 11:45AM...and go_hour is '2:00 PM' then
@countdown = '2:15'

as it will be 2 hours and fifteen minutes till process runs as scheduled at '2:00 PM' ...
-----------

--heres my code with errors.. : )

------------------
declare @GO_HOUR nvarchar(10)
Set @GO_HOUR = '1:00 AM'

DECLARE @CURR_DATE smalldatetime
SET @CURR_DATE = ISNULL(@CURR_DATE, getDATE())

declare @countdown nvarchar(10)
set @countdown = @GO_HOUR - @CURR_DATE

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lowfatspread

declare @GO_HOUR nvarchar(10)
Set @GO_HOUR = '1:00 AM'

DECLARE @CURR_DATE smalldatetime
SET @CURR_DATE = ISNULL(@CURR_DATE, getDATE())

declare @countdown nvarchar(10)

Select @countdown= right('00'+ convert(varchar(2), x.diff / 60),2)+':'+
right('00'+convert(varchar(2),x.diff % 60),2)
from (Select datediff(n,coalesce(@curr_date,getdate())) as Diff) as x

cyberkiwi

Just use date types

declare  @GO_HOUR  nvarchar(10)
Set @GO_HOUR  = '1:00 AM'

DECLARE @CURR_DATE smalldatetime
SET @CURR_DATE = ISNULL(@CURR_DATE, getDATE())

declare @countdown nvarchar(10)
set @countdown = convert(varchar(8),
    convert(datetime,@GO_HOUR)
   -
    dateadd(d,-datediff(d,0,@CURR_DATE),@CURR_DATE),8)

select @countdown

Open in new window