Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jan 2005
    Location
    Portland, Oregon
    Posts
    40

    Unanswered: Update day,month and year of a Datetime

    using MS SQL Server, I want to update only the day,month and year portion of a Datetime field.

    So, i want to create an update query to set the day, month and year the same in each record but leave the datepart unchanged.

    This gives me an error message:
    UPDATE dbo.tblMEETINGS
    SET DATEPART(dd, MeetingTimeNEW) = 1, DATEPART(mm, MeetingTimeNEW) = 1, DATEPART(yy, MeetingTimeNEW) = 2006


    In my mind, an electrician, it make sense to only update each part of the whole. However.........

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can get there from here, but the route is kinda funky...
    Code:
    UPDATE dbo.tblMEETINGS
    SET MeetingTimeNEW = '2006-01-01 ' + Convert(CHAR(8), MeetingTimeNEW, 108)
    As Date and Time manipulation are very database engine specific, I'm moving this thread to the Microsoft SQL forum.

    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Or this, which may be slightly faster:
    Code:
    UPDATE	dbo.tblMEETINGS
    SET	MeetingTimeNEW = dateadd(day, datediff(day, MeetingTimeNEW, '2006-06-10'), MeetingTimeNEW)
    Regardless, the error you were getting is because you were trying to set a function to a value, instead of the other way around. Functions RETURN values, but they cannot be set to a value.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    Or this, which may be slightly faster:
    Code:
    UPDATE	dbo.tblMEETINGS
    SET	MeetingTimeNEW = dateadd(day, datediff(day, MeetingTimeNEW, '2006-06-10'), MeetingTimeNEW)
    Regardless, the error you were getting is because you were trying to set a function to a value, instead of the other way around. Functions RETURN values, but they cannot be set to a value.
    ... in SQL Server 2000. This is definitely not true of all database engines, and I'm not even positive it is true of all SQL based engines (even though I think that it should be).

    -PatP

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Which? The additive algorithm being faster than string manipulation, or the inability to set a function to a value?
    Support?
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Some database engines allow you to assign values to functions. The function actually returns something much like a C pointer, so the assignment makes perfect sense within the context of that database engine, although it will be a mind-bender for folks that don't have any experience with it!

    -PatP

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Cool! I'll have to look into other database engines

    Over time I became what I refer to as an "expert" on pointers and pointer references in "C" (and as proof of this expertise, I can direct you to any number of bathroom walls )...

    When C went away, a little part of the world died. Well, at least the part that made me feel superior in some way to others in my domain NOW I've had to find other ways to bolster my self image (like being a Steeler fan since the '70s )
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan
    Some database engines allow you to assign values to functions.
    Sounds awesome, but has Fabian Pascal approved of it yet? Because of course, if not, then it's functionality I would never actually use.
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by TallCowboy0614
    Cool! I'll have to look into other database engines

    Over time I became what I refer to as an "expert" on pointers and pointer references in "C" (and as proof of this expertise, I can direct you to any number of bathroom walls )...

    When C went away, a little part of the world died. Well, at least the part that made me feel superior in some way to others in my domain NOW I've had to find other ways to bolster my self image (like being a Steeler fan since the '70s )
    You do know what the definition of an expert is, don't you?

    -PatP

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    Sounds awesome, but has Fabian Pascal approved of it yet? Because of course, if not, then it's functionality I would never actually use.
    I'm not positive, but I don't think that Mr Pascal approves of himself, especially if someone else were to speak positively of him. That goes against his very nature.

    -PatP

  11. #11
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by Pat Phelan
    You do know what the definition of an expert is, don't you?

    -PatP
    You're not going to call me a Fabian, are you?
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by TallCowboy0614
    You're not going to call me a Fabian, are you?
    No, not at all! I like you.

    This works better verbally than online, but think about your days in High School... X is the mathematical symbol for the unknown. A spurt is just a drip, under pressure. Therefore, an expert must be an unknown drip, under pressure.

    -PatP

  13. #13
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    *LOL* gotcha...though you start talking about unknown spurts, and High School, and I was going a totally different direction there before I came back in

    Not much pressure here though...I like it I just shake my head at the kids we hire now...they think the job is so haaaarrrrdaa *spoken with the appropriate level of whine* - - This is the easiest job I've ever had, and at the highest pay...wow...maybe I'm in management and don't realize it!!!

    that would suck.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by TallCowboy0614
    that would suck.
    No, if you are condemned to manage, it would be kinder if you didn't realize it!

    -PatP

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "An expert is someone who has made all possible mistakes in a very narrow field." - Niels Bohr
    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
  •