Modify date to first day of week,month,quarter

 Author Message loki1049 SSC Veteran Group: General Forum Members Points: 205 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 formulaDATEADD(, (DATEDIFF(,0,@mydate), 0)``--Date with no timeDATEADD(day, (DATEDIFF(day,0,@mydate), 0)--First day of monthDATEADD(month, (DATEDIFF(month,0,@mydate), 0)--First day of quarterDATEADD(quarter, (DATEDIFF(quarter,0,@mydate), 0)--First day of yearDATEADD(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 believeSET DATEFIRST = 7--Setting up a test variableDECLARE @mydate DATETIMESET @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 1900SELECT datediff(week,0,@mydate)--Then we would add this to the reference date as beforeSELECT 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 variableDECLARE @mydate DATETIMESET @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 variableDECLARE @mydate DATETIMESET @mydate = '1900-01-08 05:23:26 PM'--Removing time component from dateSELECT (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 SSCrazy Eights Group: General Forum Members Points: 9777 Visits: 10569 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. WayneMicrosoft Certified Master: SQL Server 2008Author - SQL Server T-SQL RecipesIf 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 SSCrazy Eights Group: General Forum Members Points: 9777 Visits: 10569 You can first strip the time from the date you are working with.This works for me:`declare @mydate datetimeset @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. WayneMicrosoft Certified Master: SQL Server 2008Author - SQL Server T-SQL RecipesIf 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 SSCertifiable Group: General Forum Members Points: 7663 Visits: 9966 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 WilliamsProblems are opportunities brilliantly disguised as insurmountable obstacles.How to post questions to get better answers fasterManaging Transaction Logs scott.pletcher SSC-Addicted Group: General Forum Members Points: 460 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