Recreating the DateSerial()/Date() function
 

Hello all,

x-posted on:
microsoft.public.access
microsoft.public.excel.programming
microsoft.public.excel.worksheet.functions

Does anyone know the logic behind the DateSerial() function in AC/VBA
(Date() in XL). I want to recreate this function in SQL Server, but have no
idea about how to tackle the logic.

Right now, I have a basic version that will only accept 1-12 for the month
argument and 1-31 for the day argument (I hope no one ever asks for the 31st
of February!!! KABOOM!!!)

I'm fairly certain I can figure out the logic for the month argument so it
will accept negative numbers or 12 and adjust the year accordingly, but I
have no idea how to handle the Day argument to do the same thing. Also, I
don't know which argument needs to get calculated first.

Thanks for any help anyone can provide,

Conan Kelly

Recreating the DateSerial()/Date() function
 

Conan Kelly wrote:
Hello all,

x-posted on:
microsoft.public.access
microsoft.public.excel.programming
microsoft.public.excel.worksheet.functions

Does anyone know the logic behind the DateSerial() function in AC/VBA
(Date() in XL). I want to recreate this function in SQL Server, but
have no idea about how to tackle the logic.

Right now, I have a basic version that will only accept 1-12 for the
month argument and 1-31 for the day argument (I hope no one ever asks
for the 31st of February!!! KABOOM!!!)

I'm fairly certain I can figure out the logic for the month argument
so it will accept negative numbers or 12 and adjust the year
accordingly, but I have no idea how to handle the Day argument to do
the same thing. Also, I don't know which argument needs to get
calculated first.
Thanks for any help anyone can provide,

CREATE FUNCTION DateSerial (
@Year int,
@Month int,
@Day int
)

RETURNS DateTime AS

BEGIN
DECLARE @ResultVar DateTime;

IF @Year < 1754 OR @Year 9999
SET @ResultVar = Null;
ELSE
BEGIN
SET @ResultVar = CONVERT(DateTime, '1899-12-1');
SET @ResultVar = DateAdd(year, @Year-1900, @ResultVar);
SET @ResultVar = DateAdd(month, @Month, @ResultVar);
SET @ResultVar = DateAdd(day, @Day-1, @ResultVar);
END

RETURN @ResultVar;
END


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com