Results 1 to 5 of 5
  1. #1
    RayMilhon is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066

    Function Syntax

    I have a query I'm working on that I need to create a function that has 1 parameter which will be an integer from 1 to 12. The function needs to return the first of the month



    Sample:
    firstofthemonth(2) should return 01/01/2013
    Firstofthemonth(4) should return 11/01/2012

    I know I can do it with Cast(str(month(dateadd(m,-2,getdate()))) + '01' + str(year(dateadd(m,-2,getdate()))) as date) But in this particular query I'm going to have to do that a number of times
    1 in the Select, twice in the where and 1 in the group by and I have to union for the last 12 months So it's 48 times and I'm not that good of a typist. I was thinking if I had something similar to

    Function firstofthemonth(byref m as int) as date

    d1 = dateadd("m",-m,Now())
    firstofthemonth = format(month(d1) & "/01/" & year(d1),"mm/dd/yyyy")

    End function

    However that syntax is good for Access but I have to do this in SQL Server and I'm not sure of the syntax any help is appreciated.

  2. #2
    RayMilhon is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066
    Got the syntax figured out but when I include the function in a query I get an error that the built in function doesn't exist. Well I know that it's a user defined function. Is there some secret to calling a user function?????

  3. #3
    ArviL is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2013
    Posts
    1
    Hi

    This isn't very easy - there are issues with leap years!
    I myself have designed a function, which returns a date from year, month and day numbers - and it is very easy to get the first/last of any month from there.

    (I have all my UDF's in special database <utilities>, so it is easier to use them through all databases)
    -----------
    USE [utilities]
    GO
    /****** Object: UserDefinedFunction [dbo].[udf_Date] Script Date: 03/07/2013 13:33:21 ******/
    SET
    ANSI_NULLSON
    GO
    SET
    QUOTED_IDENTIFIERON
    GO
    /-- =============================================
    /-- Author: Arvi Laanemets
    /-- Create date: 12.05.2012
    /-- Description: Returns a date from given year, month and day
    /-- =============================================
    ALTER
    FUNCTION [dbo].[udf_Date]
    (

    /-- Add the parameters for the function here
    @Year
    INT, @Month INT, @Day INT
    )
    RETURNS datetime
    AS
    BEGIN
    /-- Declare the return variable here
    DECLARE @Date DATETIME
    /-- Add the T-SQL statements to compute the return value hereSET @Date =
    (CASE
    WHEN @Month = 2 AND @Day >28 AND @Day < 31 THEN
    DateAdd(yyyy,@Year-1900,DateAdd(m,@Month,0))- 1 -DateAdd(yyyy,@Year-1900,DateAdd(m,@Month-1,31-1))
    ELSE 0
    END)+
    DateAdd(yyyy,@Year-1900,DateAdd(m,@Month-1,@Day-1))
    /-- Return the result of the function
    RETURN @Date
    END
    -----------------------------------
    Now, I can from anywhere call the function, p.e.
    SELECT utilities.dbo.udf_Date(2012,11,1), or
    SELECT utilities.dbo.udf_Date(Year(GetDate()),MONTH(GetDa te())-4,1), or
    SELECT utilities.dbo.udf_Date(2012,12,-1), or
    SELECT utilities.dbo.udf_Date(Year(GetDate()),MONTH(GetDa te())-4+1,-1) etc.

  4. #4
    Sven552 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2013
    Posts
    1
    Quote Originally Posted by RayMilhon View Post
    I have a query I'm working on that I need to create a function that has 1 parameter which will be an integer from 1 to 12. The function needs to return the first of the month

    Sample:
    firstofthemonth(2) should return 01/01/2013
    Firstofthemonth(4) should return 11/01/2012

    I know I can do it with Cast(str(month(dateadd(m,-2,getdate()))) + '01' + str(year(dateadd(m,-2,getdate()))) as date) But in this particular query I'm going to have to do that a number of times
    1 in the Select, twice in the where and 1 in the group by and I have to union for the last 12 months So it's 48 times and I'm not that good of a typist. I was thinking if I had something similar to

    Function firstofthemonth(byref m as int) as date

    d1 = dateadd("m",-m,Now())
    firstofthemonth = format(month(d1) & "/01/" & year(d1),"mm/dd/yyyy")

    End function

    However that syntax is good for Access but I have to do this in SQL Server and I'm not sure of the syntax any help is appreciated.
    Ray -

    You need to put the custom bit of function in a Module.... I'm guessing that you included the function originally in a form?

    Define the function as Public within the module like this:
    Public Function firstofthemonth(ByRef m As Integer) As Date

    d1 = DateAdd("m", -m, Now())
    firstofthemonth = Format(Month(d1) & "/01/" & Year(d1), "mm/dd/yyyy")
    End Function

    Then, once you are in the query, begin to build the query with the expression builder. Under Functions, you will see "Built In Functions" as well as the name of your database. Double click on the Database name and that should expand to show you the 'custom' function you just built.

    Click image for larger version. 

Name:	Custom Function in Query Builder.jpg 
Views:	10 
Size:	77.6 KB 
ID:	11592

  5. #5
    RayMilhon is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066
    Arvin leap years are irrelevant I don't need the last day of the month I only need the first every month has a first. I have the function programmed I'm just having an issue using it. If I include the Function in the Query I get an error that it doesn't exist yet I can see that it does.

    Sven, I originally did this in a module in Access 2010 My issue is that I have to use this in a Function within SQL Server 2008 R2.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. simple syntax for putting a variable into a function
    By markjkubicki in forum Programming
    Replies: 2
    Last Post: 11-02-2012, 09:46 AM
  2. IIF function syntax
    By kris335 in forum Access
    Replies: 5
    Last Post: 09-13-2011, 11:14 AM
  3. Incomplete Syntax Clause (syntax error)
    By ajetrumpet in forum Programming
    Replies: 4
    Last Post: 09-11-2010, 10:47 AM
  4. Please help SQL Syntax
    By jordanturner in forum Access
    Replies: 4
    Last Post: 09-02-2010, 08:05 AM
  5. Want function to get current function name
    By Davis DeBard in forum Programming
    Replies: 2
    Last Post: 08-13-2009, 05:02 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums