Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8

    Unanswered: MSaccess Function to SQL function

    OK I'm new to this SQL server Stuff

    would like some Pointing down the right track

    I have this Function

    Code:
    Function MMPYear(F)
       Dim f1 As Integer
       Dim f2 As Integer
       If IsNull(F) Then Exit Function
       f1 = year(F)
       f2 = Month(F)
       If f2 > 7 Then
             MMPYear = Right(Str(f1 + 0), 4) + " - " + Right(Str(f1 + 1), 4)
       Else 
             MMPYear = Right(Str(f1 - 1), 4) + " - " + Right(Str(f1 + 0), 4)
       End If 
    End Function
    I wrote for MSaccess to tell me What year we were in base on a date
    I wrote this about ten years ago and it still working
    now I need to move it to the next level

    in msaccess I just mmpyear:MMPYear(indate)

    so today I would like to know how to convert it a a SQL function

    so I can call it from SQL server
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What does it actually return?

    For YYYY
    Code:
    SELECT Year(your_date)
    For YYYY-01-01
    Code:
    SELECT DateAdd(yy, DateDiff(yy, 0, your_date), 0)
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Have you tried anything yet you want to share with us? I have you looked at the CREATE FUNCTION article in books online? There are helpful examples near the bottom.

    George,

    It looks more like a Fiscal Year- like calculation. Personally I would set the definitions of the return values up in a table and join to it, but I very rarely use functions. They tend to cause me performance issues.
    Last edited by Thrasymachus; 12-01-08 at 16:54.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    if I put
    aaa = MMPYear(#5/5/2008#)

    aaa = 2007 - 2008
    and if
    aaa = MMPYear(#8/5/2008#)

    aaa = 2008 - 2009
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Call me Mr Thicky Pants if you must but isn't:
    Code:
    Right(Str(f1 + 0), 4)
    exactly the same as
    Code:
    f1
    (given VBA will implicitly cast)or
    Code:
    str(f1)
    if you want to be explicit.
    Anyhoo:
    Code:
    CAST(YEAR(@date) - CASE WHEN MONTH(@date) > 7 THEN 0 ELSE 1 END AS VARCHAR) + ' - ' + CAST(YEAR(@date) + CASE WHEN MONTH(@date) > 7 THEN 1 ELSE 0 END AS VARCHAR)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    CREATE FUNCTION dbo.MMPYear(
       @f		DATETIME
       ) RETURNS VARCHAR(9)
    AS BEGIN
    
    RETURN (SELECT Convert(CHAR(4), y) + '-' + Str(1 + y, 4)
       FROM (SELECT Year(@f) - CASE WHEN 7 < Month(@f) THEN 1 ELSE 0 END AS y) AS z
    ) END
    -PatP

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    oooooooooooooooooooooooooooo

    that is to easy

    Thanks

    that open the door for me I have a number of Msaccess function i can ad to the SQL function list

    will keep you up to date.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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