locked
Start week of the month for a given week RRS feed

  • Question

  • Good mornig!

    May any body help me?

    I need a Select sentence that return me the first week of the month for a given week.

    For example If I have week number 12 (Begins 2015/03/16 and Ends 2015/03/22) I need that returns 9, I mean Week number 9 wich is the first week of march (having in mind @@DATEFIRST).

    I only need give a week number of the year and then returns the week number of the first week of that month.

    Thanks a lot for your valuable help

    Regards,

    Jamesit0.


    Jamesit0

    Thursday, July 23, 2015 5:10 PM

Answers

  • Check out this article on calendar tables

    ;WITH weekNumber AS (
    select ROW_NUMBER() OVER (PARTITION BY year ORDER BY today) AS weekNumber, *
      from calendar
     WHERE today = weekStart
    )
    
    SELECT *
      FROM weekNumber
     WHERE weekNumber = 12
       AND year = 2015



    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.


    • Edited by Patrick Hurst Thursday, July 23, 2015 6:24 PM
    • Proposed as answer by disssss Thursday, July 23, 2015 9:10 PM
    • Marked as answer by Jamesit0 Friday, July 24, 2015 3:34 AM
    Thursday, July 23, 2015 6:22 PM
  • >> First I didn't know that I may vote diferent answers if they were partial I always voted only the right answer.

    Voting is done with the link in the left of the message. Marking as answer is done with the links in the bottom of the message :-) Moreover if thread contain more then one answer then you can mark several answers.

    >> Second, my point is that my data can't depend from culture I mean if a user is from France he must have the same result as other from (in my case) Colombia.

    Absolutely right!

    This is exactly my point as well and my solutions. I work a lot with nulti-language databases and this is just simple case :-)

    check my example above. I loop 1-8 which mean any culture that there is (there are only 7 days so each culture can use different day as the starting day of the week). Yet my solution in the last column return the same value for all.

    We need you basic logic first day of the week for the math, but any user in any culture will see the same result if you will use my solution which are Deterministic :-) I just need to know what is the ONE SOLUTION that you want everyone to see. and this ONE SOLUTION is depend on your choose of what you see as the first day of the week.

    PLEASE CHECK THE EXAMPLE ABOVE AND EXECUTE IT with several dates until you understand the behavior :-)

    >> but arithmetic must be the same

    true, but we are not talking about arithmetic. we are talking about logic and configuration. arithmetic is for example using DATDIFF and this is why I implement DATEPART using DATDIFF. 2-1 is arithmetic, but saying that today (2015-0724) we are in the 6 day in the week is not arithmetic! It is configuration. for some people we are now at the 6th day in the week and for others we are in the first day of the week. It is depending on the culture.

    >> I may store the first day of the week as a parameter.

    This is an option that I used long time ago (I think that My CMS app that I developed in 2001 used this option). It will work but you dont need it and you can design your database better without this limitation, after all you might want to use different day of the week tomorrow or you will have 100k users that use 7 different "first days of the week". Dont limit your self by design! your database today can be the base for 10k other application in the future and in some cases the same database uses for several applications and each will need different "first day of the week". SO you will stoire them all in table?!?

    This is an option :-)
    A simple option but I dont like this solution for this case :-))
    Since we can use simple arithmetic which is as you said pure Deterministic

    * in the current case keeping the parameter hard-coded in the database is better then calendar table, I think. But you will need calendar table as mentioned for other things

    ** everything that I say here is just "in general" and I can not suggest you what to do, since I do not have the full specification of your system.

    >> Thanks a lot again for your valuable helps.

    You are most welcome :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    • Marked as answer by Jamesit0 Friday, July 24, 2015 9:17 PM
    Friday, July 24, 2015 3:26 PM

