Recreating the DateSerial()/Date() function

  • Thread starter Thread starter Conan Kelly
  • Start date Start date
C

Conan Kelly

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
 
On Sat, 01 Mar 2008 04:16:28 GMT, "Conan Kelly"

T-SQL is a great set-based language, not very suitable to writing
procedural code like this. MUCH better to implement this in a .NET
assembly that can be hosted in SQL Server 2005 and up, and called from
T-SQL. Since DateSerial is implented in the .NET Framework, it should
be very easy to write this. Performance of these assemblies is very
good.

-Tom.
 
Conan said:
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,
Click to expand...

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
 
Tom van Stiphout,

Thanks for the feedback.

Have you ever seen Jeff Dunham and Peanut? Well Peanut just did his
"NNNNNNEEW!!!" to me.

I have no idea how to write/develop .NET assemblies or if I even have the
software on my computer to do this. Nor do I know how to host assemblies in
SQL Server.

I can stumble my way around T-SQL okay, I'm pretty good a VBA in XL, okay at
VBA in AC & WD, and I'm learning how to develop fairly simple SSIS packages
(with script tasks & transforms), but that is about the extend of developing
abilities.

I wouldn't mind learning, but doing something like this would take quite a
bit of hand-holding to accomplish this.

Thanks again for all of your help,

Conan
 
On Sat, 01 Mar 2008 05:39:57 GMT, "Conan Kelly"

Visual Studio Express Edition is a free download.
Books Online in SQL Server has a nice chapter about assemblies.
It's really not that hard, but being new to all of this it would be
nice to have a programming buddy around. Of course there are
newsgroups specializing in .NET programming.

-Tom.
 
Back
Top