1

nextUpdate can be any date time value in the past. I'm trying to update the nextUpdate field to today's date but keeping the time unchanged and then add 8 hours.

I get

error converting string to datetime

T-SQLe:

UPDATE 
    business.dbo.db_schedule  
SET 
    nextUpdate = DATEADD(hh, 8, CONVERT(datetime, CONVERT(VARCHAR(8), GETDATE(), 111) + ' ' + CONVERT(VARCHAR(8), nextUpdate, 108), 111))  
WHERE
    sno = 8

datetime format in my location is 111

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hami
  • 335
  • 1
  • 7
  • 22
  • Datetime is SQL Server **doesn't have** any format - it's just an 8-byte numerical value.... – marc_s Oct 01 '12 at 13:35

1 Answers1

3
UPDATE business.dbo.db_schedule
SET nextUpdate= DATEADD(hh, 8,
                DATEADD(d, DATEDIFF(D,nextUpdate,Getdate()),
                          nextUpdate))
where sno=8
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262