SET = dateadd(mm, - 1, - Add to date the proper days subtracting 1, - since we used 1 as start instead of zero. This is very straight-forward and explained in code, so.Īdds some error handling for invalid start years has been added that can be adjusted per your own tastes.ĬREATE FUNCTION dbo.DateSerial ( int, int, bigint ) RETURNS datetime AS BEGIN DECLARE datetime - catch invalid year entries and default appropriately SET = CASE WHEN 9999 THEN year(getdate()) ELSE END - convert date by adding together like yyyymmdd SET = * 10000 + 101 AS char(8)) - Add to date the proper months subtracting 1, - since we used 1 as start instead of zero. without the quotes (unlike MS Access) a signed integer indicating the number or days to add/subtract the datetime to add/subtract number of datepart units from.īasically, once a date is established by parsing the year sent to our DateSerial() function, we can simply add months and days using DateAdd(). The DateAdd() function takes a datepart like "day", "month", or "year", etc.
The main principle is to utilize simple DateAdd() function.
Others may exist, but let's explore my version. If you want more information on how the DateSerial() function worked within Access, see reference below. If you are a fan of DateSerial() function in Microsoft Access or like me you deal with data where you only have year and month but would like to do date calculation/manipulation, then here is a replication of this functionality for Microsoft SQL Server utilizing a user defined function.ĭateSerial() takes in a number of years, months, and days and returns the appropriate date allowing you vary inputs and use negatives without having to worry about crossing over months or years.
0 kommentar(er)
