February 4, 2015 at 9:20 am
Hi
I am trying to get the date of Tuesday from now of the past x Years
The below Query works fine for weeks
DECLARE @x INT
SET x= 53
SELECT CONVERT(VARCHAR, DATEADD(WW,-@x,DATEADD(DD, -((@@DATEFIRST + DATEPART(DW, GETDATE()) -3) % 7), GETDATE())) ,23)
The issue is with below query for any passed Number of Years
SET x = 7
CONVERT(VARCHAR,DATEADD(yy, -@x, DATEADD(D, -((@@DATEFIRST + DATEPART(DW, GETDATE()) -3) % 7), GETDATE())) ,23)
So can some one help me in getting past years Tuesday Date and also verify if week also works for any passed week numbers ? The x parameter can be changed based on the situation
February 4, 2015 at 10:01 am
I don't get exactly what you are trying to do but I know that a calendar table is helpful in this kind of situation. If you don't have one available you can use a tally table to create one on the fly.
This may help...
WITH
E1 AS (SELECT n = 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(n)),--10
E2 AS (SELECT n = 1 FROM E1 a CROSS JOIN E1 b),--100
iTally AS (SELECT n = row_number() over (order by (select null)) FROM E2 a CROSS JOIN E2 b),
cal_table AS
(SELECT
dtval = dateadd(D,n,cast('1/1/1990' AS date)),
wkdaynbr = datepart(weekday,dateadd(D,n,cast('1/1/1990' AS date))),
wkdaytext = datename(weekday,dateadd(D,n,cast('1/1/1990' AS date)))
FROM iTally
)
SELECT *
FROM cal_table
--WHERE wkdaynbr = 3;
-- Itzik Ben-Gan 2001
February 4, 2015 at 10:24 am
To be more clear i am looking to get date of Tuesday for the x parameter year
Example:
Assume x = 1 (One Year)
-->Today is 02/04/2015 Is Wednesday
-->I need to get Tuesday date of last years (02/04/2014)( As x = 1)
February 4, 2015 at 11:07 am
The Solution is as below
SELECT CONVERT(VARCHAR,DATEADD(yy, -@x, DATEADD(D, -((@@DATEFIRST + DATEPART(DW, DATEADD(yy,-@x ,GETDATE()) ) -3) % 7), GETDATE())) ,23)
February 4, 2015 at 1:04 pm
As a DBA, I dislike doing thousands of logical I/Os -- such as a calendar table -- when instead a simple mathematical calcs can yield the same results:
DECLARE @num_years int
SET @num_years = 7
--you could replace this cte with a tally table, if you have one
;WITH cteYearsAgo AS (
SELECT 1 AS years_ago UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9 UNION ALL
SELECT 10
)
SELECT
DATEADD(DAY, -DATEDIFF(DAY, known_base_Tuesday, years_ago_date) % 7,
years_ago_date) AS Prior_Tuesdays
FROM (
SELECT CAST(GETDATE() AS date) AS today,
1 AS known_base_Tuesday
) AS dates
INNER JOIN cteYearsAgo y ON
y.years_ago BETWEEN 1 AND @num_years
CROSS APPLY (
SELECT DATEADD(YEAR, -y.years_ago, today) AS years_ago_date
) AS assign_alias_names
Edit: Changed wording and split line(s) for readability.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 4, 2015 at 6:37 pm
RamSteve (2/4/2015)
The Solution is as belowSELECT CONVERT(VARCHAR,DATEADD(yy, -@x, DATEADD(D, -((@@DATEFIRST + DATEPART(DW, DATEADD(yy,-@x ,GETDATE()) ) -3) % 7), GETDATE())) ,23)
That's one solution but it relies on @@DATEFIRST which is subject to change based on selected language and other things. You also use the currently undocumented date format of 23 as well as the worst practice of a "naked" VARCHAR. It's also a bit long for what it does. While I have no problems with using undocumented features when necessary, it's just not necessary in this case.
Try the following, instead. It doesn't have any of the aforementioned problems.
SELECT CONVERT(CHAR(10),DATEADD(dd,DATEDIFF(dd,1,DATEADD(yy,-@Years,GETDATE()))/7*7,1),120)
;
--Jeff Moden
Change is inevitable... Change for the better is not.
February 5, 2015 at 8:08 am
The above script looks good 🙂 and how about not using the DATEFirst for the below code ? Thanks in advance
SELECT CONVERT(VARCHAR, DATEADD(WW,-@x,DATEADD(DD, -((@@DATEFIRST + DATEPART(DW, GETDATE()) -3) % 7), GETDATE())) ,23)
February 5, 2015 at 8:09 am
So the solution would be like like below
SELECT CONVERT(CHAR(10),DATEADD(dd,DATEDIFF(dd,1,DATEADD(DW,-@Years,GETDATE()))/7*7,1),120)
February 5, 2015 at 9:04 am
Jeff Moden (2/4/2015)
RamSteve (2/4/2015)
The Solution is as belowThat's one solution but it relies on @@DATEFIRST which is subject to change based on selected language and other things. You also use the currently undocumented date format of 23 as well as the worst practice of a "naked" VARCHAR. It's also a bit long for what it does. While I have no problems with using undocumented features when necessary, it's just not necessary in this case.SELECT CONVERT(VARCHAR,DATEADD(yy, -@x, DATEADD(D, -((@@DATEFIRST + DATEPART(DW, DATEADD(yy,-@x ,GETDATE()) ) -3) % 7), GETDATE())) ,23)
Try the following, instead. It doesn't have any of the aforementioned problems.
SELECT CONVERT(CHAR(10),DATEADD(dd,DATEDIFF(dd,1,DATEADD(yy,-@Years,GETDATE()))/7*7,1),120)
;
I believe the idea of the original code is that using @@DATEFIRST will adjust the value returned by DW (WEEKDAY) to return a consistent weekday value result regardless of the DATEFIRST setting.
To demonstrate:
DECLARE @x int
SET @x = 1
SET DATEFIRST 3
SELECT CONVERT(VARCHAR,DATEADD(yy, -@x, DATEADD(D, -((@@DATEFIRST + DATEPART(DW, DATEADD(yy,-@x ,GETDATE()) ) -3) % 7), GETDATE())) ,23)[/quote]
SET DATEFIRST 5
SELECT CONVERT(VARCHAR,DATEADD(yy, -@x, DATEADD(D, -((@@DATEFIRST + DATEPART(DW, DATEADD(yy,-@x ,GETDATE()) ) -3) % 7), GETDATE())) ,23)[/quote]
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 5, 2015 at 9:26 am
ScottPletcher (2/5/2015)
Jeff Moden (2/4/2015)
RamSteve (2/4/2015)
The Solution is as belowThat's one solution but it relies on @@DATEFIRST which is subject to change based on selected language and other things. You also use the currently undocumented date format of 23 as well as the worst practice of a "naked" VARCHAR. It's also a bit long for what it does. While I have no problems with using undocumented features when necessary, it's just not necessary in this case.SELECT CONVERT(VARCHAR,DATEADD(yy, -@x, DATEADD(D, -((@@DATEFIRST + DATEPART(DW, DATEADD(yy,-@x ,GETDATE()) ) -3) % 7), GETDATE())) ,23)
Try the following, instead. It doesn't have any of the aforementioned problems.
SELECT CONVERT(CHAR(10),DATEADD(dd,DATEDIFF(dd,1,DATEADD(yy,-@Years,GETDATE()))/7*7,1),120)
;
I believe the idea of the original code is that using @@DATEFIRST will adjust the value returned by DW (WEEKDAY) to return a consistent weekday value result regardless of the DATEFIRST setting.
To demonstrate:
DECLARE @x int
SET @x = 1
SET DATEFIRST 3
SELECT CONVERT(VARCHAR,DATEADD(yy, -@x, DATEADD(D, -((@@DATEFIRST + DATEPART(DW, DATEADD(yy,-@x ,GETDATE()) ) -3) % 7), GETDATE())) ,23)
SET DATEFIRST 5
SELECT CONVERT(VARCHAR,DATEADD(yy, -@x, DATEADD(D, -((@@DATEFIRST + DATEPART(DW, DATEADD(yy,-@x ,GETDATE()) ) -3) % 7), GETDATE())) ,23)[/quote]
[/code][/quote]
My mistake. You're correct of course. I do hate to see it being used, though, and will avoid it whenever I can. Thank you for the feedback, Scott.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 5, 2015 at 10:10 am
Jeff Moden (2/5/2015)
ScottPletcher (2/5/2015)
Jeff Moden (2/4/2015)
RamSteve (2/4/2015)
The Solution is as belowThat's one solution but it relies on @@DATEFIRST which is subject to change based on selected language and other things. You also use the currently undocumented date format of 23 as well as the worst practice of a "naked" VARCHAR. It's also a bit long for what it does. While I have no problems with using undocumented features when necessary, it's just not necessary in this case.SELECT CONVERT(VARCHAR,DATEADD(yy, -@x, DATEADD(D, -((@@DATEFIRST + DATEPART(DW, DATEADD(yy,-@x ,GETDATE()) ) -3) % 7), GETDATE())) ,23)
Try the following, instead. It doesn't have any of the aforementioned problems.
SELECT CONVERT(CHAR(10),DATEADD(dd,DATEDIFF(dd,1,DATEADD(yy,-@Years,GETDATE()))/7*7,1),120)
;
I believe the idea of the original code is that using @@DATEFIRST will adjust the value returned by DW (WEEKDAY) to return a consistent weekday value result regardless of the DATEFIRST setting.
To demonstrate:
DECLARE @x int
SET @x = 1
SET DATEFIRST 3
SELECT CONVERT(VARCHAR,DATEADD(yy, -@x, DATEADD(D, -((@@DATEFIRST + DATEPART(DW, DATEADD(yy,-@x ,GETDATE()) ) -3) % 7), GETDATE())) ,23)
SET DATEFIRST 5
SELECT CONVERT(VARCHAR,DATEADD(yy, -@x, DATEADD(D, -((@@DATEFIRST + DATEPART(DW, DATEADD(yy,-@x ,GETDATE()) ) -3) % 7), GETDATE())) ,23)
[/code][/quote]
My mistake. You're correct of course. I do hate to see it being used, though, and will avoid it whenever I can. Thank you for the feedback, Scott.[/quote]
I agree. I also very much dislike that technique, but it was popular for a while.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply