We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

User-Defined Function with Date

Yongshu Li
Yongshu Li asked
on
Medium Priority
1,302 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
Comment
Watch Question

Commented:
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
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Yongshu LiIT Project Manager

Author

Commented:
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?
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.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
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.
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
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!

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Yongshu LiIT Project Manager

Author

Commented:
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?
Yongshu LiIT Project Manager

Author

Commented:
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...
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

Commented:
getdate is a built in function.You cannot use a built-in non-deterministic function within a user defined function.
Yongshu LiIT Project Manager

Author

Commented:
Hi, ChrisFretwell:

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

Thanks,
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
Yongshu LiIT Project Manager

Author

Commented:
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?
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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.
I'm interested?
Me too!
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
CORRECTION:

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

should be:

-- (can connect to its *default* db, but with deny read, deny write)
Yongshu LiIT Project Manager

Author

Commented:
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.



Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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).
Yongshu LiIT Project Manager

Author

Commented:
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.

Yongshu LiIT Project Manager

Author

Commented:
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'
Yongshu LiIT Project Manager

Author

Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.