Results 1 to 4 of 4

Thread: Update date part of DateTime field ONLY

  1. #1
    Join Date
    Aug 2007
    Posts
    3

    Update date part of DateTime field ONLY

    Part of an app I am working on involves finding the time difference between times.

    One of my database tables includes a field which has times in the following format: for example:

    01-01-1900 14:35:00
    01-01-1900 15:45:00

    The problem I have is that I want to find the difference between say 14:35:00 in my database table and 29-12-2007 14:45:15.

    When I use DATEDIFF this falls because of the huge difference between the Date parts of these values.

    Is there any way I can update just the date parts of a DateTime field. So for instance (today) my two examples would become:

    29-12-2007 14:35:00
    29-12-2007 15:45:00

    I have tried using DATEPART to extract the day, month and year of the current date and then 'rebuild' these dates using an UPDATE statement but this did not work.

    I also tried the following which takes the time from each record and then attempts to update the relevant field with the current date and the correct time for the record.

    Code:
    DECLARE @currentPK int
    SET @currentPK = 1
    
    DECLARE @maxPK int
    SELECT @maxPK = MAX(CallChargePK) FROM InternetTracking_Data.dbo.tblCallCharges
    
    DECLARE @tempTime datetime
    DECLARE @time varchar(15)
    
    while (@currentPK <= @maxPK)
    begin
    	SELECT @tempTime = StartTime FROM InternetTracking_Data.dbo.tblCallCharges WHERE CallChargePK = @currentPK
    		
    	UPDATE InternetTracking_Data.dbo.tblCallCharges
    	SET StartTime = getDate() + ' ' + @tempTime
    
    	SET @currentPK = @currentPK + 1
    end
    This just displays a stupid date though because sql is attempting to ADD the current date and the time.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Tried with dateadd function?

  3. #3
    Join Date
    Dec 2004
    Posts
    502
    There are a few different ways to do this. Here to me is the most direct way:

    UPDATE InternetTracking_Data.dbo.tblCallCharges
    SET StartTime = DATEADD(day, DATEDIFF(day, StartTime, getdate()), StartTime)

    The inner part:
    DATEDIFF(day, StartTime, getdate())
    finds the number of days between the StartTime date and today's date. Then, the DATEADD function simply adds this number of days back to the StartTime, which in effect updates StartTime to today's date while preserving the time element. BTW, if you use this method, you shouldn't have to use the WHILE loop. One UPDATE statement should be all you need.

  4. #4
    Join Date
    Aug 2007
    Posts
    3
    Thanks. That worked brilliantly! Exactly what I wanted!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •