logo
down
shadow

Deterministic way of getting WeekEnding (Sun) for given date column (for use in computed column)


Deterministic way of getting WeekEnding (Sun) for given date column (for use in computed column)

By : Lucia Fernandez
Date : November 20 2020, 01:01 AM
wish of those help A kind of ugly but solid solution would be, to rely on a fixed date value, and then use the date difference modulo 7 as an alternative to the DATEPART function:
code :
DATEPART(WEEKDAY, [BookingDate])  -- with @@datefirst = 1
DATEDIFF(DAY, CONVERT(DATETIME,'1990-01-01',120), [BookingDate]) % 7 + 1  -- because 1990-01-01 is a monday
CREATE TABLE [dbo].[tbl_DateExample] (
    [ID] [INT] IDENTITY(1, 1) NOT NULL ,
    [BookingDate] [DATE] NOT NULL ,
    [WeekEnding] AS ( DATEADD(DAY, ( 6 ) - (DATEDIFF(DAY, CONVERT(DATETIME,'1990-01-01 00:00:00.000',120), [BookingDate]) % 7), [BookingDate]) ) ,
    CONSTRAINT [PK_tbl_DateExample] PRIMARY KEY CLUSTERED ( [ID] ASC )
)


Share : facebook icon twitter icon
Computed column 'Month' in table cannot be persisted because the column is non-deterministic

Computed column 'Month' in table cannot be persisted because the column is non-deterministic


By : ShoutStreatham
Date : March 29 2020, 07:55 AM
it should still fix some issue The datename function can return different results dependant on the language of the logged in user hence is not deterministic. Marking a computed column as persisted means SQL Server stores the results of the computation which requires there to be exactly one result.
If you need this as persisted at all you can replace with a 12 branch case expression with the specific language you want used.
code :
CREATE TABLE #T
  (
     [AppliedDate] DATETIME,
     [Month] AS CASE MONTH([AppliedDate])
          WHEN 1 THEN 'January'
          WHEN 2 THEN 'February'
          WHEN 3 THEN 'March'
          WHEN 4 THEN 'April'
          WHEN 5 THEN 'May'
          WHEN 6 THEN 'June'
          WHEN 7 THEN 'July'
          WHEN 8 THEN 'August'
          WHEN 9 THEN 'September'
          WHEN 10 THEN 'October'
          WHEN 11 THEN 'November'
          WHEN 12 THEN 'December'
        END PERSISTED
  )  

  INSERT INTO #T([AppliedDate]) VALUES (GETDATE())
Cannot persist computed column - not deterministic

Cannot persist computed column - not deterministic


By : Tanusree Chaudhuri
Date : March 29 2020, 07:55 AM
help you fix your problem CONVERT([varchar](4),@Year,(0))+'-01-01' is being passed to a DATEDIFF call, in a position where a date is expected, forcing an implicit conversion to occur.
From the rules for deterministic functions:
Hashbytes Computed column cannot be persisted because the column is non-deterministic

Hashbytes Computed column cannot be persisted because the column is non-deterministic


By : zhuimeng30
Date : March 29 2020, 07:55 AM
wish help you to fix your issue I got the solution. I modified HashBytes logic as below to get the required datetime format and also on the C# side, I am using default encode.
code :
Select hashbytes('MD5', convert(varchar(200),(CONVERT(varchar(10),datestamp,126)+' '+CONVERT(VARCHAR(24),datestamp,114)),2))
from Events
Where DateStamp ='2016-06-30 12:19:35.257961'
Computed column 'BookID' in table 'Books' cannot be persisted because the column is non-deterministic

Computed column 'BookID' in table 'Books' cannot be persisted because the column is non-deterministic


By : Shekhar
Date : March 29 2020, 07:55 AM
To fix the issue you can do I want to create a table with computed column for its custom ID column. The format which I want is BID(The Year)-0000 for example the one like this: , Simplest solution
I would add a CreateDate column thus:
code :
ALTER TABLE dbo.Books
ADD CreateDate DATETIME NOT NULL
CONSTRAINT DF_Books_CreateDate
DEFAULT(GETDATE())
GO
ALTER TABLE dbo.Books
ADD BookID AS  ('BID' + LTRIM(YEAR(CreateDate)) + '-' + RIGHT('0000' + LTRIM(ID), 4)) /*PERSISTED*/
GO
Computed column '...' in table '...' cannot be persisted because the column is non-deterministic?

Computed column '...' in table '...' cannot be persisted because the column is non-deterministic?


By : user3092559
Date : March 29 2020, 07:55 AM
wish helps you Functions must be decorated with the WITH SCHEMABINDING hint, otherwise SQL Server skips the validation of determinism (a performance optimization), and treats that default result as not being deterministic.
Related Posts Related Posts :
  • FreeTDS working, but ODBC cannot connect
  • How to change datetime format of varchar datatype column
  • Trigger on extended properties? SQL SERVER
  • Database Schema guidance for an app store
  • sql trigger to update based on specific column is updated and if value is equal to 'x'
  • How to descope a local variable in t-sql
  • SQL Server Compact stored procedure with multiple values in 1 input
  • How to validate the ICCID number of a SIM card in TSQL?
  • how to calculate all previous datas sum of a sql column on a date range report
  • How to create view listing all friendship for "friend1,friend2" table in T-SQL and prevent inserting duplicate
  • Sum of multiple column in SQL Server
  • Check month and day lies between two dates
  • How would I configure Effort Testing Tool to mock Entity Framework's DbContext withOut the actual SQL Server Database up
  • While looping through a function, Error: Maximum stored procedure, function, trigger, or view nesting level exceeded (li
  • Collapsing records with adjacent start and end dates
  • SQL Server Management Studio 2008 - Timeout Expired error
  • Insert date from VB to SQL Server
  • How to find what table foreign key is from?
  • Sql Server Nested Inserts possible
  • SQL Server, why does one order by query work with a string, but not with an integer?
  • How do I install a SQL Server on the hosted build agent in Visual Studio Online?
  • EXECUTE AS/REVERT within a transaction in SQL Server
  • Distinct 2 field AS one column SQL SERVER
  • how to create a new database after a trigger fired from a table
  • How to sort string alphabetically
  • Checking data in one column in a temp table to a column in another table
  • What's wrong with my IF/ELSE? "ELSE: Incorrect syntax near 'ELSE'."
  • CASE CLAUSE IN WHERE CONDITION
  • TSQL - Creating a running total of a column
  • Why am I getting not null here?
  • Why does sp_refreshview raise an exception on views that are not schema bound?
  • EXEC parameters from a SELECT
  • SQL Server Compact connection
  • Star Schema Structure - To many Dimensions
  • sort utf string in sql server with join clause
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org