CODEWITHSUNDEEP
sqlsql-server
user9273914
user9273914

Reputation: 99

SQL : Date Function

On the daily basis, I have to update a DateTime column in the table. The field should in the format '2019-04-30 00:00:00.000', It should be always the 'NextYear-04-30 00:00:00.000'. How do I write this function?

As I am running this update statement today i.e in 2018 so it should be '2019-04-30 00:00:00.000', If I run the update statement in 2019, it should be '2020-04-30 00:00:00.000'

For Example: This query is giving me '2018-04-30 00:00:00.000'
Select CONVERT(datetime, CONVERT(varchar(20),DATEPART(year, getdate())) + '-04-30')

Thanks

Upvotes: 0

Views: 373

Answers (5)

Pawel Czapski
Pawel Czapski

Reputation: 1864

Use below logic to get time as 00:00:00.000

SELECT CAST(CAST(DATEADD(YEAR, 1, GETDATE()) AS DATE) AS DATETIME)

Outup:

2019-10-26 00:00:00.000

EDIT

use DATEFROMPARTS (added to sqlserver 2012)

SELECT CAST(DATEFROMPARTS ( YEAR(GETDATE())+1, 4, 30 )  AS DATETIME)

Output:

2019-04-30 00:00:00.000

DateFromParts Source:

https://learn.microsoft.com/en-us/sql/t-sql/functions/datefromparts-transact-sql?view=sql-server-2017

Upvotes: 0

Zorkolot
Zorkolot

Reputation: 2027

Using your same query, you can just encapsulate with a DATEADD function to add 1 year:

Select DATEADD(YEAR, 1, CONVERT(datetime, CONVERT(varchar(20),DATEPART(year, getdate())) + '-04-30'))

Produces output:

2019-04-30 00:00:00.000

Upvotes: 1

NicVerAZ
NicVerAZ

Reputation: 407

For this you can also use a computed column. It will mean a loss of performance vs running a job updating a column once a day, but it will give you peace of mind as the job could fail and you not being made aware.

The function would be:

CAST(DATEADD(YEAR, 1, GETDATE()) AS DATE)

If this is something fed into a report, I would use a sproc to generate the report and dynamically add a column populated with the function above.

If this is used in business I would also use sprocs and dynamically calculate the variable internally.

I see little use for a column always indicating the date in one year. It's just not a good idea.

Upvotes: 0

Suraj Kumar
Suraj Kumar

Reputation: 5653

You can add one year in your table date column which you want to update as shown below - 

update YourTable
set YourDateColumn = DATEADD(year, 1,YourDateColumn) 
where YourDateColumn <= GETDATE() --It will only update which has value less than next year. Remove or update it as required

As with your values

select DATEADD(year, 1,'2018-04-30 00:00:00.000')

Upvotes: 0

Peter Schneider
Peter Schneider

Reputation: 2939

You should use the DATEADD() function. 

UPDATE yourTable SET DateCol = DATEADD(year,1,DateCol)

Upvotes: 0

Related Questions