All replies

  • I need a Select sentence that return me the first week of the month for a given week.

    For example If I have week number 12 (Begins 2015/03/16 and Ends 2015/03/22) I need that returns 9


    Jamesit0

    What exactly are you wanting to do? Did you want to return the week number of the first week in the month?

    You can get that by getting the first day of the month and passing it to the datename function:

    SELECT DATENAME(wk, DATEFROMPARTS(YEAR('2015/03/16'), MONTH('2015/03/16'), 1)), DATENAME(wk, '2015/03/16')
    This takes '2015-03-16' and returns 9 as you requested. :)


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)




    Thursday, July 23, 2015 5:20 PM
  • Try this...

    DECLARE @SomeDate DATE = '2015-07-02'
    
    SELECT DATENAME(mm, DATEADD(dd, ((DATEPART(dw, @SomeDate) -1) * -1), @SomeDate))


    Jason Long

    Thursday, July 23, 2015 5:27 PM
  • Try this , this will provide you the firstday of the week  you pass .....

    for this query you need to pass the week number only

    SET DATEFIRST 1

    declare @wk int  set @wk = 30

    declare @yr int  set @yr = DAtePART(YYYY,GETDATE())

    declare @DT as datetime

    SELECT @DT= DATEADD (WEEK, @WK, DATEADD (YEAR, @YR-1900, 0)) - 4 -

             DATEPART(DW, DATEADD (WEEK, @WK, DATEADD (YEAR, @YR-1900, 0)) - 4) + 1

    SELECT  datepart (wk,CAST(CAst(datepart(yyyy,@DT) as varchar(4))+'-'+cast(datepart(mm,@DT ) as varchar(2))+'-01' as datetime))

    • Edited by Amit mishra Thursday, July 23, 2015 5:49 PM
    Thursday, July 23, 2015 5:41 PM
  • Good afternoon!

    Thanks Daniel Janik.)

    I need to provide the week number of the year and I need return the first week of the month from that year, i mean I don't need the number of the week in that month (1 to 4 or 1 to 5), I need return a number 1 to 52 or 53 but been the first week of the month of a given week number of the year.

    In my example y said if I give week number 12 I need return 9 wich is the first week of march corresponding with the month of a given week.


    In your example you are usig dates and I need to use TINYINTS for 1 to 52 or 53 weeks in the year.

    Thanks for your reply.


    Jamesit0

    Thursday, July 23, 2015 6:04 PM
  • Good afternoon!

    Thanks jason A Long, but I do not need dates numbers or work with dates. I need work with week numbers.

    In my example if I have week number 12 I need return week number 9 wich is the first week of the month for the given week 12.

    Regards,

    Jamesit0.


    Jamesit0

    Thursday, July 23, 2015 6:06 PM
  • Good afternoon!

    Thanks Daniel Janik.)

    I need to provide the week number of the year and I need return the first week of the month from that year, i mean I don't need the number of the week in that month (1 to 4 or 1 to 5), I need return a number 1 to 52 or 53 but been the first week of the month of a given week number of the year.

    In my example y said if I give week number 12 I need return 9 wich is the first week of march corresponding with the month of a given week.


    In your example you are usig dates and I need to use TINYINTS for 1 to 52 or 53 weeks in the year.

    Thanks for your reply.


    Jamesit0

    This does exactly that. It returns the week of the year the first week of the month for week 12. It works for 2012 and higher.

    SELECT DATENAME(wk, DATEFROMPARTS(YEAR('2015/03/16'), MONTH('2015/03/16'), 1)), DATENAME(wk, '2015/03/16')

    This one works for versions prior to 2012 (2008 R2 and below)

    SELECT DATENAME(wk, CONVERT(DATETIME, CONVERT(VARCHAR(12), YEAR('2015/03/16')) + '-' + CONVERT(VARCHAR(12), MONTH('2015/03/16'))  + '-' +  CONVERT(VARCHAR(12), 1))), DATENAME(wk, '2015/03/16')


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)




    Thursday, July 23, 2015 6:07 PM
  • Good afternoon Amit Mishra!

    Thanks for your reply but I do not need week days, I need work only with week numbers. I repeat if I give the week number 12 I need to return the first week of the month to which it belongs, in my exaple that number is 9 wich is the first week of march because the week entered (12) belongs to march.

    Thanks again,

    Regards,

    Jamesito.


    Jamesit0

    Thursday, July 23, 2015 6:10 PM
  • Goood afternoon Daniel Janik!

    Please forgive me because I'm too dummy!

    First you are right, I'm using SQL Server 2008 R2.

    Second you are working directly with dates, I mean "2015/03/16" and I do not have dates, I have week numbers "12"

    It does not work for me.

    Regards,


    Jamesit0

    Thursday, July 23, 2015 6:16 PM
  • Check out this article on calendar tables

    ;WITH weekNumber AS (
    select ROW_NUMBER() OVER (PARTITION BY year ORDER BY today) AS weekNumber, *
      from calendar
     WHERE today = weekStart
    )
    
    SELECT *
      FROM weekNumber
     WHERE weekNumber = 12
       AND year = 2015



    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.


    • Edited by Patrick Hurst Thursday, July 23, 2015 6:24 PM
    • Proposed as answer by disssss Thursday, July 23, 2015 9:10 PM
    • Marked as answer by Jamesit0 Friday, July 24, 2015 3:34 AM
    Thursday, July 23, 2015 6:22 PM
  • Good afternoon Daniel Janik!

    In your answer for SQL Server 2008 R2 we have a mistake because of the date format. If I run the script, I obtain week number 1 like this:

    Week

    Thanks,


    Jamesit0

    Thursday, July 23, 2015 6:30 PM
  • Good afternoon Patrick Hurst!

    That is a great article! I will study it next weekend because I will need it to work with holidays, but is there any option to my select with out storing data in the database because I am not afraid with holidays in this case?

    Regards,


    Jamesit0

    Thursday, July 23, 2015 6:33 PM
  • Good afternoon!

    Thanks jason A Long, but I do not need dates numbers or work with dates. I need work with week numbers.

    In my example if I have week number 12 I need return week number 9 wich is the first week of the month for the given week 12.

    Regards,

    Jamesit0.


    Jamesit0

    You may want to consider adding the year to that as well... The week number / month combos will vary from year to year.

    Also, what are your expected results for week number 1? The 1st week of 2015 started in 2014... So both 1 & 12 are legit answers depending on what you're after.


    Jason Long

    Thursday, July 23, 2015 6:34 PM
  • Good afternoon Daniel Janik!

    In your answer for SQL Server 2008 R2 we have a mistake because of the date format. If I run the script, I obtain week number 1 like this:

    Week

    Thanks,


    Jamesit0

    My month and your month are different thanks to the language. :)

    Try this:

    SELECT DATENAME(wk, CONVERT(DATETIME, DATENAME(mm, 'Mar 16 2015') + ' ' +  CONVERT(VARCHAR(12), 1) + ' ' + CONVERT(VARCHAR(12), YEAR('Mar 16 2015')))), DATENAME(wk, 'Mar 16 2015')



    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)



    Thursday, July 23, 2015 6:40 PM
  • Good afternoon Jason A Long!

    Thanks a lot for your reply!

    You are right! We will need the year too and I have that column too.

    In that case How can I obtain the first week of the month for a given week number and year number? I. E. If I have year number 2015 (SMALLINT) and week number of the year 12 (TINYINT), I need to obtain the week number in the year (9) that is the first week of the month wich belongs the given week 12 for tha given year 2015 in SQL Server 2008R2.

    Regards,


    Jamesit0



    • Edited by Jamesit0 Thursday, July 23, 2015 6:48 PM
    Thursday, July 23, 2015 6:40 PM
  • Good afternoon Daniel Janik!

    Thanks a lot for your reply...

    I repeat, I am too dummy, because of that I complemented my question like this:

    How can I obtain the first week of the month for a given week number and year number? I. E. If I have year number 2015 (SMALLINT) and week number of the year 12 (TINYINT), I need to obtain the week number in the year (9) that is the first week of the month wich belongs the given week 12 for tha given year 2015 in SQL Server 2008R2.

    In your example you are using months names. What about Greek when marchs is март? We can not use languages complexities in this case.

    Regards,


    Jamesit0


    • Edited by Jamesit0 Thursday, July 23, 2015 6:49 PM
    Thursday, July 23, 2015 6:43 PM
  • Good afternoon Daniel Janik!

    Thanks a lot for your reply...

    I repeat, I am too dummy, because of that I complemented my question like this:

    How can I obtain the first week of the month for a given week number and year number? I. E. If I have year number 2015 (SMALLINT) and week number of the year 12 (TINYINT), I need to obtain the week number in the year (9) that is the first week of the month wich belongs the given week 12 for tha given year 2015 in SQL Server 2008R2.

    Regards,


    Jamesit0

    I think this will get you there:

    SELECT DATENAME(wk, CONVERT(DATETIME, DATENAME(mm, 'Mar 16 2015') + ' ' +  CONVERT(VARCHAR(12), 1) + ' ' + CONVERT(VARCHAR(12), YEAR('Mar 16 2015')))), DATENAME(wk, 'Mar 16 2015')

    The problem with my first post was that the date was formatted for the US. So the month was out of line for you and it was reporting 1 because it was looking at January (2015-01-03) and not March (2015-03-01). It looks like the dates between our countries is reported differently.

    I hope that solves your issue!

    Also, for me in the US I show Feb 28 = week 9 and March 1 = week 10. This is probably due to @@Datefirst settings that you mentioned. So I think you're all set with the code I listed above.

    SELECT DATENAME(wk, 'Feb 28 2015'), DATENAME(wk, 'Mar 1 2015')


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)



    Thursday, July 23, 2015 6:51 PM
  • The optimal solution is to use a calendar table. With a calendar table, your query is as simple as the following...

    	SELECT 
    	c.monthname
    	FROM dbo.Calendar c
    	WHERE 
    		c.W = 12
    		AND c.DW = 1
    		AND c.Y = 2015

    Without the calendar table, you end up using something that looks like this...

    DECLARE 
    	@Year INT,
    	@WeekNum TINYINT;
    SELECT 
    	@Year = 2015,
    	@WeekNum = 12
    
    	SELECT DATENAME(mm, DATEADD(dd, (DATEPART(dw, DATEADD(wk, @WeekNum, DATEFROMPARTS(@Year, 1, 1))) -1) * -1, DATEADD(wk, @WeekNum, DATEFROMPARTS(@Year, 1, 1))))

    HTH,

    Jason


    Jason Long

    Thursday, July 23, 2015 6:58 PM
  • This will temporarily create the calendar table:

    DECLARE @startDate DATE = '2015-01-01'
    
    SELECT *
     INTO #cal
      FROM dbo.phDates(@startDate)
    WHILE (SELECT COUNT(*) FROM #cal) < 365
     BEGIN
     INSERT INTO #cal 
     SELECT *
       FROM dbo.phDates(DATEADD(DAY,(SELECT COUNT(*) FROM #cal),@startDate))
     END
    
    SELECT *
      FROM #cal
     ORDER BY today
    
    DROP TABLE #cal


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    Thursday, July 23, 2015 7:04 PM
  • You do not even know that a date is yyyy-mm-dd in ANSI/ISO Standard SQL. Sinc ethis is oneof the most common IT standards on Earth, why do you not know it? 

    Please read ISO-8601 and look at the week-date format. Google Calendar tables; you need one. Add this column to that table.  


    <SNIP>

    • Edited by pituachMVP Thursday, July 23, 2015 10:22 PM <SNIP>
    Thursday, July 23, 2015 7:16 PM
  • You do not even know that a date is yyyy-mm-dd in ANSI/ISO Standard SQL. Sinc ethis is oneof the most common IT standards on Earth, why do you not know it? 

    Please read ISO-8601 and look at the week-date format. Google Calendar tables; you need one. Add this column to that table.  


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Not sure who you're talking to on that one Joe.

    You may want to go back and actually read the code in the posts and then look at the results he got back. They don't match yyyy-mm-dd.  2015-03-01 came out as January 3 2015...


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Thursday, July 23, 2015 7:35 PM
  • Good afternoon friends!

    I made this solution:

    DECLARE @Year AS [INTEGER] = 2015;
    DECLARE	@WeekNum AS [TINYINT] = 12;
    
    SELECT
     DATEPART(WEEK,DATEADD(DAY, 1 - DATEPART(DAY,DATEADD(WEEK, @WeekNum - 1, RTRIM(@Year * 10000 + 1 * 100 + 1))), DATEADD(WEEK, @WeekNum - 1, RTRIM(@Year * 10000 + 1 * 100 + 1))))
    GO

    (Reading this post: Get Dates From A Week Number In T-Sql)

    May you tell me if am I right?

    Thanks a lot for every body for your valuable help.

    Regards,


    Jamesit0

    Thursday, July 23, 2015 8:38 PM
  • Daniel I think that you can ignore him. I am not sure that CELKO can read/write code... but obviously he has issue with politeness :-(


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]


    • Edited by pituachMVP Thursday, July 23, 2015 9:04 PM
    Thursday, July 23, 2015 9:04 PM
  • Good day Daniel,

    If I may post a small comment :-)
    You are doing the same in your new query

    try to execute it in different language and you will get error, since "Mar" for example fits English but not FRENCH or Hebrew:

    SET LANGUAGE FRENCH;
    GO
     
    SELECT 
    	DATENAME(wk, CONVERT(DATETIME, DATENAME(mm, 'Mar 16 2015') + 
    	' ' +  CONVERT(VARCHAR(12), 1) + ' ' + 
    	CONVERT(VARCHAR(12), YEAR('Mar 16 2015')))), DATENAME(wk, 'Mar 16 2015')
    GO -- error

    * I highly recommend to read this article: http://ariely.info/Blog/tabid/83/EntryId/161/Date-displaying-format-vs-Date-storing-format.aspx


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]


    • Edited by pituachMVP Thursday, July 23, 2015 9:20 PM
    Thursday, July 23, 2015 9:19 PM
  • DECLARE @Year AS INT = 2015;
    DECLARE	@WeekNum AS TINYINT = 12;
    DECLARE @FirstDayOfYear AS DATE
    
    SET @FirstDayOfYear = CAST(@Year AS CHAR(4)) + '-01-01'
    
    SELECT 
    @FirstDayOfYear AS FirstDayOfYear,
    DATEADD(WEEK, @WeekNum, @FirstDayOfYear) AS DateAddedWeekNumFromFirstDayOfYear,
    DATEADD(DAY, -(DAY(DATEADD(WEEK, @WeekNum, @FirstDayOfYear)) -1), DATEADD(WEEK, @WeekNum, @FirstDayOfYear)) AS FirstDayOfThatMonth,
    DATEPART(WEEK, DATEADD(DAY, -(DAY(DATEADD(WEEK, @WeekNum, @FirstDayOfYear)) -1),DATEADD(WEEK, @WeekNum, @FirstDayOfYear))) AS StartWeekNum


    A Fan of SSIS, SSRS and SSAS

    Thursday, July 23, 2015 10:06 PM
  • Good day Jamesit0,

    You request is a bit problematic since FIRST DAY OF THE WEEK is different value in different cultures. You can read more about this in this link: https://msdn.microsoft.com/en-us/library/ms181598.aspx

    Therefore the week number will be different in different cultures for the same date. for example let's test your query :-) 

    SET DATEFIRST 1
    
    DECLARE @Year AS INTEGER = 2015; -- Types do not need barkets
    DECLARE	@WeekNum AS TINYINT = 12;
    SELECT -- I just formated your query to more readable format (I think)
    	DATEPART(
    		WEEK,DATEADD(
    			DAY, 
    			1 - DATEPART(
    				DAY,DATEADD(WEEK, @WeekNum - 1, RTRIM(@Year * 10000 + 1 * 100 + 1))
    			), 
    			DATEADD(WEEK, @WeekNum - 1, RTRIM(@Year * 10000 + 1 * 100 + 1))
    		)
    	)
    GO
    
    
    SET DATEFIRST 5
    
    DECLARE @Year AS INTEGER = 2015; -- Types do not need barkets
    DECLARE	@WeekNum AS TINYINT = 12;
    SELECT -- I just formated your query to more readable format (I think)
    	DATEPART(
    		WEEK,DATEADD(
    			DAY, 
    			1 - DATEPART(
    				DAY,DATEADD(WEEK, @WeekNum - 1, RTRIM(@Year * 10000 + 1 * 100 + 1))
    			), 
    			DATEADD(WEEK, @WeekNum - 1, RTRIM(@Year * 10000 + 1 * 100 + 1))
    		)
    	)
    GO

    Therefore in order to get a solution that fits your request you need one more parameter. what is your culture or what is the first day for you? is it Sunday or Monday or... Friday? These type of query are named Non-deterministic since for the same data it return different value.

    In any case, here is a bit cleaner and faster solution that give the same result as your query: 

    DECLARE @Year AS INTEGER = 2015; -- Types do not need barkets
    DECLARE	@WeekNum AS TINYINT = 12;
    -- solution
    DECLARE @MyDATE DATE = dateadd(week, @WeekNum - 1, DATEFROMPARTS(@Year, 1, 1)) -- DATEFROMPARTS work from SQL Server 2012
    select 
    	DATEPART(WEEK, DATEADD(DAY, 1-DATEPART(DAY,@MyDATE), @MyDATE)) -- First week of the month
    GO

    I hope that this is useful and if you need more information please try to clarify the problematic issue i mentioned :-)

    have a great day :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]


    • Edited by pituachMVP Thursday, July 23, 2015 10:34 PM
    Thursday, July 23, 2015 10:32 PM
  • Good night friends!

    Thanks a lot every body for your great help.

    I read the Patrick Hurst link and seems to be the best option because as said Pituach Ariely I can not depend on culture.

    I will create a holiday table and I will create a calendar table.

    Probably I will bother next days because in my calendar table I will need manage weeks, fortnights, biweek, tens and months.

    I only have a last question today: If I need to ask something about calendar table next days do I need to open a new question or I must follow this one?

    Again thanks to all.

    Regards,


    Jamesit0

    Friday, July 24, 2015 3:46 AM
  • Hi Jamesit0,

    First, you are most welcome :-)

    1. Administrative and the new Q: If you have another thread please open a new thread. It the new thread is related to an old thread, then please remember to add a link to the old thread. I prefer that each question will be on separate thread, it is simpler to follow short threads then endless one :-)

    2. Administrative: There are lot of people and responses in this thread. (a) You can chose more then one answer if there are, (b) there are lot of responses that gave you part of the understanding or solution and they are useful. you can and should vote for them even if they are not the answer :-)

    3. Regarding the original Q: Please read all :-)

    Personally I will not chose Patrick solution in most case like this, but it is great solution.

    I did not said that there is no simple solution to your case! I just said that we are missing one basic information. I did said the current solutions that you got are nun-deterministic, but there are simple solutions which are deterministic (but again we need to chose the first day of the week).

    If you want to get the requirements that you asked for, then we are missing one parameter since the result is depending on your culture. Using calendar table make no difference! You still ,must decided in advanced what is the missing parameter! what is your first day of the week. The question itself is problematic, not the solution. If you decide/know what is the answer (what is the first day of the week for you), then you can get simple solution without calendar table.

    * Calendar table or Dates table is a must for lot of cases, and I have it in any server, as other accessories tables as well. For example when we want to deal with holidays, or when we need to split range of dates (from x throw y) into dates (x, x+1,x+2... y), but not for this case. Here we need a simple math operation. Yes you can use calendar table here as well, but you do not have to. You can store in your database 100 millions solutions for adding two numbers x+y=z (1,1,2),(1,2,3)... But you probably will not do so, and just use math on-the-fly :-). By the way, like always, there are cases that we do want to save performance and NOT do the math on-the-flay, so we save common cases. In your case this is simple math operation that can be don on-the-fly. All you need is to chose the first day of the week (as you need to do with calendar table as well) and I will show you a simple solution.

    For example:

    the function DATEPART with the parameter DW (Day of the week) or the parameter WEEK (week of the year) is Non-deterministic function, and I will almost never uses them. But I can create my own functions that are deterministic very simple and fast (The build in function meant to be flexible and fits any culture, so it fits none, but if I hard-coded decided what is my culture then I can create simple deterministic function), and here is a sample of deterministic solution that give the day of the week:

    DATEDIFF(DAY,0,GETDATE())%7 + 2

    * I chose to add 2 since: 0 represent the date 1900-01-01. At that date the day was Monday, In Israel culture... for us the first day of the week is Sunday so I add 2 to the modulo 7 of the days in order to get the DAY-of-The-WEEK which fit. If your first day of the week is not Sunday then you can use different number instead of adding 2, but now that we have this solution, it will give the same solution for any culture! (* check the example bellow)

    * Using Non-deterministic functions can be very problematic when dealing with multi-language interface or when you move your database between servers with different configurations! FOr example I had a client that asked my help since he moved his database from his office in Israel to GoDaddy servers (in US), and as a result, all his applications stop to work and raise errors. The fix was simple as the solution above :-)

    ---- Here are some examples from My lecture on Non-deterministic elements : ----------

    * If you totally stops using Non-deterministic functions, or you will have lot of manual work to do :-)
    lot of the Microsoft built-in functions are Non-deterministic. You just need to know them and understand the issues related with using them. I prefer to use my functions instead of most of these.

    DECLARE @MyDATEFIRST int = 1
    WHILE @MyDATEFIRST < 8 BEGIN
    	SET DATEFIRST @MyDATEFIRST
    	SELECT 
    		@@DATEFIRST                       as First_Day_Of_Week_NonDeterministic
    		, DATEPART(DW,GETDATE())          as Day_of_Week_today_NonDeterministic
    		, DATEPART(WEEK,GETDATE())        as Day_of_Month_today_NonDeterministic
    		, DATEDIFF(DAY,0,GETDATE())%7 + 2 as [Day_of_Week_today_Deterministic  :-)]
    	SET @MyDATEFIRST = @MyDATEFIRST + 1
    END;
    GO

    and here is another Non-deterministic function for fun:

    -- ISDATE is Nondeterministic function
    SET DATEFORMAT mdy;
    SELECT ISDATE('2008/15/04'); --Returns 0.
    SET DATEFORMAT ydm;
    SELECT ISDATE('2008/15/04'); --Returns 1.



    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]




    • Edited by pituachMVP Friday, July 24, 2015 1:54 PM
    Friday, July 24, 2015 7:26 AM
  • Good morning Pituach Ariely!

    Thanks a lot for your reply.

    First I didn't know that I may vote diferent answers if they were partial I always voted only the right answer.

    Second, my point is that my data can't depend from culture I mean if a user is from France he must have the same result as other from (in my case) Colombia. I'm building a multilanguage system and show every thing in the user language (including formats), but arithmetic must be the same depending some configuration table.

    I will use calendar table because I must handle holidays but I wanted to use just functions when holidays did not affect, then I may store the first day of the week as a parameter.

    I will need to split dates too, for example if a week covers two different months I will need to split that week the same for biweek case.

    I thought that if I must use calendar table to handle holidays I may use that calendar table to handle other scenarios where holidays does not affect. What will be the best practice?

    Thanks a lot again for your valuable helps.

    Regards,


    Jamesit0

    Friday, July 24, 2015 2:10 PM
  • Good day Daniel,

    If I may post a small comment :-)
    You are doing the same in your new query

    try to execute it in different language and you will get error, since "Mar" for example fits English but not FRENCH or Hebrew:

    SET LANGUAGE FRENCH;
    GO
     
    SELECT 
    	DATENAME(wk, CONVERT(DATETIME, DATENAME(mm, 'Mar 16 2015') + 
    	' ' +  CONVERT(VARCHAR(12), 1) + ' ' + 
    	CONVERT(VARCHAR(12), YEAR('Mar 16 2015')))), DATENAME(wk, 'Mar 16 2015')
    GO -- error

    * I highly recommend to read this article: http://ariely.info/Blog/tabid/83/EntryId/161/Date-displaying-format-vs-Date-storing-format.aspx


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]



    Thanks for the info! The regional date settings are not things I typically work with, so this is good to know.

    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Friday, July 24, 2015 3:17 PM
  • >> First I didn't know that I may vote diferent answers if they were partial I always voted only the right answer.

    Voting is done with the link in the left of the message. Marking as answer is done with the links in the bottom of the message :-) Moreover if thread contain more then one answer then you can mark several answers.

    >> Second, my point is that my data can't depend from culture I mean if a user is from France he must have the same result as other from (in my case) Colombia.

    Absolutely right!

    This is exactly my point as well and my solutions. I work a lot with nulti-language databases and this is just simple case :-)

    check my example above. I loop 1-8 which mean any culture that there is (there are only 7 days so each culture can use different day as the starting day of the week). Yet my solution in the last column return the same value for all.

    We need you basic logic first day of the week for the math, but any user in any culture will see the same result if you will use my solution which are Deterministic :-) I just need to know what is the ONE SOLUTION that you want everyone to see. and this ONE SOLUTION is depend on your choose of what you see as the first day of the week.

    PLEASE CHECK THE EXAMPLE ABOVE AND EXECUTE IT with several dates until you understand the behavior :-)

    >> but arithmetic must be the same

    true, but we are not talking about arithmetic. we are talking about logic and configuration. arithmetic is for example using DATDIFF and this is why I implement DATEPART using DATDIFF. 2-1 is arithmetic, but saying that today (2015-0724) we are in the 6 day in the week is not arithmetic! It is configuration. for some people we are now at the 6th day in the week and for others we are in the first day of the week. It is depending on the culture.

    >> I may store the first day of the week as a parameter.

    This is an option that I used long time ago (I think that My CMS app that I developed in 2001 used this option). It will work but you dont need it and you can design your database better without this limitation, after all you might want to use different day of the week tomorrow or you will have 100k users that use 7 different "first days of the week". Dont limit your self by design! your database today can be the base for 10k other application in the future and in some cases the same database uses for several applications and each will need different "first day of the week". SO you will stoire them all in table?!?

    This is an option :-)
    A simple option but I dont like this solution for this case :-))
    Since we can use simple arithmetic which is as you said pure Deterministic

    * in the current case keeping the parameter hard-coded in the database is better then calendar table, I think. But you will need calendar table as mentioned for other things

    ** everything that I say here is just "in general" and I can not suggest you what to do, since I do not have the full specification of your system.

    >> Thanks a lot again for your valuable helps.

    You are most welcome :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    • Marked as answer by Jamesit0 Friday, July 24, 2015 9:17 PM
    Friday, July 24, 2015 3:26 PM
  • First I didn't know that I may vote diferent answers if they were partial I always voted only the right answer.

    Regards,


    Jamesit0

    You can and should vote on the left for every helpful post. Doing this does not mark the post as an answer. I always make sure to give people credit where credit is due. When a post is helpful I make sure they get points for it. Like Pituach's post about my code and language. It was helpful so I let him know with a nod and some points. :)

    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)


    Friday, July 24, 2015 3:44 PM
  • You are most welcome Daniel :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    Friday, July 24, 2015 4:07 PM
  • Look up the ISO-8601 Standard. One of the formats is the Week-Date, which looks like [0-9][0-9][0-9][0-9]W[0-5][0-9]-[0-7] or "yyyyWww-d" The last position is the day {1-7} within the week, ww is {01-53} within the year {0001-9999}.   Load this from the internet into your calendar table. Use it with the calendar date and put it all ina VIEW.

    The week_date is handy for lots of things, and is used in the Nordic countries. 


    --CELKO-- <SNIP>


    • Edited by pituachMVP Thursday, August 6, 2015 5:08 PM <SNIP>
    Friday, July 24, 2015 4:25 PM