April 20, 2011 at 6:38 am
I created the following function to pull the min or max date from a table of dates. It does work. However, if it does not find a record, it returns Null. I would like the value returned to be 0 due to other functionality further down the line, specifically in the ASP.NET code that this db backs. I tried to use Coalesce as in RETURN = Coalesce(@ShowDate,0), however that returned an error indicating that there was a clash between int and date datatypes.
CREATE TABLE [dbo].[ShowDates](
[ShowNumber] [varchar](8) NOT NULL,
[DateCategoryId] [smallint] NOT NULL,
[MilestoneDate] [date] NULL,
[ConfirmationStatus] [varchar](4) NOT NULL,
[MilestoneTimeStart] [time](0) NULL,
[MilestoneTimeEnd] [time](0) NULL,
[EntryUserId] [nvarchar](128) NULL,
[EntryDateTime] [datetime] NULL,
[ModifiedUserId] [nvarchar](128) NULL,
[ModifiedDateTime] [datetime] NULL,
[Id] [int] IDENTITY(1000,1) NOT NULL,
CONSTRAINT [PK_ShowDates] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ShowDates] ADD CONSTRAINT [DF_ShowDates_Status] DEFAULT (N'TEN') FOR [ConfirmationStatus]
GO
CREATE FUNCTION [dbo].[getShowDate]
(
@ShowNumber varchar(8),
@DateCategoryId smallint,
@Mode char(3)
)
RETURNS Date
AS
BEGIN
DECLARE @ShowDate Date
IF @Mode = 'Max'
BEGIN
SELECT @ShowDate = Max(MilestoneDate) FROM ShowDates WHERE ShowNumber = @ShowNumber AND DateCategoryId = @DateCategoryId
END
IF @Mode = 'Min'
BEGIN
SELECT @ShowDate = Min(MilestoneDate) FROM ShowDates WHERE ShowNumber = @ShowNumber AND DateCategoryId = @DateCategoryId
END
RETURN @ShowDate
END
April 20, 2011 at 7:34 am
david.holley (4/20/2011)
I created the following function to pull the min or max date from a table of dates. It does work. However, if it does not find a record, it returns Null. I would like the value returned to be 0 due to other functionality further down the line, specifically in the ASP.NET code that this db backs. I tried to use Coalesce as in RETURN = Coalesce(@ShowDate,0), however that returned an error indicating that there was a clash between int and date datatypes.
CREATE TABLE [dbo].[ShowDates](
[ShowNumber] [varchar](8) NOT NULL,
[DateCategoryId] [smallint] NOT NULL,
[MilestoneDate] [date] NULL,
[ConfirmationStatus] [varchar](4) NOT NULL,
[MilestoneTimeStart] [time](0) NULL,
[MilestoneTimeEnd] [time](0) NULL,
[EntryUserId] [nvarchar](128) NULL,
[EntryDateTime] [datetime] NULL,
[ModifiedUserId] [nvarchar](128) NULL,
[ModifiedDateTime] [datetime] NULL,
[Id] [int] IDENTITY(1000,1) NOT NULL,
CONSTRAINT [PK_ShowDates] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ShowDates] ADD CONSTRAINT [DF_ShowDates_Status] DEFAULT (N'TEN') FOR [ConfirmationStatus]
GO
CREATE FUNCTION [dbo].[getShowDate]
(
@ShowNumber varchar(8),
@DateCategoryId smallint,
@Mode char(3)
)
RETURNS Date
AS
BEGIN
DECLARE @ShowDate Date
IF @Mode = 'Max'
BEGIN
SELECT @ShowDate = Max(MilestoneDate) FROM ShowDates WHERE ShowNumber = @ShowNumber AND DateCategoryId = @DateCategoryId
END
IF @Mode = 'Min'
BEGIN
SELECT @ShowDate = Min(MilestoneDate) FROM ShowDates WHERE ShowNumber = @ShowNumber AND DateCategoryId = @DateCategoryId
END
RETURN @ShowDate
END
"RETURNS Date" at the start of the function should tell you that you can't switch the datatype of the return value. In any case, if you could, then every call to the function would require code to gracefully detect the returned datatype. Not so easy.
Why not return a date which has a special meaning, for instance the lower bound of the DATE datatype in 2k8: CAST('0001-01-01 ' AS DATE)?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 20, 2011 at 1:15 pm
So check to see if the returned value is null and if it is return the lower boundary date?
April 20, 2011 at 1:41 pm
I doubt this is is a good idea, but you can return the value in a sql_variant.
create function dbo.F_TEST ( @MyValue int )
returns sql_variant
as
begin
declare @MyVariant sql_variant
if @MyValue is null set @MyVariant = convert(datetime,'17530101')
if @MyValue > 0 set @MyVariant = convert(int,0)
if @MyValue <= 0 set @MyVariant = convert(int,-1)
return @MyVariant
end
go
select dbo.F_TEST( null ) as [Param = Null]
select dbo.F_TEST( -1 ) as [Param = -1]
select dbo.F_TEST( 1 ) as [Param = 1]
go
drop function dbo.F_TESTResults:
Param = Null
-----------------------
1753-01-01 00:00:00.000
(1 row(s) affected)
Param = -1
-----------------------
-1
(1 row(s) affected)
Param = 1
-----------------------
0
April 20, 2011 at 3:12 pm
david.holley (4/20/2011)
So check to see if the returned value is null and if it is return the lower boundary date?
Exactly. Your app will have to check the value is or isn't equal to the lower bound date and respond appropriately.
@michael-2 - I can't believe you posted that!
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 20, 2011 at 3:22 pm
ChrisM@home (4/20/2011)
...
@michael-2 - I can't believe you posted that!
I'm just hoping to see someone use it in production code. :satisfied:
Actually, I started wondering if a function that returned a sql_variant it was even possible, so I did that as a test. Seems to work in 2005 and 2008.
April 21, 2011 at 12:01 am
Michael Valentine Jones (4/20/2011)
ChrisM@home (4/20/2011)
...
@michael-2 - I can't believe you posted that!
I'm just hoping to see someone use it in production code. :satisfied:
Actually, I started wondering if a function that returned a sql_variant it was even possible, so I did that as a test. Seems to work in 2005 and 2008.
It's surprising how much you can learn whilst seeking a solution. Not all of it good 😉
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply