How do I SET DATEFIRST 1 with PetaPoco in a Query or manage pooled connections

Question

I have a query that groups results by week. In order to ensure a consistent week start I am trying to add the first line into a Peta Poco db.Query(sql) method.

SET DATEFIRST 1
SELECT
    count(logDate) as Value,
    dateadd(WEEK, datediff(WEEK,0, logDate),0) as [Date]
FROM
    ... etc ...

Without the SET DATEFIRST 1 PetaPoco generates something like...

exec sp_executesql N'   
                SELECT      
                    count(logDate) as Value,
                    dateadd(WEEK, datediff(WEEK,0, logDate),0) as [Date]
                FROM
                ... etc ...

With the SET DATEFIRST 1 PetaPoco generates something like...

exec sp_executesql N'SELECT [DataPoint].[Date], [DataPoint].[Value] FROM [DataPoint]    
                SET DATEFIRST 1
                SELECT
                    count(logDate) as Value,
                    dateadd(WEEK, datediff(WEEK,0, logDate),0) as [Date]
                FROM
                ... etc ...

How do I get around this generating the incorrect SQL? From the docs it looks like I can use db.Execute("SET DATEFIRST 1"); as long as it executes on the same connection, but since the connections are all managed for me, do I need to do anything to ensure it's the same connection as my query?

Answer 1

Put a semicolon as first char to tell PetaPoco to execute the query as is

  db.Execute(";SET DATEFIRST 1 ....