calculating a date in the future

Question

calculating a date in the future

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

a20213 Say Hey Kid Points: 664 More actions · Answer 1

Thank you Sean, i was indeed browsing that section later, but didn't went deep to 2009 )

I got another doubt, i don't know if its best to say it it or open a new thread, since the subject is again datetime calculations.

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Unless it is a totally different thing I would think in this thread is fine. You already have a couple people following this one anyway. 🙂

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

a20213 Say Hey Kid Points: 664 More actions · Answer 3

i was searching for a solution for this new problem and found it ... it was about merging datetime intervals into a bigger one, it seemed simple when i started implemeting, but it was not.

my problem is best described here:

trying to understand all this :hehe:

ScottPletcher SSC Guru Points: 101099 More actions · Answer 4

None of that is necessary: a simple calculation can give you the future date you need.

The code below assumes you want a future matching-day date on or after the months-added date. So, adding 12 mos gives a date of '2014-11-05', which becomes '2014-11-11', since '2014-11-04' would be earlier.

If you want the first future matching-day date on or before the months-added date, just remove the "DATEADD(DAY, 6, ...)" from the code below. Then '2014-11-05' would return '2014-11-04'.

DECLARE @dt DATETIME = '20131105'

SELECT

#months,

future_date,

DATEADD(DAY, -DATEDIFF(DAY, curr_day_of_week, future_date) % 7, future_date) AS future_Tues_date

FROM (

--check 3 different future dates: 6, 12 and 18 mths out

SELECT 6 AS #months UNION ALL

SELECT 12 AS #months UNION ALL

SELECT 18 AS #months

) AS #months

CROSS APPLY (

SELECT

DATEDIFF(DAY, 0, @dt) % 7 AS curr_day_of_week,

DATEADD(DAY, 6, DATEADD(MONTH, #months, @dt)) AS future_date

) AS test_data

Edit: The code also assumes the future date should be on the same day of week as the @dt passed in. If, instead, you always want the future date to be a Tues, force "curr_day_of_week" to always be 1.

Edit2: Corrected "adding 6 mos" to be "adding 12 mos".

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

a20213 Say Hey Kid Points: 664 More actions · Answer 5

Perfect Mr. Scott.

Indeed, the same day of week has to be the same, mandatory.

PS: When you say "So, adding 6 mos gives a date of '2014-11-05', which becomes '2014-11-11', since '2014-11-04' would be earlier.", didn't you wanted to say ".. which becomes 2014-05-06" ?

this was the results after running the SQL without any modifications

#months future_date future_Tues_date

----------- ----------------------- -----------------------

6 2014-05-11 00:00:00.000 2014-05-06 00:00:00.000

12 2014-11-11 00:00:00.000 2014-11-11 00:00:00.000

18 2015-05-11 00:00:00.000 2015-05-05 00:00:00.000

ScottPletcher SSC Guru Points: 101099 More actions · Answer 6

a20213 (11/7/2013)
Perfect Mr. Scott.
Indeed, the same day of week has to be the same, mandatory.
PS: When you say "So, adding 6 mos gives a date of '2014-11-05', which becomes '2014-11-11', since '2014-11-04' would be earlier.", didn't you wanted to say ".. which becomes 2014-05-06" ?
this was the results after running the SQL without any modifications
#months future_date future_Tues_date
----------- ----------------------- -----------------------
6 2014-05-11 00:00:00.000 2014-05-06 00:00:00.000
12 2014-11-11 00:00:00.000 2014-11-11 00:00:00.000
18 2015-05-11 00:00:00.000 2015-05-05 00:00:00.000

Yes, sorry, that should of been "adding 12 mos gives a date ...".

And technically "future_Tues_date" should be "future_Same_Day_of_Week_Date" :-).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

a20213 Say Hey Kid Points: 664 More actions · Answer 7

Thank you, this is already defined in the table value functions 🙂