MVC LINQ Converting from datetime to week

Question

I am using WEB API and got a thing that bothers me for some time. I've got the following linq query:

I've got this query returning a date. What I want is to convert this date from YYYY - MM - DD to Week... which week of the year does match the date.

var currCulture = CultureInfo.CurrentCulture;

var weekNo = currCulture.Calendar.GetWeekOfYear(
            query, 
            currCulture.DateTimeFormat.CalendarWeekRule,
            currCulture.DateTimeFormat.FirstDayOfWeek);

I know it is wrong, but I can't seem to find a way to do the trick...

score 0 · Answer 1 · answered Aug 11 '16 at 20:57

0

Where are you erroring? And what type is your query object? IEnumerable? If so, it's not going to be instantiated until you call .ToArray() or .ToList() or something to actually make the query do work.

var currCulture = CultureInfo.CurrentCulture;
var cal = currCulture.Calendar;
CalendarWeekRule myCWR = myCI.DateTimeFormat.CalendarWeekRule;
DayOfWeek myFirstDOW = myCI.DateTimeFormat.FirstDayOfWeek;
var results = (from booking in context.Bookings select booking.Date).ToArray()
    .Select(p => new { Date = p, Week = cal.GetWeekOfYear(p, myCWR, myFirstDOW) })
    .ToArray();

That's a SWAG - haven't tested it - see MSDN for details on GetWeekOfYear usage. The key is executing the .ToArray() prior to manipulating the date, because otherwise you'll try to send that date manipulation down to your database and it won't understand what you're asking.

answered Aug 11 '16 at 20:57

David T. Macknet

3,112
3
27
36

Will be the same deal: it's not sent to the database until you tell it to instantiate the data as a structure of some sort, and your database doesn't like C# functions. – David T. Macknet Aug 11 '16 at 21:14
That said: if you've got a database back there, you could use its built-in week function to give you a column that's containing your week numbers, and you'd not have to instantiate - you could leave it as `IQueryable`. – David T. Macknet Aug 11 '16 at 21:15
I see. Thank you very much, David ! – Random User Aug 11 '16 at 21:57

score 0 · Answer 2 · answered Aug 11 '16 at 22:06

0

Assuming that context is EF database context and RDBMS is Sql Server, then you can try (not tested):

var weekNumbers = from booking in context.Booking
                    select SqlFunctions.DatePart("wk", booking.Date);

If it works, it should then perform week extraction from date on Sql server.

answered Aug 11 '16 at 22:06

davke

350
1
7

Interesting if it works - I'd wonder about start day of week & all that, to make this compatible with ANSI dates. – David T. Macknet Aug 13 '16 at 02:11
I suppose that would depend on how it is implemented on the backend - the SQL Server itself, since the method call is presumably simply translated to a SQL Server equivalent [DATEPART](https://msdn.microsoft.com/en-us/library/ms174420.aspx) T-SQL and then returns the same value. If that is ANSI-compliant, so should be then the values obtained in `weekNumbers` variable. – davke Aug 15 '16 at 09:45
The T-SQL datepart can be used to determine ISO week, but it's not exactly straightforward if you're considering year. http://stackoverflow.com/a/33736436/6850 has a good discussion of the issue there. My gut feel is that this should be performed by C# rather than passing it down to the database, but that feel likely has to do with how I've had to implement UI functions in the particular instance I've had to deal with this. So, I guess I'm saying: test this to see what it sends down to MSSQL, to see what the results are and whether they're expected, because it's complicated. – David T. Macknet Aug 16 '16 at 15:55

score 0 · Answer 3 · edited May 23 '17 at 12:32

0

In my case it would work to do:

var query = from booking in context.Bookings
        select booking.Date.weekOfYear();

Where I would have defined the weekOfYear() method in an extension class:

public static class dateExtensions
        {   
            public static int weekOfYear(this DateTime d)
            {                        
                return CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(d, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);                    
            }                
        }

For further detail on the precise calculation of the week number I would recommend as well reading the following answer: Get the correct week number of a given date

edited May 23 '17 at 12:32

Community

1
1

answered Aug 11 '16 at 22:31

donquijote

1,642
5
19
41

Actually, I think that would blow up, because you haven't instantiated the data, so the extension method would not exist in your database. – David T. Macknet Aug 13 '16 at 02:10
You are right. The first line should be for example: var query = from booking in context.Bookings.ToList() and it could work for him if he doesn't need that the evaluation happens on the database – donquijote Aug 13 '16 at 08:47

MVC LINQ Converting from datetime to week

3 Answers3