Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    22

    Unanswered: converting to date only

    I have a function on a MSSQL 2000 db like the following:
    Code:
    create function GetDateOnly (@pInputDate datetime)
    returns datetime
    as
    begin
    	return cast(convert(varchar(10), @pInputDate, 111) as datetime)
    end
    which returns the date with the time all zeros ( '2006-05-09 00:00:00' ). I tried to implement this same function on an MSSQL 7 server and I get errors.
    Code:
    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'function'.
    How can I code something similar in version 7? I'm assuming it's a version difference that is causing my problems.

    Thanks,
    Randy

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    7.0 did not support user-defined functions.

    You could write a stored proc with an OUTPUT parameter to do this, but you could not include it in a SELECT statement as you can with a function.

    You will probably need to code the logic each time you need it. But I suggest you use this formula instead, which is more efficient than the CONVERT method you are using:

    Code:
    create function GetDateOnly (@pInputDate datetime)
    returns datetime
    as
    begin
    	return dateadd(day, datediff(day, 0, getdate()), 0)
    end
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Sep 2003
    Posts
    22
    thanks alot for the advise. I thought about stored procedure, but then like you mentioned I can't use it in a SELECT which was my whole goal. Back to the drawing board :-)

    Randy

Posting Permissions

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