Update in date field updating calculated field

Asked

Viewed 2,466 times

3

I have in my table a Dtbehavior field of type Datetime. Besides this I have three other fields computed as follows:

DAY: (datepart(day,getdate()))

MES: (datepart(Month,getdate()))

YEAR: (datepart(year,getdate()))

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

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

DAY: (datepart(day,Dtbehavior))

MES: (datepart(Month,Dtbehavior))

YEAR: (datepart(year,Dtbehavior))

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

"Conversion of a data type varchar into a data type datetime resulted in a value outside the 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

SQL of Table Creation:

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

  • Are you sure your problem is in the calculated fields? What kind are your calculated fields? If you remove them from the table and try to give the update, it works?

  • What is the format of the field DtComportamento?

  • Run this command, please: (select name from sys.syslanguages Where langid=@@langid). What is the result?

  • @Cantoni I edited the question and put the table creation script... if remove the calculated fields from the table I do the update normally.

  • @Luciorubens, as I quoted in the question the field Dtcomportamento is of the type Datetime...

  • @The result is Portuguese (Brazil)

  • I got the update by removing the hyphens from the date, passing it as: Dtbehavior = '20150731 16:12'. But there are still fields calculated based on Dtbehavior and not on getdate()

  • 1

    A short form would be: DAY(Dtbehavior), MONTH(Dtbehavior), YEAR(Dtbehavior)

  • @Luciorubens worked, thank you very much!

Show 4 more comments

1 answer

5


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

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

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

However, in your comment, the language of your database is Portuguese. For you to update (or insert), change to:

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

The command to know the language of the database is:

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

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

SET LANGUAGE portuguese

Updating:

To create calcualdos fields that extract Day, Month and Year from a field of the type Datetime, you can do it in two ways:

Using the function of datepart:

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

Or as @Luciorubens said in their comment, using DAY, MONTH and YEAR functions directly:

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

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.