Help with Defined Function using dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with Defined Function using dates

Author

Topic

LaDeana
Starting Member

2 Posts

Posted - 2008-09-15 : 15:39:02

I have a simple user defined function which finds a value in a table using passed parameters from a view.

It is returning null in some instances and I don't see why:

CREATE FUNCTION [dbo].[getmailingperiod] (@Inputdate varchar(20), @Market as varchar (6), @Years as int)
RETURNS int AS

BEGIN
Declare @MailingPeriod as int

Set @MailingPeriod = (select mailingperiod from tblMailingPeriods
WHERE (StartDate <= CONVERT(DATETIME, @inputdate, 102))
AND (EndDate >= CONVERT(DATETIME, @inputdate, 102))
AND (Market = @market) AND (MPYear = @Years))

Return @MailingPeriod
END

Here is how I'm calling the function

SELECT dbo.getmailingperiod(dbo.tblContractDetail.InHomeDate, tblContractDetail.FranchiseID, YEAR(dbo.tblContractDetail.InHomeDate)) AS MailingPeriod
FROM dbo.tblContractDetail

The function works if I run it manually through the query analyzer but not when using it against a table.

The inhomedate in tblcontractdetail is defined as datetime. I'm sure that it's probably the way the date is being converted, but I'm not sure where to fix it.

Thanks for any help you can send me!!

LaDeana

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-15 : 15:42:31

What are the data types of StartDate and EndDate?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

LaDeana
Starting Member

2 Posts

Posted - 2008-09-15 : 16:00:51

quote:
Originally posted by tkizer

What are the data types of StartDate and EndDate?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

They are both datetime as well.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-15 : 16:05:53

Try this instead:

WHERE (StartDate <= DATEADD(Day, DATEDIFF(Day, 0, @inputdate), 0)
AND (EndDate >= DATEADD(Day, DATEDIFF(Day, 0, @inputdate), 0)
AND (Market = @market) AND (MPYear = @Years))

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-15 : 16:51:49

The @InputDate parameter should be DateTime, not VARCHAR! Always, always use proper data types for your columns, variables and parameters.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources