November 5, 2013 at 9:37 am
hello,
i was trying to find dates in the future when a few doubts arise
with this sql i can calculate, lets say all tuesdays starting from today till 2015
declare @dt datetime =N'2013-11-05'
;with data_sequencia as (
select cast(@dt as datetime) as dt
union all
select DATEADD(WEEK, 1,dt)
from data_sequencia where DATEPART(YEAR, DATEADD(WEEK, 1, dt)) <= 2015)
now, i want to look for some dates, can use something like
select dt from data_sequencia where dt .... OPTION (maxrecursion 0)
but what if i don't want to calculate all dates, but just "today" in the next year (2014-11-04 - Tuesday) ?
i found that simply adding to the date +52 weeks DATEPART(WEEK, 52, @dt) i get the date i want, but if i add +1 YEAR i get 2014-11-05 - Wednesday. Its safe to assume that adding 52 or multiples i find the right dates in the future for the test date ? And, or if i just want the test date +6 months ... how to calculate what day will be 6 months from now and it has to be a tuesday.
November 5, 2013 at 9:46 am
a20213 (11/5/2013)
hello,i was trying to find dates in the future when a few doubts arise
with this sql i can calculate, lets say all tuesdays starting from today till 2015
declare @dt datetime =N'2013-11-05'
;with data_sequencia as (
select cast(@dt as datetime) as dt
union all
select DATEADD(WEEK, 1,dt)
from data_sequencia where DATEPART(YEAR, DATEADD(WEEK, 1, dt)) <= 2015)
now, i want to look for some dates, can use something like
select dt from data_sequencia where dt .... OPTION (maxrecursion 0)
but what if i don't want to calculate all dates, but just "today" in the next year (2014-11-04 - Tuesday) ?
i found that simply adding to the date +52 weeks
DATEPART(WEEK, 52, @dt)i get the date i want, but if i add +1 YEAR i get 2014-11-05 - Wednesday. Its safe to assume that adding 52 or multiples i find the right dates in the future for the test date ? And, or if i just want the test date +6 months ... how to calculate what day will be 6 months from now and it has to be a tuesday.
I guess that depends on how you classify 6 months from now and which Tuesday do you want? DATEADD(month, 6, getdate()) is 6 months from now. It sounds like doesn't work in this case. So given that adding 6 months to today (11/5/2013) results in May 5th, 2014 (which is a Saturday), which Tuesday do you want? The one before or the one after?
Depending on how you define a month this could change too. Maybe a month is 4 weeks? In that case, just add 24 weeks and it will always be a Tuesday.
_______________________________________________________________
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/
November 5, 2013 at 9:50 am
Be careful on using recursive CTEs to count as you did. Those won't perform fine and might be worse than a cursor as stated on this article: http://www.sqlservercentral.com/articles/T-SQL/74118/
Another point is that adding 52 weeks is the same as adding 364 days but it's not the same as adding a year or a year minus one day (because of leap years).
As Sean pointed out, it depends on which rules would you follow to calculate your dates.
November 5, 2013 at 11:38 am
a20213 (11/5/2013)
@Sean, are you sure 5th May is a Saturday ? in this case i want to May 6th 2014.Its allright to assume 4 weeks each month ?
hehe Nope, May 5th is Monday. Why May 6th then? You want the first Tuesday following 6 months?
I don't know if it is ok to assume 4 weeks in each month. That would be a business rule/decision you will have to make.
_______________________________________________________________
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/
November 5, 2013 at 11:49 am
i want May 6th because its the first tuesday of May, like today is the first tuesday of November, its the most "logical" day, just i have a feeling that this cannot be used as a straightforward rule.
November 5, 2013 at 1:40 pm
a20213 (11/5/2013)
i want May 6th because its the first tuesday of May, like today is the first tuesday of November, its the most "logical" day, just i have a feeling that this cannot be used as a straightforward rule.
So you want the first Tuesday of the month for 6 months from now?
_______________________________________________________________
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/
November 5, 2013 at 1:46 pm
You might want to take a look at this article from Lynn. It has lots of datetime routines. You are going to need to utilize a number of these once you determine what you actually want.
_______________________________________________________________
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/
November 5, 2013 at 1:49 pm
in this case, yes, but my goal is to make it calculate for any kind of date and delta future (1 month, 2 month, ... 1 and 1/2 years).
November 5, 2013 at 2:07 pm
a20213 (11/5/2013)
in this case, yes, but my goal is to make it calculate for any kind of date and delta future (1 month, 2 month, ... 1 and 1/2 years).
It is extremely unclear what you are trying to do here. Are you trying to come up with some sort of function that will calculate any kind of date with any type of parameter?
Just to clarify, how would you figure out 1 1/2 years? Is that 18 months? Is that (365 * 2) / 1.5 days? How do you figure out 1 1/2 years when one of them is a leap year?
I am happy to help you figure out something but you have to have some rules for what you want. What exactly are you trying to do? What is the purpose? How do you envision calling this?
_______________________________________________________________
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/
November 5, 2013 at 2:19 pm
Sean Lange (11/5/2013)
a20213 (11/5/2013)
in this case, yes, but my goal is to make it calculate for any kind of date and delta future (1 month, 2 month, ... 1 and 1/2 years).It is extremely unclear what you are trying to do here. Are you trying to come up with some sort of function that will calculate any kind of date with any type of parameter?
Just to clarify, how would you figure out 1 1/2 years? Is that 18 months? Is that (365 * 2) / 1.5 days? How do you figure out 1 1/2 years when one of them is a leap year?
I am happy to help you figure out something but you have to have some rules for what you want. What exactly are you trying to do? What is the purpose? How do you envision calling this?
in bold what i want to do. the parameter is @NumberOfMonths ... a natural number. 1 1/2 years its 18 months, my mistake for mentioned, should have just stay with months nomenclature.
the idea of this is replicate appointments, tasks in a scheduler.
You mentioned an article from Lynn, but i cannot see it.
November 5, 2013 at 6:19 pm
First let me share a small utility function that could help to make short work of this problem:
CREATE FUNCTION [dbo].[GenerateCalendar]
(
@FromDate DATETIME,
@NoDays INT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== High speed code provided courtesy of Jeff Moden (idea by Dwain Camps)
--===== Generate sequence numbers from 1 to 65536 (credit to SQL Guru Itzik Ben-Gen)
WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1), --2 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --4 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --16 rows
E8(N) AS (SELECT 1 FROM E4 a, E4 b), --256 rows
E16(N) AS (SELECT 1 FROM E8 a, E8 b), --65536 rows
cteTally(N) AS (SELECT TOP (ABS(@NoDays)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16)
SELECT [SeqNo] = t.N,
[Date] = dt.DT,
[Year] = dp.YY,
[YrNN] = dp.YY % 100,
[YYYYMM] = dp.YY * 100 + dp.MM,
[BuddhaYr] = dp.YY + 543,
[Month] = dp.MM,
[Day] = dp.DD,
[WkDNo] = DATEPART(dw,dt.DT),
[WkDName] = CONVERT(NCHAR(9),dp.DW),
[WkDName2] = CONVERT(NCHAR(2),dp.DW),
[WkDName3] = CONVERT(NCHAR(3),dp.DW),
[JulDay] = dp.DY,
[JulWk] = dp.DY/7+1,
[WkNo] = dp.DD/7+1,
[Qtr] = DATEPART(qq,dt.Dt),
[Last] = (DATEPART(dd,dp.LDtOfMo)-dp.DD)/7+1,
[LdOfMo] = DATEPART(dd,dp.LDtOfMo),
[LDtOfMo] = dp.LDtOfMo
FROM cteTally t
CROSS APPLY ( --=== Create the date
SELECT DT = DATEADD(dd,(t.N-1)*SIGN(@NoDays),@FromDate)
) dt
CROSS APPLY ( --=== Create the other parts from the date above using a "cCA"
-- (Cascading CROSS APPLY, Acourtesy of ChrisM)
SELECT YY = DATEPART(yy,dt.DT),
MM = DATEPART(mm,dt.DT),
DD = DATEPART(dd,dt.DT),
DW = DATENAME(dw,dt.DT),
Dy = DATEPART(dy,dt.DT),
LDtOfMo = DATEADD(mm,DATEDIFF(mm,-1,dt.DT),-1)
) dp
GO
Then the code you need should be as easy as this:
DECLARE @dt DATETIME = '2013-11-05'
,@Months INT = 18;
SELECT * -- examine the return results and keep only what you need
FROM dbo.GenerateCalendar(@dt, DATEDIFF(day, @dt, DATEADD(month, @Months, @dt)))
WHERE WkDName2 = 'Tu';
I have first added 18 months to the date provided (@dt) and converted that to the number of days in that period, which the GenerateCalendar FUNCTION needs to perform its magic.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 6, 2013 at 1:45 am
pretty neat function you did Dwain )), plenty of new sql strategy to learn and understand, thank you. By the way, you a small syntax error @ CREATE FUNCTION [dbo].[GenerateCalenda1].
November 6, 2013 at 3:32 am
a20213 (11/6/2013)
pretty neat function you did Dwain )), plenty of new sql strategy to learn and understand, thank you. By the way, you a small syntax error @CREATE FUNCTION [dbo].[GenerateCalenda1].
Jeff Moden gets all of the credit for making it fast. Just my idea to build it in the first place.
I fixed that issue. Thanks for reporting it.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 6, 2013 at 7:14 am
You mentioned an article from Lynn, but i cannot see it.
Oops. Forgot to post the link. 😉
http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]
_______________________________________________________________
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/
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply