Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2

    Unanswered: User-defined function woes

    Hi All

    In this case, the user is me.

    I'm working on moving some processing from the ADP front end to the SQL back end in a split application (Access 2k front end, SQL 2k back end). Part of this involves translating some work that's done to data in recordsets on a row-by-row basis into a SQL function.

    Now, part of this involves dealing with low date/time values (1899-12-31 00:00:00 to 1900-01-06 23:59:59). The idea is that I have a date that's in the near future, and a date/time as above. I need to take the time element of the early value and combine it with the near future date value.

    So far, based on the VBA code that achieves this, I have this:
    Code:
    CREATE FUNCTION dbo.fnPlanningDepartureTime
    	(
    	@SchDate DATETIME
    ,	@PlnDptTime DATETIME
    	)  
    RETURNS DATETIME
    AS  
    BEGIN 
    
    DECLARE
    	@Return DATETIME
    ,	@SchDtStr NVARCHAR(20)
    ,	@SchHHStr NVARCHAR(2)
    ,	@SchMMStr NVARCHAR(2)
    ,	@NewDate DATETIME
    
    
    SET @SchDtStr = CAST(@SchDate AS NVARCHAR(20))
    SET @SchHHStr = RIGHT('00' + CAST(DATEPART(hh, @PlnDptTime) AS NVARCHAR(2)), 2)
    SET @SchMMStr = RIGHT('00' + CAST(DATEPART(mi, @PlnDptTime) AS NVARCHAR(2)), 2)
    SET @NewDate = CAST(@SchDtStr +' '+@SchHHStr+':'+@SchMMStr AS DATETIME)
    
    IF DATEDIFF(dd, @SchDate, @NewDate) < 10
    	SET @NewDate = DATEADD(dd, 1, @NewDate)
    
    SET @Return = @NewDate
    
    RETURN @Return
    
    END
    When I try to run this, I get the following error message:
    Server: Msg 8115, Level 16, State 2, Procedure fnPlanningDepartureTime, Line 21
    Arithmetic overflow error converting expression to data type datetime.
    Line 21 is the initial SET @NewDate statement.

    What am I doing wrong?
    Last edited by weejas; 02-16-12 at 12:50.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE
       @d1      DATETIME
    ,  @d2      DATETIME
    
    SET @d1 = '1899-12-31 01:02:03.123'
    SET @d2 = GetDate()
    
    SELECT @d1, @d2, DateAdd(day, DateDiff(day, @d1, @d2), @d1)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ummm...you want to "combine" 2 dates

    What do you mean by Combine?
    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.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser View Post
    What do you mean by Combine?
    Oh come on now, you've "double dated" before!

    I'm pretty sure that my solution will do what weejas wants, combine the date part from one datetime with the time part of another datetime.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    @Pat
    Brilliant, thank you! Much more elegant than what I was trying to achieve!

    @Brett
    I need to take the time element of the early value and combine it with the near future date value.
    I'm not sure how much more clearly I can put it, but I want to take (for example), '2012-02-20' and '1900-01-01 09:30:00', and end up with '2012-02-20 09:30:00'. Pat's code does this!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You don't know how more clearly..how about an explination as to WHY this even makes sense

    That's where you lost me

    And Pat's Comments are directed at me, and the translation is "Come on, you know people don't make sense)
    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.

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    The rationale behind it is that for two tables, there are various records that are used as templates for new records. These template records are denoted by the early dates and times listed in my initial post. When creating new records, the new date comes from an external source, but the time is dictated by the template.

    As for making sense... It's a business requirement - outside of the transport office, the whole application makes little sense!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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