Date Serial In Access

The following example uses numeric expressions instead of absolute date numbers. Here the DateSerial function returns a date that is the day before the first day (1 - 1), two months before August (8 - 2), 10 years before 1990 (1990 - 10); in other words, May 31, 1980. DateSerial(1990 - 10, 8 - 2, 1 - 1). Displaying specific dates To display specific dates, you can use the DateSerial function to manipulate the day, month, and year portions of a date. For example, you can use the following expressions in the ControlSource property of a text box or in the Immediate window to return specific dates.

-->

Returns a Variant (Date) for a specified year, month, and day.

Syntax

DateSerial(year, month, day)

The DateSerial function syntax has these named arguments:

Part	Description
year	Required; Integer. Number between 100 and 9999, inclusive, or a numeric expression.
month	Required; Integer. Any numeric expression.
day	Required; Integer. Any numeric expression.

Remarks

Brett aqualine parts. Driver parallel lines pc. To specify a date, such as December 31, 1991, the range of numbers for each DateSerialargument should be in the accepted range for the unit; that is, 1–31 for days and 1–12 for months. However, you can also specify relative dates for each argument by using any numeric expression that represents some number of days, months, or years before or after a certain date.

The following example uses numeric expressions instead of absolute date numbers. Here the DateSerial function returns a date that is the day before the first day (1 - 1), two months before August (8 - 2), 10 years before 1990 (1990 - 10); in other words, May 31, 1980.

Under Windows 98 or Windows 2000, two digit years for the year argument are interpreted based on user-defined machine settings. The default settings are that values between 0 and 29, inclusive, are interpreted as the years 2000–2029. The default values between 30 and 99 are interpreted as the years 1930–1999. For all other year arguments, use a four-digit year (for example, 1800).

Earlier versions of Windows interpret two-digit years based on the defaults described earlier. To be sure the function returns the proper value, use a four-digit year.

When any argument exceeds the accepted range for that argument, it increments to the next larger unit as appropriate. For example, if you specify 35 days, it is evaluated as one month and some number of days, depending on where in the year it is applied. If any single argument is outside the range -32,768 to 32,767, an error occurs. If the date specified by the three arguments falls outside the acceptable range of dates, an error occurs.

Note

Dateserial In Access Query

For year, month, and day, if the Calendar property setting is Gregorian, the supplied value is assumed to be Gregorian. If the Calendar property setting is Hijri, the supplied value is assumed to be Hijri.

The returned date part is in the time period units of the current Visual Basic calendar. For example, if the current calendar is Hijri and the date part to be returned is the year, the year value is a Hijri year. For the argument year, values between 0 and 99, inclusive, are interpreted as the years 1400–1499. For all other year values, use the complete four-digit year (for example, 1520).

Blackberry passport software download. Free BlackBerry Passport programs download.jar,.sis. Announced during the company’s magazine salary call, the BlackBerry Credentials – which is person tipped as the perfect Instagram phone gratitude to its tetragon display – will as well allegedly advantage from a quad core Snapdragon MSM8974 computer, Adreno 330 graphics and 3GB of Pack.

Example

This example uses the DateSerial function to return the date for the specified year, month, and day.

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.
DateSerial() 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. If you want more information on how the DateSerial() function worked within Access, see reference below.
Others may exist, but let's explore my version..
The Methodology
The main principle is to utilize simple DateAdd() function. The DateAdd() function takes a datepart like 'day', 'month', or 'year', etc. 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.
Basically, once a date is established by parsing the year sent to our DateSerial() function, we can simply add months and days using DateAdd().
This is very straight-forward and explained in code, so..
The Code
Adds some error handling for invalid start years has been added that can be adjusted per your own tastes.
Here is the usage:
Example usage above returns '2009-04-01 00:00:00.000' and '2008-11-01 00:00:00.000', respectively.
The first example, dbo.DateSerial(2009, 4, 1), simply puts in a year, month, and day. However, the second is a bit more involved.
For the second example, we start with the year 2009. Now for month, we have a -1, but what are we starting with? Well, since January is month 1, then a month of 0 would be one month prior to January; therefore, a year of 2009 and month of 0 would start us at December 2008. Now subtracting one month, we arrive at November 2008. The last parameter sets the day to be the 1st; hence our result.
Clear as mud, right!

Dateserial Examples

Well, here is a look at a scenario where this may be useful:
We have a labor entry system that stores a year and month of entry that we cannot change, but we need to utilize the two values as a date to make our where clause more easily able to determine entries for the last three months. This is possible, otherwise, but can sometimes be ugly in syntax.
The below snippet is one solution I have used..
Now here is the same code with DateSerial..
Notice we were able to use standard date function as our DateSerial() tool returns a valid datetime value.
To explore this further, see this alternative allowing us to compare dates:
Very useful.

As a good alternative, my method without DateSerial() shown earlier is very clean as well for this particular case and was developed after creating this function, but as with anything else there is always a more complex need where this can come in handy like:
This will return the date 35 days prior to the first day of the current month in the current year!
Thanks for reading, and I hope you find my ramblings useful. Always good to have a lot of tools in the arsenal, so just giving you another with our new found DateSerial() function.
Happy coding!

Dateserial In Access Email

Best regards,
Kevin (aka MWVisa1)
References / Related Articles:

Dateserial In Access Form

DateSerial in Access > http://office.microsoft.com/en-us/access/HA012288131033.aspx
TimeSerial in MS SQL > http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_669-TimeSerial-Function-for-Microsoft-SQL-Server.html

Publiomg3