Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

countdown - subtract date times

Posted on 2011-02-25
3
Medium Priority
?
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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