Solved

countdown - subtract date times

Posted on 2011-02-25
3
271 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 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query Grouping Question Record Inserts 9 63
How come this XML node is not read? 3 53
Linked Server - SP with Param to VIew 7 29
T-SQL: Only Wanting One Record 8 60
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this article I will describe the Copy Database Wizard 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.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

734 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