Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Modify date to first day of week,month,quarter Expand / Collapse
Author
Message
Posted Monday, June 21, 2010 1:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 10, 2012 12:19 PM
Points: 103, 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?
Post #940612
Posted Monday, June 21, 2010 2:06 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:26 PM
Points: 6,546, Visits: 8,770
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
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
Post #940617
Posted Monday, June 21, 2010 2:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:26 PM
Points: 6,546, Visits: 8,770
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
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
Post #940622
Posted Monday, June 21, 2010 2:43 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:17 AM
Points: 4,379, Visits: 9,470
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #940637
Posted Monday, June 21, 2010 2:59 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 31, 2010 9:46 AM
Points: 274, 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
Post #940647
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse