Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Posts
    205

    Unanswered: GETDATE() with a user defined function

    Hi,

    I have a requirement where i need to get the current time/date within a Function. As getDate function is a non deterministic function it can not be used with in a function. Your guidence in this regard is greately appreciated.

    Regards,
    Samcute.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Nope sorry...

    Why not just use GetDate() instead of a Function? Or pass it in as a parameter to a function?

    Anyone know how to create an external sproc?

    Code:
    USE Northwind
    GO
    
    CREATE PROC mySproc99 @myDate99 datetime OUTPUT AS SELECT @myDate99 = GetDate()
    GO
    
    DECLARE @myDate99 datetime
    
    EXEC mySproc99 @myDate99 OUTPUT
    
    SELECT @myDate99
    
    CREATE FUNCTION udf_myFunction99(@x datetime)
    RETURNS datetime
    AS
      BEGIN
    	DECLARE @myDate99 datetime
    	EXEC mySproc99 @myDate99 OUTPUT
    	RETURN @myDate99
      END
    GO
    
    SELECT dbo.udf_myFunction99(0)
    GO
    
    DROP FUNCTION udf_myFunction99
    DROP PROC mySproc99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    Nope sorry...

    Why not just use GetDate() instead of a Function? Or pass it in as a parameter to a function?

    Anyone know how to create an external sproc?

    Code:
    USE Northwind
    GO
    
    CREATE PROC mySproc99 @myDate99 datetime OUTPUT AS SELECT @myDate99 = GetDate()
    GO
    
    DECLARE @myDate99 datetime
    
    EXEC mySproc99 @myDate99 OUTPUT
    
    SELECT @myDate99
    
    CREATE FUNCTION udf_myFunction99(@x datetime)
    RETURNS datetime
    AS
      BEGIN
    	DECLARE @myDate99 datetime
    	EXEC mySproc99 @myDate99 OUTPUT
    	RETURN @myDate99
      END
    GO
    
    SELECT dbo.udf_myFunction99(0)
    GO
    
    DROP FUNCTION udf_myFunction99
    DROP PROC mySproc99
    GO
    Pass GetDate() as a function argument.

    Yes, I can write extended stored procedures. No, it isn't worth it for the average user, since it is a lot of work and you can break nearly all of the rules in an xp. If you don't know how/why you're breaking the rules, that can be a REALLY bad thing!

    -PatP

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Pat Phelan
    Pass GetDate() as a function argument.
    -PatP
    Did I already say that?


    Yes, I can write extended stored procedures. No, it isn't worth it for the average user, since it is a lot of work and you can break nearly all of the rules in an xp. If you don't know how/why you're breaking the rules, that can be a REALLY bad thing!
    oooo scary....

    Did you see Hendersons work for Arrays in SQL Server?

    And you're right...I chickened out....was going to build them....but I figured why bother...a tables an array, and with the table variable is was even easier...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    If you don't know how/why you're breaking the rules, that can be a REALLY bad thing!
    Yeah...I called that "midlife crisis", and a few thousands of dollars and several handcuff burns later...I tend to agree Although..."that which does not kill us...."
    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

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Thousands of dollars ?!?! Was she worth it???

    -PatP

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    *LOL* Nope...not even a "she" dammit (though, it's probably best to assume the "she" connection first in any such situations)...what a wasted midlife crisis...just partyin' too much with m'homeboys Jose' C and Jack D
    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
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Picture I'm getting includes 4 wheels...a tree...flashing lights and MASSIVE amounts of alcohol...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    Picture I'm getting includes 4 wheels...a tree...flashing lights and MASSIVE amounts of alcohol...
    No, no, no! He didn't say it was a normal Tuesday afternoon. This was something special!

    I figured with thousands of dollars and handcuff burns, there just HAD to be a "she" in there somewhere!

    You do have to be wary when playing with Jack and Jose. Those fellas play kinda rough sometimes. Glad to know that you survived it anyway!

    -PatP

  10. #10
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Thanks...and no, no trees or blood involved, but Gov. Davis was nice enough to send me on a 14-month vacation clearing brush and fighting fires for the state over it

    Perhaps needless to say, I lost quite a few brain cells that would come in handy now trying to figure out how to debug in SQL Server, and how to put non-deterministic functions into user-defined functions!

    (hey, how's THAT for coming back on-topic )
    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

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by TallCowboy0614
    Gov. Davis was nice enough to send me on a 14-month vacation clearing brush and fighting fires for the state over it
    Did you get a room with the view of the lake?

    This is good Yak Corral stuff....

    And Pat do you use debugger or not?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    On my own code, I've only used the debugger once or twice in order to show other people how my code worked. I've never needed it for actually debugging code that I've written.

    The debugger has come in handy more than once trying to finger out what in blazes some of the code that I've inherited actually does. Some of that stuff can be most charitably described as bizarre.

    -PatP

Posting Permissions

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