CODEWITHSUNDEEP
sqlsql-serversql-server-2008azure-sql-database
Fysicus
Fysicus

Reputation: 381

How to make a datepart() function in a view accept Monday as the first day of the week

For a webapplication I created the following view:

alter view [dbo].[vwBookingData] 
as
    select concat(rtrim(ltrim(str(datepart(year, bookingdata.booking_datetime)))), '-', rtrim(ltrim(str(datepart(week, bookingdata.booking_datetime))))) as  WeekNumber, 
           bookingdata.booking_customerCode as ClientCode, customer.Name as ClientName, concat(bookingdata.booking_provider, concat('-', bookingdata.booking_system)) as ProviderCombo,
           bookingdata.segments_carrierCode as CarrierCode, bookingdata.booking_datetime as BookingDate, bookingdata.booking_bookingId, flgConfirmed, flgFailed
    from dbo.flights_bookingdata bookingdata
         inner join dbo.Customer on Customer.Number = bookingdata.booking_customerCode

My problem is 1 specific part of the used query:

datepart(week, bookingdata.booking_datetime)

I have noticed the datepart() appears to take a week starts on Sunday rather than on Monday, this breaks the overview the view is supposed to generate.

Is there a way I can fix this within the query itself?

Upvotes: 0

Views: 1641

Answers (3)

Javier Cañon
Javier Cañon

Reputation: 162

With this function you can return the day of the week, starting from monday, in any server language or DATEFIRST setting:

-- =============================================
-- Author:      Javier Cañon
-- Create date: 2019-04-23
-- Description: Return day of week without know of server SET DATEFIRST setting
-- =============================================
CREATE FUNCTION [dbo].[fnGetWeekDayFromMonday]
(
    -- Add the parameters for the function here
    @SomeDate DATE
)
RETURNS int
AS
BEGIN

DECLARE @SqlWeekDay INT, @ResultVar int;

    SET  @SqlWeekDay= DATEPART(dw, @SomeDate);
    SET @ResultVar = ((@SqlWeekDay + @@DATEFIRST - 1 - 1) % 7) + 1;

    -- Return the result of the function
    RETURN @ResultVar;

END
GO

Upvotes: 0

Edmond Quinton
Edmond Quinton

Reputation: 1739

Seeing that you can’t use the SET DATEFIRST option I would simply subtract one day from the datetime when you call the DATEPART function as follow:

DATEPART(WEEK, DATEADD(DAY, -1, bookingdata.booking_datetime))

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81970

Perhaps you can try (I was right first with 1 not 2)

SET DATEFIRST 1;

At the top of your query

SET DATEFIRST 7;  -- The Default
Select *
      ,WeedDay=DateName(DW,RetVal)
      ,WeekNo=DatePart(WK,RetVal) 
 From [dbo].[udf-Range-Date]('2016-10-01','2016-10-14','DD',1)

enter image description here

While 

SET DATEFIRST 1;
Select *
      ,WeedDay=DateName(DW,RetVal)
      ,WeekNo=DatePart(WK,RetVal) 
 From [dbo].[udf-Range-Date]('2016-10-01','2016-10-14','DD',1)

enter image description here

Upvotes: 1

Related Questions