June 21, 2010 at 1:53 pm
What I'm trying to do, is given a certain datetime value (ex '2010-06-21 12:59:26 PM' ) convert this to a 'first day of period' with time component excluded. So if I wanted the first day of the month, I would get this:
'2010-06-01' from ( '2010-06-21 12:59:26 PM' )
Or if I wanted the first day of the quarter I would get this:
'2010-04-01' from ( '2010-06-21 12:59:26 PM' )
I'll start off by saying I have found a lot of very interesting ways to do this, but recently I found a new way which seems to make all of the rest look overly involved. You simply use 0 as a reference date and take your date and count the amount of periods from 0 to your date, then add those amount of periods to a 0 reference date. Simple enough, it seems to work. This would be the code:
--generic formula
DATEADD(<Period>, (DATEDIFF(<Period>,0,@mydate), 0)
--Date with no time
DATEADD(day, (DATEDIFF(day,0,@mydate), 0)
--First day of month
DATEADD(month, (DATEDIFF(month,0,@mydate), 0)
--First day of quarter
DATEADD(quarter, (DATEDIFF(quarter,0,@mydate), 0)
--First day of year
DATEADD(year, (DATEDIFF(year,0,@mydate), 0)
This method works on using the reference date of 0, which is '1900-01-01', but would work regardless even if microsoft changed this date in future instance of SQL server. I have seen some pretty extravagant ways to get the first day of the quarter including the following below, which I initially used:
CAST(YEAR(@mydate) AS VARCHAR(4)) +
CASE WHEN MONTH(@mydate) IN ( 1, 2, 3) THEN '/01/01'
WHEN MONTH(@mydate) IN ( 4, 5, 6) THEN '/04/01'
WHEN MONTH(@mydate) IN ( 7, 8, 9) THEN '/07/01'
WHEN MONTH(@mydate) IN (10, 11, 12) THEN '/10/01'
END
Seems like a mess compared to the first method.
Now the issue: This doesn't seem to work with using the 'week' period. My @@DATEFIRST variable is set to '7' which should make Sunday the first day of the given week, but using the first method described, it always seems to give the monday as the first day of the week. Lets look at an example for this:
--Tells SQL server that weeks begin on sunday and end on saturday
--This is default for english language I believe
SET DATEFIRST = 7
--Setting up a test variable
DECLARE @mydate DATETIME
SET @mydate = '1900-01-08'
--This will return that the given date is in the second week since the reference date '1900-01-01' i.e. in week of
--Jan 7(sunday)-13(saturday) of year 1900
SELECT datediff(week,0,@mydate)
--Then we would add this to the reference date as before
SELECT DATEADD(week, (DATEDIFF(week,0,@mydate), 0)
--which is in our case equivalent to DATEADD(week,1,0)
--You will find that the returned date is '1900-01-08' a monday, and not '1900-01-07' a sunday
This is because the reference date starts on a monday, and when you add periods for weeks they are added as instance of 7. So you will always have to subtract a day to get sunday as the first day. And worse off yet is if microsoft were to change the reference date to one that started on anything else other than a monday, you would have to modify your code to get it to work. The only way I could see to get this to work was using the following method
--Setting up a test variable
DECLARE @mydate DATETIME
SET @mydate = '1900-01-08'
--Here you get the expected '1900-01-07'
SELECT dateadd(day,-(datepart(dw,@mydate)-1),@mydate)
The only problem with the above code is that it does not set the 'time' component of the date to 0 as with the method initially described. You can hack off the time and then place that result into the above method but that gets messy:
--Setting up a test variable
DECLARE @mydate DATETIME
SET @mydate = '1900-01-08 05:23:26 PM'
--Removing time component from date
SELECT (CAST(FLOOR(CAST(@mydate AS FLOAT))AS DATETIME))
--Putting this into the method above in one step results in a rather disgusting select statement:
SELECT dateadd(day,-(datepart(dw,@mydate)-1), (CAST(FLOOR(CAST(@mydate AS FLOAT))AS DATETIME)) )
But it can be done regardless. Anyone know a different method of doing this? I would assume you could always add one more dateadd() function and subtract a single day from the result of " dateadd(day,-(datepart(dw,@mydate)-1), @mydate ) " but this unlike with the other periods, this would change if microsoft changed the reference date.
Suggestions?
June 21, 2010 at 2:06 pm
loki1049 (6/21/2010)
Now the issue: This doesn't seem to work with using the 'week' period.
DateDiff is counting the # of boundaries crossed. In the "datepart Boundaries" section, it implies that calandar weeks are used for the week datepart.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 21, 2010 at 2:16 pm
You can first strip the time from the date you are working with.
This works for me:
declare @mydate datetime
set @mydate = GETDATE()
SELECT DateAdd(day, -1, DATEADD(week, DATEDIFF(week,0,DATEADD(day, DateDiff(day,0,@mydate), 0)), 0))
Interesting issue. Too bad you have to double the number of calculations you're working with.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 21, 2010 at 2:43 pm
Hmm, not changing the DATEFIRST here - but I seem to get Sunday 😀
Declare @currentDate datetime
,@sundayDate datetime;
Set @currentDate = getdate()
Set @sundayDate = dateadd(week, datediff(week, -1, @currentDate), -1);
Select @sundayDate, datename(weekday, @sundayDate);
Set @currentDate = dateadd(day, -5, getdate());
Set @sundayDate = dateadd(week, datediff(week, -1, @currentDate), -1);
Select @sundayDate, datename(weekday, @sundayDate);
Now, if I want every Tuesday we change the seed date:
Declare @currentDate datetime
,@tuesdayDate datetime
,@seedDate datetime;
Set @seedDate = '20100105'; -- First Tuesday of this year
Set @currentDate = getdate()
Set @tuesdayDate = dateadd(week, datediff(week, @seedDate, @currentDate), @seedDate);
Select @tuesdayDate, datename(weekday, @tuesdayDate);
Set @currentDate = dateadd(day, 10, getdate());
Set @tuesdayDate = dateadd(week, datediff(week, @seedDate, @currentDate), @seedDate);
Select @tuesdayDate, datename(weekday, @tuesdayDate);
Should do it...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 21, 2010 at 2:59 pm
And worse off yet is if microsoft were to change the reference date to one that started on anything else other than a monday, you would have to modify your code to get it to work.
Nah. Just hard-code '1900-01-01' as the date instead of using 0 :-).
When I need a week to start on Sunday, I use Jan 01, 1950 as a base date, since it was a Sunday.
Hmm, not changing the DATEFIRST here - but I seem to get Sunday
That's because you used -1 as the base date, which would be 12/31/1899, which was a Sunday.
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply