Solved

User-Defined Function with Date

Posted on 2004-03-29
27
1,253 Views
Last Modified: 2012-06-21
I need to have the following funtion in Access converted into an MS SQL Server 2000 User-Defined Function. Would anyone be able to help? Or Would anyone know a book that offers the most often used user-defined functions such as first/last day of month, quarter, year, with an OPTIONAL parameter to pass, in MS SQL Server 2000?

Appreciated!!!

'   Get the last day of current month.
Public Function fncGetEndOfMonth(Optional dtmDate As Date = 0) As Date
   
    ' Did the caller pass in a date? If not, use the current date.
    If (dtmDate = 0) Then
        dtmDate = Date
    End If
       
    fncGetEndOfMonth = DateSerial(Year(dtmDate), Month(Date) + 1, 0)

End Function
0
Comment
Question by:YLI
  • 9
  • 5
  • 5
  • +3
27 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 10705406
User-defined functions don't support optional parameters
You'll need to pass a datetime parameter to the function because SQL Server won't let you use
non-deterministic fuctions like getdate() inside a function

Hilaire
0
 
LVL 14

Assisted Solution

by:adwiseman
adwiseman earned 100 total points
ID: 10705469
Here's the best I can do.  You can't have optional parameters so your going to have to alter your code to always pass something in.  Passing in null is valid, it will return null though.

create Function dbo.fncGetEndOfMonth(@dtmDate As smalldatetime)
RETURNS smalldatetime
As
BEGIN
   

 RETURN DATEADD(day, -1, DATEADD(month, 1, CONVERT(varchar, datepart(mm, @dtmDate)) + '/01/' + CONVERT(VARCHAR, datepart(yyyy, @dtmDate))))

End

--Select dbo.fncGetEndOfMonth(getdate())

0
 

Author Comment

by:YLI
ID: 10705473
That's what I learned. In this case, what would be an equivalent to the DateSerial() function to get the poblem solved? Could you offer a complete user-defined function to get the end of any month?
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 10705485
My function uses the month, and year passed in in the date, and uses the first of the month.  It then adds 1 month, and subtracts 1 day.  This gets you the last day of the month for the month of the date passed in.
0
 
LVL 26

Assisted Solution

by:Hilaire
Hilaire earned 100 total points
ID: 10705501
Here's mine

create function fncGetEndOfMonth(@dtmDate datetime) returns datetime
as
begin
return(dateadd(d,-1*day(dateadd(m,1,@dtmDate)),convert(varchar(10),dateadd(m,1,@dtmDate),102)))
end
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 10705531
There's no good equivelent of DateSerial.  

Datepart will give you the parts of the date you want.  There are also functions like year(), month() and day() that do the same thing.

dateadd lets you add a given unit to a date.

See books online for "date and time functions".  There are several others than can be of use when working with dates.
0
 
LVL 7

Assisted Solution

by:ChrisFretwell
ChrisFretwell earned 150 total points
ID: 10705545
Not sure of a book with all that, but its not complicated.
To find the last day of the month (just in a single line) then

select (DATEADD(d, -DAY(DATEADD(m,1,isnull(@myDate,getdate())),DATEADD(m,1,isnull(@myDate,getdate())))

you can wrap that in a fuction if you dont want to code it everywhere, however, getdate is not permitted within a UDF so it cant be an optional parameter if you put it in a fuction. so in a fuction the line would be

create function LastOfMonth (@mydate as datetime )
returns datetime
as
begin
return (DATEADD(d, -DAY(DATEADD(m,1,@myDate)),DATEADD(m,1,@myDate)))
end

What you could do is in the call to the function check for nulls before calling so


select field1, field2, lastofmonth(isnull(datefield,getdate()) from table

if that gives you an error then

select field1, field2, case if datefield is null then lastofmonth(getdate()) else lastofmonth(datefield) end from table



0
 
LVL 14

Expert Comment

by:adwiseman
ID: 10705587
Never knowticed that Hilaire.

Dateadd(m, 1, <date>) recognises that if you add 1 month to 01/31/2004, there's no 02/31/2004, it returns the last day of the month 02/29/2004.

That's my new knowledge for the day!

0
 

Assisted Solution

by:janu_s
janu_s earned 50 total points
ID: 10705670
Here are the function :

1. For First Day of month
CREATE FUNCTION dbo.FirstDayMon
  ( @fDate datetime )
RETURNS datetime
AS
BEGIN
  RETURN ( convert(datetime,convert(char(6),@fDate,112)+'01') )
END
GO

Example:

select dbo.FirstDayMon(getdate())


2. For Last Day of month

CREATE FUNCTION dbo.LastDayMon
  ( @fDate datetime )
RETURNS datetime
AS
BEGIN
  RETURN ( dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@fDate )+1, 0)) )
END
GO
Example:
select dbo.LastDayMon(getdate())

3. For Quater of year
CREATE FUNCTION dbo.Quater
  ( @fDate datetime )
RETURNS int
AS
BEGIN
  RETURN ( DATEPART(Qq, @fDate) )
END
GO

4.Year

CREATE FUNCTION dbo.CurrYear
  ( @fDate datetime )
RETURNS int
AS
BEGIN
  RETURN ( DATEPART(Yy, @fDate) )
END
GO
0
 

Author Comment

by:YLI
ID: 10705698
Thank you all (Hilaire, adwiseman, and ChrisFretwell ) for the solutions. But would it be possible to create a package of functions (or even combining a function with a stored procedure) to provide a default GETDATE() insted of having the date checked in a SELECT statement when calling it?
0
 

Author Comment

by:YLI
ID: 10705724
Hi, janu_s, appreciated all of the functions you provided. But what I asked was the FIRST DAY / the LAST DAY of each month, quarter, or year...
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10705769
You can use an sproc to give you all that. It cant be called inline in a select statement like a function could, but it could access a default date.
With an sproc, you could pass an optional date and return all of the date values for use later. When you call the sproc, assign the results to variables for later use.

