Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Pittsburgh, PA
    Posts
    41

    Question Unanswered: Using datePart in an UPDATE statement

    I have a perculiar instance when I must use datepart in an update statement, but it seems that all the parentheses are erroring out the code. Here's what I'd like to write:

    UPDATE [Event Times] SET
    [Day of Week] = 6, [Recurrence Type] = 0,
    DATEPART([hour], [Start Time]) = 09,
    DATEPART([minute], [Start Time]) = 00
    WHERE ([Event id] = 4) AND ([Day of Week] = 6) AND ([Recurrence Type] = 0 )

    Any Ideas? Thanks in advance!

  2. #2
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    DATEPART is a function that RETURNS a value, not SETS a value. Are you just trying to set the time or is the date important as well?

  3. #3
    Join Date
    Feb 2004
    Location
    Pittsburgh, PA
    Posts
    41
    I have a date and time already in the field, and just want to update the time portion. I just want to keep from doing an additional select statement to find what the date is, then add it to the string.

  4. #4
    Join Date
    Oct 2003
    Posts
    84
    If [Start Time] is a DATETIME value:

    UPDATE [Event Times] SET
    [Day of Week] = 6, [Recurrence Type] = 0,
    DATEADD(hh, 9, DATEDIFF(d, 0, [Start Time]));
    WHERE ([Event id] = 4) AND ([Day of Week] = 6) AND ([Recurrence Type] = 0 )

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    First, clean up your code by dumping unnecessary parenthesis:

    UPDATE [Event Times]
    SET [Day of Week] = 6,
    [Recurrence Type] = 0,
    DATEPART([hour], [Start Time]) = 09,
    DATEPART([minute], [Start Time]) = 00
    WHERE [Event id] = 4
    AND [Day of Week] = 6
    AND [Recurrence Type] = 0

    Next....HELLO???? Why are you update [Day of Week] to 6 and [Recurrence Type] to 0 for records that already have those values? Dump those SET statements:

    UPDATE [Event Times]
    SET DATEPART([hour], [Start Time]) = 09,
    DATEPART([minute], [Start Time]) = 00
    WHERE [Event id] = 4
    AND [Day of Week] = 6
    AND [Recurrence Type] = 0

    Lookin' better already. Now, to set just the time portion of the [Start Time] field, use this

    UPDATE [Event Times]
    SET [Start Time] = convert(char(10), [Start Time], 120) + ' 09:00:00'
    WHERE [Event id] = 4
    AND [Day of Week] = 6
    AND [Recurrence Type] = 0

    Much improved! Now if you really want to go all the way and look like a real SQL Pro, change all your object, variable, and parameter names so that they don't have spaces in them.

    UPDATE EventTimes
    SET StartTime = convert(char(10), StartTime, 120) + '09:00:00'
    WHERE EventID = 4
    AND DayOfWeek = 6
    AND RecurrenceType = 0

    I have seen the light, and it is good!

    Lastly, you can probably dump the DayOfWeek column altogether. I imagine it is related to the StartTime value, and thus violates basic normalization principles.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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