August 3, 2010 at 9:32 am
Greetings,
I am looking for a way to build a calendar table on the fly for the previous 3 weeks.
This calendar table will be like a table variable and this will be used for my actual query.
The result table should look like
Declare @CalTable Table (BeginDate varchar(8), EndDate varchar(8), PeriodEndingDate varchar(8))
insert into @CalTable
select '20100724','20100730','20100730' union all
select '20100717','20100723','20100723' union all
select '20100710','20100716','20100716'
select * from @CalTable
Please help!
August 3, 2010 at 9:42 am
UnionAll (8/3/2010)
Greetings,I am looking for a way to build a calendar table on the fly for the previous 3 weeks.
This calendar table will be like a table variable and this will be used for my actual query.
The result table should look like
Declare @CalTable Table (BeginDate varchar(8), EndDate varchar(8), PeriodEndingDate varchar(8))insert into @CalTable
select '20100724','20100730','20100730' union all
select '20100717','20100723','20100723' union all
select '20100710','20100716','20100716'
select * from @CalTable
Please help!
1. Does all your weeks start from Saturday (as in your samples) or it depends on the input current day?
2. What the difference between EndDate and PeriodEndingDate?
August 3, 2010 at 10:45 am
1. Does all your weeks start from Saturday (as in your samples) or it depends on the input current day?
2. What the difference between EndDate and PeriodEndingDate?
Yes, week always start from Saturday, Saturday to Friday is a week with Friday being the period end date.
EndDate and PeriodEndingDate are both the same. I just want to show them separetely.
The current week need not be reported, i always need prior 3 three weeks.
Thanks,
August 3, 2010 at 10:49 am
Is your EndDate always the same as the PeriodEndingDate? If not, when is it different and what should the value be?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 3, 2010 at 12:50 pm
drew.allen (8/3/2010)
Is your EndDate always the same as the PeriodEndingDate? If not, when is it different and what should the value be?Drew
Yes Drew, always same. Thanks.
August 4, 2010 at 6:06 am
Try:
declare @ReqDate datetime
set @ReqDate = '20100806' --GETDATE()
select LF-(7*wn)-(6) BeginDate, LF-(7*wn) EndDate, LF-(7*wn) PeriodEndingDate
from
(SELECT dateadd(dd, (DATEDIFF(dd,'1900-01-01',@ReqDate - 4) % 7 ) * -1, @ReqDate ) LF) d
cross join (values (0), (1), (2)) w(wn)
order by w.wn
The above query run for @ReqDate = [Friday] will include the week finished on this Friday (OP didn't specify any requirements about it).
To not include this week, simply 1 day shold be deducted out of @ReqDate before using it in the query.
August 4, 2010 at 5:43 pm
Eugene Elutin (8/4/2010)
Try:
declare @ReqDate datetime
set @ReqDate = '20100806' --GETDATE()
select LF-(7*wn)-(6) BeginDate, LF-(7*wn) EndDate, LF-(7*wn) PeriodEndingDate
from
(SELECT dateadd(dd, (DATEDIFF(dd,'1900-01-01',@ReqDate - 4) % 7 ) * -1, @ReqDate ) LF) d
cross join (values (0), (1), (2)) w(wn)
order by w.wn
The above query run for @ReqDate = [Friday] will include the week finished on this Friday (OP didn't specify any requirements about it).
To not include this week, simply 1 day shold be deducted out of @ReqDate before using it in the query.
Even though dates are stored as real numbers, you really should be using DateAdd().
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 5, 2010 at 12:02 am
drew.allen (8/4/2010)
Even though dates are stored as real numbers, you really should be using DateAdd().Drew
Whether I agree with such claims or not, such claims should be accompanied by links and/or code to support the claims. 😉 Performance comparision code or justification by deprecation notice are two substantial ways to justify such claims. You should at least give a reason so someone can learn something new.
Let's see wha'cha got, Drew.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 5, 2010 at 2:48 am
drew.allen (8/4/2010)
Even though dates are stored as real numbers, you really should be using DateAdd().
Drew
Why? Is it faster (for three rows)? Is any condition where code will produce wrong results?
Looking into code, using dateadd will bring no much benefits, however it will make code more noodly or vermishelly 😀
August 5, 2010 at 7:35 am
Jeff Moden (8/5/2010)
drew.allen (8/4/2010)
Even though dates are stored as real numbers, you really should be using DateAdd().Drew
Whether I agree with such claims or not, such claims should be accompanied by links and/or code to support the claims. 😉 Performance comparision code or justification by deprecation notice are two substantial ways to justify such claims. You should at least give a reason so someone can learn something new.
Let's see wha'cha got, Drew.
From the following link: http://msdn.microsoft.com/en-us/library/ms180878.aspx#UsingOperatorswithDateandTimeDataTypes
Date and Time Arithmetic Operators
To add and subtract for all date and time data types, use DATEADD and DATEDIFF.
That's good enough reason for me. But you might also want to look at this link
http://www.devx.com/dbzone/Article/34594. It does require a free registration to read the full article.
There are several reasons why I think it's a good idea to use DATEADD and DATEDIFF.
* It makes it clear that you are manipulating dates, so it makes your code easier to understand.
* It doesn't depend on how the data is stored. MS could decide to change the way that datetime data is stored. DATEADD and DATEDIFF would continue to work while the simple arithmetic would fail.
* It automatically handles leap years. Using arithmetic leads to sloppy coding like adding 365 to add a year, which doesn't handle leap years.
* This one bears repeating. It doesn't depend on how the data is stored.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 5, 2010 at 10:32 am
drew.allen (8/5/2010)
...From the following link: http://msdn.microsoft.com/en-us/library/ms180878.aspx#UsingOperatorswithDateandTimeDataTypes
Date and Time Arithmetic Operators
To add and subtract for all date and time data types, use DATEADD and DATEDIFF.
...
You should read this article in full!
You will find section:
E. Using DATEPART and DATEADD to find the first and last days of a datepart
you will find how MS recommends to "Find the last day of the current month." :
--Find the last day of the current month.
SELECT DATEADD(month, DATEDIFF(month, 0, SYSDATETIME()) + 1, 0) - 1;
Yes, it uses DATEDIFF and DATEADD, but then simply substructs one!
😛
I love MS 😀
August 5, 2010 at 11:27 am
Back to the original question...
How does this work for you? (It might need some adjustment if running on a Sunday, but you should be able to work that out. If not, ask away!)
DECLARE @DateFirst TINYINT,
@StartDate DATETIME,
@Days TINYINT;
-- store off the current setting so it can be restored.
SET @DateFirst = @@DATEFIRST;
SET DATEFIRST 7;
-- get the number of day to retrieve to start at date of 3rd previous week
SET @Days = 21 + DatePart(WEEKDAY, GetDate());
-- strip time from date
SET @StartDate = DateAdd(DAY, DateDiff(DAY, 0, GetDate()), 0);
-- table variable to hold the dates
DECLARE @Dates TABLE (DateField DATETIME PRIMARY KEY CLUSTERED);
WITH TALLY (N) AS
(
SELECT TOP (@Days) ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM sys.objects
), CTEDates (DateField) AS
(-- convert to dates
SELECT DateAdd(DAY, -N+1, @StartDate)
FROM TALLY
)
-- get just 3 weeks worth of dates
INSERT INTO @Dates
SELECT TOP (21) DateField
FROM CTEDates
ORDER BY DateField;
-- restore settings
SET DATEFIRST @DateFirst;
SELECT * from @Dates;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 5, 2010 at 8:56 pm
drew.allen (8/5/2010)
To add and subtract for all date and time data types, use DATEADD and DATEDIFF.That's good enough reason for me.
I guess it's a matter of how you read something like that. It's true that you need DATEADD and the like to manipulate the DATE and TIME datatypes which means that things like DATEADD will, in fact, allow you to add and subtract for "all" date and time data types.
Is the use of the date functions a good idea. Yeah, probably. But without such a reason, it's unsubstantiated advice. Even with such a reason, I find it a whole lot ironic that MS posts and uses integer math in that same link as Eugene pointed out. 😉
There are several reasons why I think it's a good idea to use DATEADD and DATEDIFF.
* It makes it clear that you are manipulating dates, so it makes your code easier to understand.
* It doesn't depend on how the data is stored. MS could decide to change the way that datetime data is stored. DATEADD and DATEDIFF would continue to work while the simple arithmetic would fail.
* It automatically handles leap years. Using arithmetic leads to sloppy coding like adding 365 to add a year, which doesn't handle leap years.
* This one bears repeating. It doesn't depend on how the data is stored.
Drew
Then you really won't like me using modulo to calculate things like the last Friday of a given month. 😉
The real key here is that I agree with the advice you gave... just not the advice by itself. There are a whole lot of reasons why you might want to violate the very advice you and MS gave. That's why it's important to give the reasons for such advice. Better yet and when possible, code demonstrations are better than mere advice.
Anyway, thanks for humoring me and posting the links. My recommendation in the face of all of this is, "It Depends". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 5, 2010 at 9:13 pm
Heh... of course, how much are you actually going to trust an article that gives the following kind of bad advice??? :Whistling:
C. Searching for all datetime2 values in a day
•The following example shows how to search for all date and time values in a day.
Copy
-- Create a table that contains with the following dates:
-- The last date-time in 2005-04-06, '2005-04-06 23:59:59.9999999'
-- The first date-time in 2005-04-07, '2005-04-07 00:00:00.0000000'
-- The last date-time in 2005-04-07, '2005-04-07 23:59:59.9999999'
-- The first date-time in 2005-04-08, '2005-04-08 00:00:00.0000000'
CREATE TABLE #Search
(
MyDate datetime2
);
INSERT INTO #Search(MyDate)VALUES('2005-04-06 23:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-07 00:00:00.0000000');
INSERT INTO #Search(MyDate)VALUES('2005-04-07 23:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-08 00:00:00.0000000');
-- The following four SELECT statements show different ways to find
-- only the two rows that contain 2005-04-07 dates.
--Use CONVERT.
SELECT MyDate
FROM #Search
WHERE CONVERT(date,MyDate) = '2005-04-07';
--Use >= and <=.
SELECT MyDate
FROM #Search
WHERE MyDate >= '2005-04-07 00:00:00.0000000'
AND MyDate <= '2005-04-07 23:59:59.9999999';
--Use > and <.
SELECT MyDate
FROM #Search
WHERE MyDate > '2005-04-06 23:59:59.9999999'
AND MyDate < '2005-04-08 00:00:00.0000000';
--Use BETWEEN AND.
SELECT MyDate
FROM #Search
WHERE MyDate BETWEEN '2005-04-07 00:00:00.0000000'
AND '2005-04-07 23:59:59.9999999';
DROP TABLE #Search
GO
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply