Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

SET DATEFIRST 1 in Direct Query mode

I am completely out of ideas.    My data is all structured to look at Monday as the first day of the week but I am using an Azure SQL backend so I can't change the default value which is set to Sun...
Need Help
miltenburger's avatar
miltenburger
Helper V
8 years ago

Hi Anonymous,

 

I was sturggling with this problem also to set datefirst 1 in my DirectQuery.

What i had to do is get the data from last week (monday till sunday), what i created was following:

 

SELECT *
FROM mytable
WHERE CreatedDateTime >= DATEADD(day, -(CASE WHEN DATEPART(DW, GETDATE()) = 1 THEN 7 ELSE DATEPART(DW, GETDATE()) - 1 END + 6), CONVERT(DATE, GETDATE())) 
    AND CreatedDateTime <  DATEADD(day, 1 - CASE WHEN DATEPART(DW, GETDATE()) = 1 THEN 7 ELSE DATEPART(DW, GETDATE()) - 1 END, CONVERT(DATE, GETDATE()))
    AND Minutes is not null
    AND Minutes > 0
    AND TicketNumber like 'CH%'

Hope it helps you to get to set the datefirst to 1, it's all based on my where clause.

Kind regards

Anonymous's avatar
Anonymous
Not applicable
8 years ago

miltenburger thanks for the reply,

 

It feels to me like they have severly regressed the softwware, I can't understand why such a tiny and simple change should be made so complicated