locked
Update Datetime RRS feed

  • Question

  • QuestionHow can one update a DateTime field when the change request may alter any of the attributes of the time? (Day | hour | hour & minute).

    WHYOf course the whole point of a timestamp is to stamp the time of the record. The problem comes in when the user wants to tweak that timestamp. I am not interested changing anything about the time at the seconds point. The user will only change major time factors.

    Platform
    • .Net 1x
    • C#
    • SQL Server 2000
    • Eventually this will be in a Stored Procedure.

    advTHANKSance
    Wednesday, October 4, 2006 10:02 PM

Answers

  • what about a cast or convert method, when updating the record.
    Thursday, October 5, 2006 12:02 AM

All replies

  • Can you explain how you will pass the change units? Will it be number of days, hours and so on? If you are passing the datepart as numerical units then you can use the built-in dateadd function for example to change the datetime value. Ex:
     
     
    declare @days int
    set @days = -1
    select dateadd(day, @days, CURRENT_TIMESTAMP)
    set @days = 30
    select dateadd(day, @days, CURRENT_TIMESTAMP)
     
    Wednesday, October 4, 2006 11:16 PM

  • > Can you explain how you will pass the change units?

    From the example you provided, I would surmise that it would be best to pass in each change item in individually to the stored Procedure. Then it would be up to the proc to determine how to apply them using dateadd.

    But it is open on what is passed up...hence the post. Can another method be done?

    ClarificationIn the code the user is presented with the date extracted from the db as basic string

    10/12/2006 10:30 PM

    The user can change any of the above items. (This application is in-house so no need to worry about culture issues...)

    Now the information can be processed and a difference between the two could be generated before being sent to the stored procedure, such as number of seconds between the two....is that the answer?


    Thanks!
    Wednesday, October 4, 2006 11:38 PM
  • what about a cast or convert method, when updating the record.
    Thursday, October 5, 2006 12:02 AM
  • Just string concatenate the pieces of the date back together and pass it on to the stored proc as a datetime by Convert.ToDateTime(stringvalue). I assume there are textboxes for each piece of the datetime in the UI? (e.g. [10]/[12]/[2006] [10]:[30]:[PM]).
    Thursday, October 5, 2006 12:22 AM
  • The covert was the key. Here is the SQL

    SQL UPDATE dbo.tblPrimary
    SET RecordUpdatedDate=CONVERT(datetime, '2006-11-16 18:38')
    WHERE   dbo.tblPrimary.MainID = '823400001000'
    AND     dbo.tblPrimary.ApplicationUser = 'XM2685'


    Thanks everyone for your help. Umachandar even though I didn't use your suggestion, I marked it as helpful! Thanks again.
    Friday, October 6, 2006 3:29 PM