Solved

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

Posted on 2011-02-25
6
249 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
2 IIF's in Access query 25 44
SQL Use Distinct with two fields 3 14
query question 12 32
SQL Server Configuration Manager WMI Error 11 18
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

856 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