locked
Erreur lors d'execution d'une fonction RRS feed

  • Question

  • Bonjour,

    voilà le code sql pour la fonction que j'ai atteint l'erreur suivant lorsque je veux l'executer:

     

    'DtReprise' n'est pas une option nom de fonction intégrée reconnue.

     

     

     

    CREATE FUNCTION DtReprise 
    
    (
    
    	@nbr_jour int,
    
    	@dt_cessation datetime
    
    )
    
    RETURNS datetime
    
    AS
    
    BEGIN
    
    	declare @dtreprise datetime
    
    
    
    	IF DATEPART(dw, @dt_cessation) + (@nbr_jour % 5) <= 6
    
    		SET @dtreprise = DATEADD(dd, (@nbr_jour / 5) * 7 + (@nbr_jour % 5), @dt_cessation)
    
    	ELSE
    
    		SET @dtreprise = DATEADD(dd, (@nbr_jour / 5) * 7 + (@nbr_jour % 5) + 2, @dt_cessation)
    
    		
    
    	RETURN @dtreprise
    
    END
    

    et pour executer la fonction j'ai utilisé le code sql suivant:

    SELECT DtReprise(23, '26/09/2011')as 'date de reprise'

    je sais pas ou est le problème mais je pense le code que j'utilise pour l’exécution est correcte alors !!!

    merci de me donner une réponse.

     

     

     

     

    • Moved by Alex Feng (SQL) Monday, September 26, 2011 11:11 AM move to an appropriate forum (From:Database Mirroring)
    Monday, September 26, 2011 10:16 AM

Answers

  • hi

    There is an issue with date format. it should be mm/dd/yyy then it works fine.

     

    try this :

    SELECT dbo.DtReprise(23, '09/26/2011')

     

    it works fine and gave the result as :

    "2011-10-27 00:00:00.000".


    Regards,

    Paresh


    • Edited by Paresh Patel Monday, September 26, 2011 11:46 AM
    • Marked as answer by ahbariamina Monday, September 26, 2011 2:35 PM
    Monday, September 26, 2011 11:46 AM
  • Try Following

    Set DateFormat dmy
    SELECT dbo.DtReprise(23, '26/09/2011')
    
    



    Mark as Answer If Reply Was Helpful
    Thanks
    Kuldeep Bisht
    Technical Lead @ Simplion Technologies
    Blog : www.dbsimplified.com
    • Marked as answer by ahbariamina Monday, September 26, 2011 2:35 PM
    Monday, September 26, 2011 12:15 PM
  • You need to specify the schema when using a scalar function in a SELECT statement:

     

     

    SELECT dbo.DtReprise(23, '26/09/2011')as 'date de reprise';
    

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Edited by Dan GuzmanMVP Monday, September 26, 2011 11:43 AM correct html formatting
    • Marked as answer by ahbariamina Monday, September 26, 2011 2:35 PM
    Monday, September 26, 2011 11:42 AM
  • or

    select dbo.DtReprise(23, '20110926')


    Monday, September 26, 2011 12:27 PM

All replies

  • Hi,

    I created the same function, and got the following error message when executing:

    Msg 195, Level 15, State 10, Line 1
    'DtReprise' is not a recognized built-in function name.
    


    Since this is a scalar-valued function, we may not be able to run it using 'SELECT function_name' directly. If I changed it to the following, it can execute successfully:

    DECLARE @MyDatetime datetime;
    EXEC @MyDatetime = DtReprise 23, '2011-09-26'
    SELECT @MyDatetime AS 'Datetime returned';
    GO
    

    More info: http://msdn.microsoft.com/en-us/library/ms175562.aspx


    PS. Since this issue is more related to T-SQL, therefore I am moving it to T-SQL form; and please post your replies in English since this is a English forum.

    Thanks,


    Best Regards
    Alex Feng

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Monday, September 26, 2011 11:10 AM
  • hello,
    Here I test your code and i get as message :


    Msg 8114, Level 16, State 5, ProcedureDtReprise, Line 0
    Error converting data type varchar to datetime.
    (1 row (s) affected (s)).
    and the result is null when the expected result is '27/ 10/2011 concider in the example you give me.

    Monday, September 26, 2011 11:31 AM
  • You need to specify the schema when using a scalar function in a SELECT statement:

     

     

    SELECT dbo.DtReprise(23, '26/09/2011')as 'date de reprise';
    

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Edited by Dan GuzmanMVP Monday, September 26, 2011 11:43 AM correct html formatting
    • Marked as answer by ahbariamina Monday, September 26, 2011 2:35 PM
    Monday, September 26, 2011 11:42 AM
  • hi

    There is an issue with date format. it should be mm/dd/yyy then it works fine.

     

    try this :

    SELECT dbo.DtReprise(23, '09/26/2011')

     

    it works fine and gave the result as :

    "2011-10-27 00:00:00.000".


    Regards,

    Paresh


    • Edited by Paresh Patel Monday, September 26, 2011 11:46 AM
    • Marked as answer by ahbariamina Monday, September 26, 2011 2:35 PM
    Monday, September 26, 2011 11:46 AM
  • Try Following

    Set DateFormat dmy
    SELECT dbo.DtReprise(23, '26/09/2011')
    
    



    Mark as Answer If Reply Was Helpful
    Thanks
    Kuldeep Bisht
    Technical Lead @ Simplion Technologies
    Blog : www.dbsimplified.com
    • Marked as answer by ahbariamina Monday, September 26, 2011 2:35 PM
    Monday, September 26, 2011 12:15 PM
  • or

    select dbo.DtReprise(23, '20110926')


    Monday, September 26, 2011 12:27 PM
  • This is an English language forum. Can you change the thread title and your post to English? Thanks.
    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Monday, September 26, 2011 2:29 PM