Solved

countdown - subtract date times

Posted on 2011-02-25
3
262 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:GlobaLevel
  • 2
3 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 34982443
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,colaesce(@curr_date,getdate())) as Diff) as x
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34984218
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
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34986192
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

0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now