so something like
create proc usp_datevalues @myDate datetime = null
as

select select (DATEADD(d, -DAY(DATEADD(m,1,isnull(@myDate,getdate())),DATEADD(m,1,isnull(@myDate,getdate())))  as LastofMonth,
         (convert(datetime,convert(char(6),@mydate,112)+'01') )  as FirstofMonth,
        etc

0
 

Expert Comment

by:janu_s
ID: 10705796
getdate is a built in function.You cannot use a built-in non-deterministic function within a user defined function.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:YLI
ID: 10706056
Hi, ChrisFretwell:

Would you provide COMPLETE coding for that sp? It gave me a Day function related error...

Thanks,
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10706066
that was my goof, I had select select in there, remove one of the selects and it will work for the first 2 last/first day of month. I'll get the rest soon. I have then around somewhere.

chris
0
 

Author Comment

by:YLI
ID: 10706151
I realized that extra 'select' and got rid of it before testing it and got the error. Could you limit the sp to return the last day of month only?
0
 
LVL 7

Assisted Solution

by:ChrisFretwell
ChrisFretwell earned 150 total points
ID: 10706183
sure.

create proc usp_datevalues @myDate datetime =  null
as

select @mydate = getdate() where @mydate is null
select (DATEADD(d, -DAY(DATEADD(m,1,@myDate)),DATEADD(m,1,@myDate)))
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 10707166
There is a work-around available to get current date from w/in a function.  I have used it several times.  If interested, please let me know.
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 10707177
I'm interested?
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10707180
Me too!
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 100 total points
ID: 10707313
OK.  First, basically you have to define the server as a linked-server to itself.  For example:

-- from a connection to server = yourServerNameHere:

EXEC sp_addLinkedServer 'yourServerNameHere', 'SQL Server'

-- as you no doubt know, proper parameters for cmd below will vary
--     depending on your specific config.;
-- just set up some id that will work for the connection;
-- for the sample below, assume I set up 'user1' with connection authority only
-- (can connect to all db, deny read, deny write)
EXEC sp_addLinkedSrvLogin 'yourServerNameHere', 'FALSE', NULL, 'User1', 'Password1'


Now create and run this function:


CREATE function getCurrentDate (
)
RETURNS datetime
AS
BEGIN
DECLARE @currentDate DATETIME
SET @currentDate = (SELECT CurrentDate FROM OPENQUERY(yourServerNameHere, 'SELECT GETDATE() AS CurrentDate'))
RETURN @currentDate
END --FUNCTION

Hopefully it worked!

Btw, naturally you don't have to read the current datetime into a variable, could have done just a return, but this shows that you *can* save it in a variable.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 10707338
CORRECTION:

-- (can connect to all db, deny read, deny write)

should be:

-- (can connect to its *default* db, but with deny read, deny write)
0
 

Author Comment

by:YLI
ID: 10707647
Thank you.

I ran the following rom the query analyzer (changing arguments appropriately - note I assume the 'yourservernamehere' was an alias - having looked at the help file on this stored procedure.)

"EXEC sp_addLinkedServer 'yourServerNameHere', 'SQL Server'" and
"EXEC sp_addLinkedSrvLogin 'yourServerNameHere', 'FALSE', NULL, 'User1', 'Password1'"

and got:

"(1 row(s) affected)


(1 row(s) affected)"

and
"(0 row(s) affected)


(1 row(s) affected)


(0 row(s) affected)


(1 row(s) affected)"

respectively (this looks like it works at this point)

However, I'm getting an error ('SQL Server does not exist or access denied') when checking syntax on the function (I did change the servername argument appropriately).  So I'm not sure if I ran the first 2 commands correctly (linking/logging into the server), or if my syntax in the function is correct.



0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 10707689
You need to change yourServerNameHere to whatever the name of your server actually is, for example, server1.

Also, when adding the login, naturally you need to make sure that a login with that name exists and that it can connect to its default db (usually master).
0
 

Author Comment

by:YLI
ID: 10707759
Thanks again.

Actually, I tried the actual server name first.  I had the same result which prompted me to check help and there I decided that the server name should be aliased (incorrect assumption!)

I'm not sure how to check "...can connect to its default db (usually master)", but will try running a simple select statement in a query screen after sending this.

0
 

Author Comment

by:YLI
ID: 10707832
Ooopss...

Originally, I ran the

EXEC sp_addLinkedServer 'yourServerNameHere', 'SQL Server'
EXEC sp_addLinkedSrvLogin 'yourServerNameHere', 'FALSE', NULL, 'User1', 'Password1'

commands with the DATABASE name (not SERVER name).  I've since corrected and instead get the following error:

'Error 7411 Server [myservernamehere] is not configured for DATA ACCESS'
0
 

Author Comment

by:YLI
ID: 10715632
ScottPletcher:

Your solution would be the best if I could get the linked server part to work. Anything you could think of on the error 7411 I got?

Any other ideas from other MS SQL Server gurus about a most appropriate/close 'translation' on my Access function?

If no other solutions by 14:00 pm EST, I would suggest that the admin of the Expert Exchange Community forward my humble suggestion to Microsoft SQL Server 2000/YUKON developers:

-- Any sql server programmers who would NOT use these basic functions more or less? Wouldn't it be nice if we would NOT have to spend so much time figuring them out by ourselves (good challenge though)? Could MS make most frequently used UDFs such as the FIRST DAY/LAST DAY of a work week, calendar week, month, quarter, and Year part of the HANDY and BUILT-IN functions on the SQL Server, even though it could make some SQL Server programmers idle? :)

yli
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now