Solved

project future date based on past...ms sql server 2005

Posted on 2011-02-25
6
248 Views
Last Modified: 2012-06-27
If I have a date....based in the past...and its on a tuesday...based on a current date...
I need to get the next weekday date in this form:

Past: 2011-02-11 00:00:00 ----Tueday
Current: 2011-02-21 00:00:00
Next reoccuring weekday: 2011-02-24 00:00:00 ---next tuesday after current date...

---another example--hypothetical;

Past: 2011-01-11 00:00:00 ---- Monday
Current: 2011-01-21 00:00:00
Next reoccuring weekday: 2011-01-26 00:00:00 ---next Monday  after current date...


0
Comment
Question by:GlobaLevel
  • 2
  • 2
  • 2
6 Comments
 
LVL 10

Author Comment

by:GlobaLevel
ID: 34983375
return in a varaible if possible..@next_weekday_date
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34983818

declare @DateNext datetime

set @DateNext = DATEADD(DAY, 7,  '2011-02-11 00:00:00')
while @DateNext < GETDATE()
  set @DateNext =  DATEADD(DAY, 7,  @DateNext)

select @dateNext
0
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 34983831
or

declare @DateNext datetime
declare @Current datetime
set @Current = '2011-02-21 00:00:00'
set @DateNext = DATEADD(DAY, 7,  '2011-02-11 00:00:00')

while @DateNext < @Current
  set @DateNext =  DATEADD(DAY, 7,  @DateNext)

select @dateNext
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 10

Author Comment

by:GlobaLevel
ID: 34984009
Im not sure that is working quite right....
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34986237
The question has some issues

Past: 2011-02-11 00:00:00 ----Tueday
Next reoccuring weekday: 2011-02-24 00:00:00 ---next tuesday after current date...
(13 days apart...)

Past: 2011-01-11 00:00:00 ---- Monday
Current: 2011-01-21 00:00:00
Next reoccuring weekday: 2011-01-26 00:00:00 ---next Monday  after current date...
(25 days apart...)

Is the question supposed to be valid?

You can use this from a table

select NextDate = convert(datetime, datediff(d,0,getdate()) + 6
                                  - datediff(d, pastdate, getdate()-1) % 7)
from sometable

Open in new window


Or from variables

select convert(datetime, datediff(d,0,@current) + 6
                       - datediff(d, @past, @current-1) % 7)

Open in new window

0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34986243
Given the data

past = Tues
current = Tues

The previous question returns Next = current (already Tues). The below will return Tues next week.

select convert(datetime, datediff(d,0,getdate()) + 7 
                       - datediff(d, pastdate, getdate()) % 7)
from past

Open in new window

0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

770 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