Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Modify date to first day of week,month,quarter


Modify date to first day of week,month,quarter

Author
Message
loki1049
loki1049
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 301
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?
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6235 Visits: 10403
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
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6235 Visits: 10403
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
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4463 Visits: 9833
Hmm, not changing the DATEFIRST here - but I seem to get Sunday :-D


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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

scott.pletcher
scott.pletcher
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 473
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search