Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

countdown till day of the week...mssql server 2005

Posted on 2011-02-25
4
Medium Priority
?
331 Views
Last Modified: 2012-05-11
If I have a curr_date...how do I subtract a future date

so that it would return '3 days: 15hours: 24 seconds"

so if tday is Friday feb 22, 2011...and the next process date is tomorrow sat at noon..

'1 day: 2 hours: 34 seconds'
0
Comment
Question by:GlobaLevel
  • 2
4 Comments
 
LVL 4

Accepted Solution

by:
rocky_lotus_newbie earned 2000 total points
ID: 34982652
DECLARE @Startdate DATETIME, @Enddate DATETIME
SET @Startdate = '2011-01-02 11:35:26'
SET @Enddate = '2011-01-06 03:15:31'

-- Query by SqlServerCurry.com
-- Total seconds in a day
DECLARE @TotalSec int
SET @TotalSec = 24*60*60;

-- Convert DateDiff into seconds
DECLARE @DiffSecs int
SET @DiffSecs = DATEDIFF(SECOND, @Startdate, @Enddate)

SELECT
CONVERT(char(2), (@DiffSecs/@TotalSec))as [Days],
CONVERT(char(2), ((@DiffSecs%@TotalSec)/3600)) as [Hours],
CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)/60)) as [Minutes],
CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)%60)) as [Seconds]

Link: http://www.sqlservercurry.com/2011/01/date-difference-in-sql-server-in-days.html
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 34982726
Use the TIMEDIFF function. In order to determine the time between now and feb 25, 2011 at 1:15:23 AM, you'd use

select timediff(now(), 20110225011523)
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 34983149
rocky...

great code....

just trying to mod yours into the below...

DECLARE @NEXT_DELIVERY_DATE NVARCHAR(MAX)
--
SET @NEXT_DELIVERY_DATE = 'SCHEDULED FOR THIS ' + @ACTUAL_DAY + ' : ' + @DAYS + ' Days ' + @HOURS + ' Hours' +
@MIN + ' Mins ' + @SEC + ' Secs LEFT TILL LAUNCH'
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 34983286
got it...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

824 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