CODEWITHSUNDEEP
sqlsql-server
Morteza Jangjoo
Morteza Jangjoo

Reputation: 1790

How to update only days of a date in SQL Server

How to update all days in date column in table to special date?

I want change only day, no month, no year

My table:

id    RegisterDate
------------------
1      2001-01-18
2      2018-09-13
3      1999-04-28
4      2012-12-15

The result that I expect:

id    RegisterDate
------------------
1      2001-01-02
2      2018-09-02
3      1999-04-02
4      2012-12-02

Upvotes: 10

Views: 35080

Answers (5)

Matteo Toma
Matteo Toma

Reputation: 675

Example:

SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY);

Source: https://www.w3schools.com/mysql/func_mysql_date_add.asp

Upvotes: 0

hkravitz
hkravitz

Reputation: 1385

for all versions of SQL Server (since 2000)

DECLARE @table TABLE (dt DATE)
INSERT INTO @table 

SELECT GETDATE()-1
UNION ALL 
SELECT GETDATE()-2
UNION ALL 
SELECT GETDATE()-3
UNION ALL 
SELECT GETDATE()-5

 UPDATE @table
 SET dt = DATEADD(DAY,1,DATEADD(MONTH,DATEDIFF(MONTH,0,dt),0))

 SELECT dt 
 FROM @table

Upvotes: 0

D-Shih
D-Shih

Reputation: 46229

If your SQL Server version is higher than 2012, you can use the FORMAT function:

CREATE TABLE T
(
    ID INT,
    RegisterDate DATE 
)

INSERT INTO T 
VALUES (1, '2001-01-18'), (2, '2018-09-13'),
       (3, '1999-04-28'), (4, '2001-12-15');

UPDATE T
SET RegisterDate = FORMAT(RegisterDate,'yyyy-MM-') + '02'

sqlfiddle:http://sqlfiddle.com/#!18/1163b/1

Upvotes: 0

uzi
uzi

Reputation: 4146

You can achieve that with eomonth function that is available from SQL server 2012

update myTable
set RegisterDate = dateadd(dd, 2, eomonth(RegisterDate, -1))

Another way for all versions

update myTable
set RegisterDate = cast(convert(char(6), RegisterDate, 112)+'02' as date)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Probably the simplest way is to use datefromparts():

update t
    set RegisterDate = datefromparts(year(RegisterDate), month(RegisterDate), 2);

Another method that works in older versions of SQL Server is:

update t
     set RegisterDate = dateadd(day, 2 - day(RegisterDate), RegisterDate);

As a side node: there is no need to use string operations for this. The built-in date/time functions are quite sufficient.

Upvotes: 29

Related Questions