[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

User-Defined Function with Date

Posted on 2004-03-29
27
Medium Priority
?
1,278 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:Yongshu Li
  • 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 400 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:Yongshu Li
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 400 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 600 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 200 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:Yongshu Li
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:Yongshu Li
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
 

Author Comment

by:Yongshu Li
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:Yongshu Li
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 600 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 70

Expert Comment

by:Scott Pletcher
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 70

Accepted Solution

by:
Scott Pletcher earned 400 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 70

Expert Comment

by:Scott Pletcher
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:Yongshu Li
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 70

Expert Comment

by:Scott Pletcher
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:Yongshu Li
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:Yongshu Li
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:Yongshu Li
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

834 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