September 27, 2011 at 9:11 pm
Hi
I am trying to find the first day of a week/month
Example
SELECT DATEADD(wk, DATEDIFF(wk, 6, '2011-09-02'), 7)
Result: 2011-08-29 00:00:00.000
I need it to be 2011-09-01 00:00:00.000
Because i am trying to find the first day of the week based on the month
Any help would be appreciated
September 27, 2011 at 9:32 pm
Hmm. What do you mean? September 1st is Thursday, not the first day of the week.
Day of week would be like this:
SELECT CASE DATEPART(WEEKDAY, '20110901')
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END;
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
September 27, 2011 at 10:10 pm
Hi andre,
Totally agree with you
I might have not been clear in my previous post
This is just a part of my query
SELECT 'Week' + CONVERT(VARCHAR(8),DATEDIFF(WEEK, DATEADD(MONTH, DATEDIFF(MONTH, 0, '2011-09-02'), 0), '2011-09-02') +1)
+ '/' + DATENAME(MONTH,'2011-09-02') + CONVERT(VARCHAR(8),YEAR('2011-09-02')) AS WeekOfTheMonth,
DATEADD(wk, DATEDIFF(wk, 6, '2011-09-02'), 7) AS FirstoftheWeek
when you run this you would get Week1/September2011 for WeekOfTheMonth and 2011-08-29 00:00:00.000 for FirstoftheWeek
based on my criteria
29/08/2011 - 31/08/2011 is week5/August2011
01/09/2011 - 04/09/2011 is Week1/September2011
05/09/2011 - 11/09/2011 is Week2/September2011
12/09/2011 - 18/09/2011 is Week3/September2011
19/09/2011 - 25/09/2011 is Week4/September2011
26/09/2011 - 30/09/2011 is Week5/September2011
now as you see 02/09/2011 has to have the first day of the week as 01/09/2011
September 27, 2011 at 10:17 pm
You can write a CASE statement and return the greatest among the First Date Of The Month and the First Date Of The Week
DECLARE@from_date DATETIME
SET@from_date = '02-Sep-2011'
SELECTCASE
WHEN DATEADD( MONTH, DATEDIFF( MONTH, 0, @from_date ), 0 ) > DATEADD( WEEK, DATEDIFF( WEEK, 0, @from_date ), 0 )
THEN DATEADD( MONTH, DATEDIFF( MONTH, 0, @from_date ), 0 )
ELSE DATEADD( WEEK, DATEDIFF( WEEK, 0, @from_date ), 0 )
END
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 27, 2011 at 10:55 pm
Hi kingston,
Thats spot on
and very clever
should have though about that
the other thing which i noticed is
when i am trying to find the firstday of the week for 04/09/2011 it comes up as 05/09/2011 instead of 01/09/2011
can you also help me with that
thanks in advance champ
September 27, 2011 at 11:13 pm
8reat1s (9/27/2011)
Hi kingston,Thats spot on
and very clever
should have though about that
the other thing which i noticed is
when i am trying to find the firstday of the week for 04/09/2011 it comes up as 05/09/2011 instead of 01/09/2011
can you also help me with that
thanks in advance champ
Is Sunday the First Day or Last Day of your week?
It appears the SQL is treating it as the first day. In which case, this change will fix that.
SELECT CASE
WHEN DATEADD( MONTH, DATEDIFF( MONTH, 0, @from_date ), 0 ) > DATEADD( WEEK, DATEDIFF( WEEK, 0, @from_date ), 0 )
THEN DATEADD( MONTH, DATEDIFF( MONTH, 0, @from_date ), 0 )
ELSE DATEADD( WEEK, DATEDIFF( WEEK, 0, @from_date ), -1 )
END
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 27, 2011 at 11:29 pm
Monday is the first day of the week
and sunday is the last day of the week in my query
what do i do in that case??
September 28, 2011 at 12:03 am
You can use 'set datefirst 7' to set sunday is the first date of a week in you sql-query
September 28, 2011 at 12:05 am
sunday is supposed to be the last day of the week
September 28, 2011 at 12:08 am
This should work for you then..
DECLARE @from_date DATETIME
SET @from_date = '11-Sep-2011'
SELECT CASE
WHEN DATEADD( MONTH, DATEDIFF( MONTH, 0, @from_date ), 0 ) > DATEADD( WEEK, DATEDIFF( WEEK, 0, @from_date - 1 ), 0 )
THEN DATEADD( MONTH, DATEDIFF( MONTH, 0, @from_date ), 0 )
ELSE DATEADD( WEEK, DATEDIFF( WEEK, 0, @from_date - 1 ), 0 )
END
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 28, 2011 at 12:10 am
It appears there is an issue with how SQL handles dates that land on Sunday. Here is a klugy workaround.
DECLARE @from_date DATETIME
--Set DateFirst 1 --tried this but it had no effect
SET @from_date = '11-Sep-2011'
SELECT CASE
WHEN DATEADD( MONTH, DATEDIFF( MONTH, 0, @from_date ), 0 ) > DATEADD( WEEK, DATEDIFF( WEEK, 0, @from_date ), 0 )
THEN DATEADD( MONTH, DATEDIFF( MONTH, 0, @from_date ), 0 )
ELSE DATEADD( wk, DATEDIFF( wk, 0, case when datepart(dw,@from_date) = 7 then dateadd(d,-1,@from_date) else @from_date end ), 0 )
END
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 28, 2011 at 12:11 am
yubo1 (9/28/2011)
You can use 'set datefirst 7' to set sunday is the first date of a week in you sql-query
That wasn't working in this query. Only Sunday is affected by this.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 28, 2011 at 12:13 am
Kingston Dhasian (9/28/2011)
This should work for you then..
DECLARE @from_date DATETIMESET @from_date = '11-Sep-2011'
SELECT CASE
WHEN DATEADD( MONTH, DATEDIFF( MONTH, 0, @from_date ), 0 ) > DATEADD( WEEK, DATEDIFF( WEEK, 0, @from_date - 1 ), 0 )
THEN DATEADD( MONTH, DATEDIFF( MONTH, 0, @from_date ), 0 )
ELSE DATEADD( WEEK, DATEDIFF( WEEK, 0, @from_date - 1 ), 0 )
END
This works quite well.
Still odd that the only day affected is Sunday.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 28, 2011 at 12:19 am
That works like a charm
Thanks for your help
September 28, 2011 at 12:22 am
You're welcome 😎
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply