Change only the day of registration

Asked

Viewed 1,185 times

2

Scenario (example):

I have the following table:

ID | TIPO |    DATAINCLUSAO
 1 |  10  | 21/07/2018 09:34:51
 2 |  10  | 11/07/2018 11:15:25
 3 |  11  | 23/07/2018 01:52:31
 4 |  11  | 04/07/2018 23:24:52
 5 |  12  | 25/07/2018 03:43:33

Goal:

I’d like to make a update only on day, leaving all day 01, for example:

ID | TIPO |    DATAINCLUSAO
 1 |  10  | 01/07/2018 09:34:51
 2 |  10  | 01/07/2018 11:15:25
 3 |  11  | 01/07/2018 01:52:31
 4 |  11  | 01/07/2018 23:24:52
 5 |  12  | 01/07/2018 03:43:33
  • Is there such a possibility in the update itself?
  • If not, what alternatives?
sql sql-server

4 answers

4


whereas DATAINCLUSAO is the type datetime you can try the following UPDATE:

UPDATE tabela SET datainclusao = CONVERT(
    datetime, FORMAT(datainclusao, 'yyyy-MM-01 H:mm:ss')
);

In function FORMAT I am formatting the date to a format that the datetime will understand, changing part of the day.

Then with CONVERT convert the date back to datetime because the return of FORMAT is a nvarchar.

3

You can use the function DATEFROMPARTS, to change only the day:

UPDATE tabela 
  SET datainclusao = datefromparts(year(datainclusao), month(datainclusao), 1)

Here, I took the same year and month of the date and I only set the day as "1", since the function accepts (year, month, day) as parameters.

  • he doesn’t waste the time in that case?

  • The hours really will be missing. But if you were SQL Server 2012, you would have the perfect option, as I published.

  • mmm truth @gmsantos is right, I didn’t pay attention to that detail :)

1

Complementing answers:

From the SQL Server 2012, there is the function: DATETIMEFROMPARTS

Syntax:

DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

Example:

UPDATE tabela 
  SET DATAINCLUSAO = DATETIMEFROMPARTS(YEAR(DATAINCLUSAO), MONTH(DATAINCLUSAO), 1, DATEPART(HOUR, DATAINCLUSAO), DATEPART(MINUTE, DATAINCLUSAO), DATEPART(SECOND, DATAINCLUSAO), DATEPART(MILLISECOND, DATAINCLUSAO))

PS: could use only DATEPART to fetch all parts.

Other functions used:

DATEPART

1

You can update the data by update yes.

You have to transform the date to text, adjust the day and transform the value to date again.

I know this way, if I have others, I will learn.

Test with SELECT

select 
  '01' + substring(convert(varchar(25), GETDATE(), 103), 3, 8),
  convert(varchar(12), GETDATE(), 114),
  convert(datetime, '01' + substring(convert(varchar(12), GETDATE(), 103), 3, 8), 103) + convert(datetime, convert(varchar(12), GETDATE(), 114), 114)

No update

update TABELA set DATAINCLUSAO = convert(datetime, '01' + substring(convert(varchar(12), GETDATE(), 103), 3, 8), 103) + convert(datetime, convert(varchar(12), GETDATE(), 114), 114)

Browser other questions tagged sql sql-server

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