How to set a value(datefirst) in an SQL statement in C#

I am having a problem trying to SET DATEFIRST while running a query in C#.

First off this is the sql statement that works in SQL management studio.

  • Why is an index created more than one time in SQL via Entity Framework
  • Select * from Database 1 and insert into database 2
  • SQL Server - Insert XML to every row in a column
  • How sqoop converts sql datatypes into hive
  • Random SQL Server Timeouts during a small window of time at random times
  • CHECKDB never completes, hanging due to wait_type RESOURCE_SEMAPHORE
  • SET DATEFIRST 1;
    GO
    
    CREATE VIEW [Tannery_Data_View2] AS
    SELECT [week_day] = DATENAME(dw, Tannery_Values.date), [week_ending] = DATEADD(DAY, 7-DATEPART(WEEKDAY, dbo.Tannery_Values.date), dbo.Tannery_Values.date), Tannery_Values.date, Tannery_Values.value, Variable_Titles.Variable, [Performance_Category] = Performance_Categories.Category, [Process_Category] = Process_Categories.Category
    FROM Tannery_Values 
    INNER JOIN Variable_Titles ON Tannery_Values.variableTitleID=Variable_Titles.VariableID
    INNER JOIN Performance_Categories ON Tannery_Values.performanceCategoryID=Performance_Categories.CategoryID
    INNER JOIN Process_Categories ON Tannery_Values.processCategoryID=Process_Categories.CategoryID
    

    Here is my code to call it.

    public void executeNonQuery(string insertString)
            {
                sqlConnection.Open();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = insertString;
                command.Connection = sqlConnection;
                command.ExecuteNonQuery();
                sqlConnection.Close();
            }
    

    The insert string is the above sql statement commented out into a string. It throws the error that the CREATE VIEW must be the first statement in the batch. I have subsequently tried seperating it out into two queries with with create view seperate from the set statement but it doesn’t work either. My understanding is that executenonquery is not the right method to be using here as the computer forgets it has been set? However, i can’t seem to find what I actually need to do.

    I could run a stored procedure and call that if it is easier but I would like to know what it is I am doing wrong. Especially since I cannot easily find the answer so it would be good to have it up here for others.

  • Hierarchical SQL Query WITH parent child items
  • writing a query to to generate two records from one record based on some condition
  • Copy SQL Server Express Database to Another Computer
  • Best proven ways to improve search performance
  • How to understand that a user gets database access in SQL Server
  • Option Recompile makes query fast - good or bad?
  • 2 Solutions collect form web for “How to set a value(datefirst) in an SQL statement in C#”

    You cannot use SET DATEFIRST in a view.

    Probably the easiest way is to generate it in your C# before running your view

    cmd.CommandText = "SET DATEFIRST 1";
    cmd.ExecuteNonQuery();
    

    Or the best way to achieve that is having a Date Table

    Creating a Date Table/Dimension

    The SQL you have creates a view after it sets the First Day of the Week to Monday. This doesn’t make sense since the View won’t retain the DATEFIRST setting when it’s actually run.

    Are you sure you don’t want the DATEFIRST to be part of the view you are creating?

    CREATE VIEW [Tannery_Data_View2] AS
    SET DATEFIRST 1;
    GO
    
    SELECT [week_day] = DATENAME(dw, Tannery_Values.date), [week_ending] = DATEADD(DAY, 7-DATEPART(WEEKDAY, dbo.Tannery_Values.date), dbo.Tannery_Values.date), Tannery_Values.date, Tannery_Values.value, Variable_Titles.Variable, [Performance_Category] = Performance_Categories.Category, [Process_Category] = Process_Categories.Category
    FROM Tannery_Values 
    INNER JOIN Variable_Titles ON Tannery_Values.variableTitleID=Variable_Titles.VariableID
    INNER JOIN Performance_Categories ON Tannery_Values.performanceCategoryID=Performance_Categories.CategoryID
    INNER JOIN Process_Categories ON Tannery_Values.processCategoryID=Process_Categories.CategoryID
    

    Or do you not just want to run the query:

    SET DATEFIRST 1;
    GO
    
    SELECT [week_day] = DATENAME(dw, Tannery_Values.date), [week_ending] = DATEADD(DAY, 7-DATEPART(WEEKDAY, dbo.Tannery_Values.date), dbo.Tannery_Values.date), Tannery_Values.date, Tannery_Values.value, Variable_Titles.Variable, [Performance_Category] = Performance_Categories.Category, [Process_Category] = Process_Categories.Category
    FROM Tannery_Values 
    INNER JOIN Variable_Titles ON Tannery_Values.variableTitleID=Variable_Titles.VariableID
    INNER JOIN Performance_Categories ON Tannery_Values.performanceCategoryID=Performance_Categories.CategoryID
    INNER JOIN Process_Categories ON Tannery_Values.processCategoryID=Process_Categories.CategoryID
    

    Update:

    After further review, SQL Server doesn’t allow the use of DATEFIRST in a View. I used Luther Miller’s theory (http://sqlmag.com/t-sql/normalizing-first-day-week) for using @@DateFirst to calculate the week.

    SELECT [week_day] = DATENAME(dw, Tannery_Values.date), 
        [week_ending] = DATEADD(DAY, 7-DATEPART(WEEKDAY, ((@@DATEFIRST - 1) % 7) + 1), dbo.Tannery_Values.date), 
        Tannery_Values.date, Tannery_Values.value, Variable_Titles.Variable, 
        [Performance_Category] = Performance_Categories.Category, [Process_Category] = Process_Categories.Category
    FROM Tannery_Values 
    INNER JOIN Variable_Titles ON Tannery_Values.variableTitleID=Variable_Titles.VariableID
    INNER JOIN Performance_Categories ON Tannery_Values.performanceCategoryID=Performance_Categories.CategoryID
    INNER JOIN Process_Categories ON Tannery_Values.processCategoryID=Process_Categories.CategoryID
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.