0

I am sure this has to be a dup post, however i have search with no results.

I have a Table with column Datetime, i want to add for example 5 days to the Date but leaving the Time as it currently is I have tried

SET date = DATEADD(DAY, 5, CAST(CAST(date AS DATE) AS DATE))

however this removes the time on the datetime

For example:

2022-09-28 10:59:45.467
2022-09-27 12:16:53.273

Would become:

2022-10-03 10:59:45.467
2022-10-02 12:16:53.273

This is an existing Database i have no control over, however i just want to manipulate some data for examples.

If someone wants to be super clever, then if possible the best solutions would be having the most recent Date to be todays date then any other date to be off set by 1 day prior (-1). example:

   1- 2022-09-28 10:59:45.467
   2- 2022-09-27 12:16:53.273
   3- 2022-09-24 09:46:24.124
   4- 2022-09-22 10:22:51.248
   5- 2022-09-20 07:31:12.232
   6- 2022-09-20 08:41:00.147
   7- 2022-09-20 10:12:13.245
   8- 2022-09-15 12:16:53.145

Outcome:

   1- 2022-10-03 10:59:45.467
   2- 2022-10-02 12:16:53.273
   3- 2022-10-01 09:46:24.124
   4- 2022-09-30 10:22:51.248
   5- 2022-09-29 07:31:12.232
   6- 2022-09-28 08:41:00.147
   7- 2022-09-27 10:12:13.245
   8- 2022-09-26 12:16:53.145

But i guess this one is a big ask, the other solution is simple enough, the super clever solution would be a awsome thing i could add in powershell to automate the process in a single click.

Update

Oversight on my side, as pointed out in the comments, i do not know why i was using Cast.

SET date = DATEADD(DAY, 5, date)

Is working.

Dave Hamilton
  • 675
  • 1
  • 12
  • 23
  • 1
    Why are you `CAST`ing the value to a `date`...? A `date` doesn't have a time portion. `CAST(CAST(date AS DATE) AS DATE)` makes even less sense... `CAST` the column `date` to a `date` and then cast that `date` to a `date`. *Why?* Is the column `date` not a `date(time)`? – Thom A Oct 03 '22 at 10:49
  • What type is `date`? Have you tried this without the `CAST`s. – Peter Smith Oct 03 '22 at 10:50
  • @Larnu Yes not sure why i was using CAST, i hadnt even picked up on it, changed without cast and yes its working – Dave Hamilton Oct 03 '22 at 10:54

0 Answers0