Update in date field updating calculated field

Question

Update in date field updating calculated field

Navigation

#1 by (5 votes)

3

I have in my table a field of DtManagement of type DateTime. In addition to this I have three other fields computed as follows:

DAY: (datepart (day, getdate ())

MONTH: (datepart (month, getdate ())

YEAR: (datepart (year, getdate ())

Based on this situation I have a problem and a question:

Doubt: How do I compute fields based on DtManagement? That is, I need something like:

DAY: (datepart (day, DtBehavior))

MONTH: (datepart (month, DtBehavior))

YEAR: (datepart (year, DtBehavior))

Problem: When doing an Update in the table, SQL returns me:

"Converting a varchar data type to a datetime data type resulted in a value out of range."

This is my Update:

UPDATE Comportamentos SET IdAvaliado = 1013, 
                          IdArea = 3, 
                          Nome = 'Auxilia quando solicitado', 
                          Tipo = 1, 
                          Descricao = 'Auxilia os colegas quando solicitado',
                          Nota = 9.5, 
                          DtComportamento = '2015-07-31 16:12' 
WHERE Id = 13

Table Creation SQL:

CREATE TABLE dbo.Comportamentos (
  Id int NOT NULL IDENTITY(1, 1),
  IdAvaliador int NOT NULL REFERENCES dbo.Colaboradores(Id),
  IdAvaliado int NOT NULL REFERENCES dbo.Colaboradores(Id),
  IdArea int NOT NULL REFERENCES dbo.Areas_Comportamento(Id),
  Nome varchar (100) NOT NULL,
  Tipo smallint NOT NULL,
  Descricao varchar (300) NULL,
  Nota decimal (5, 2) NULL,
  Dia AS (datepart(day,getdate())),
  Mes AS (datepart(month,getdate())),
  Ano AS (datepart(year,getdate())),
  DtComportamento datetime NULL DEFAULT GETDATE(),
  DtCadastro datetime NOT NULL DEFAULT GETDATE(),
  Status smallint NOT NULL DEFAULT 1
)

sql sql-server

asked by anonymous 05.08.2015 / 14:52

1 answer

How to implement Navigation Drawer with Map in a Fragment Distancing image from text in Button

score 5 · Accepted Answer

Your problem is not in the calculated fields, but in the format of the Date and Language of your database.

In the example question, the Update in the DTCsports field is:

DtComportamento = '2015-07-31 16:12'

However, in your comment, the language of your bank is Portuguese. For you to do your update (or Insert), switch to:

DtComportamento = '2015-31-07 16:12'

The command to know the bank language is:

select name from sys.syslanguages where langid=@@langid

The command to change the language (only in the current session) is:

SET LANGUAGE portuguese

Update :

To create calcuald fields that extract Day, Month, and Year from a DateTime type field, you can do two ways:

Using the datepart function:

 Dia AS (datepart(day,DtComportamento)),
 Mes AS (datepart(month,DtComportamento)),
 Ano AS (datepart(year,DtComportamento)),

Or as @LucioRubens said in your comment using the DAY, MONTH and YEAR functions directly:

 Dia AS (DAY(DtComportamento)),
 Mes AS (MONTH(DtComportamento)),
 Ano AS (YEARDtComportamento)),