Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.access,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.access,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
updating Outlook contact excel spreadsheets without recreating? | Excel Discussion (Misc queries) | |||
update linked spreadsheets w/o recreating links | Excel Worksheet Functions | |||
RECREATING THE PROBLEM !!!!!!! | Excel Discussion (Misc queries) | |||
Time/DateSerial Numeric Value | Excel Worksheet Functions | |||
How do I change my DSN without recreating the odbc connection? | Excel Worksheet Functions |