Solved

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

Posted on 2011-02-25
6
246 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
Comment Utility
return in a varaible if possible..@next_weekday_date
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 10

Author Comment

by:GlobaLevel
Comment Utility
Im not sure that is working quite right....
0
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
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
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now