MS SQL calculating the number of days in a the month of a given date

I was looking for MS Access SQL to calculate the numbers of days in a certain month, specified by a date record. I did not find the solution but the neccessary hint in the right direction on Eggheadcafe (Hattip: John W. Vinson [MVP]).

SQL functions needed are:

• DateDiff( "d" ; date1 ; date2)
  calculates the difference in days between data1 and date2, and
• DateSerial( year ; month ; day )
  creates a datevariable representing the date given by a number for the year, the month, and the day.
• Year( date )
  gets the number of the year out of a date variable. Year(#01.01.2010#) produces the number 2010. The functions Month() and Day() work the same way and are needed.

To get the number of days in the month given by the date “startTime” the following code worked for me with MS Access 2003:

DateDiff("d";DateSerial(Year([startTime]);Month([startTime]);1);DateSerial(Year([startTime]);Month([startTime])+1;1))

Advertisement

Convert Character to Numeric in MS Access

Several times I had to correct database tables with a character primary key (which is ok, if not all other tables had numeric …). In order to get the necessary numeric format I inserted another variable with the correct format and transformed the character variable to numeric using the CDbl() Function.

The syntax for the CDbl function is:
CDbl([MyString])
I did forget it again and could not find any documentation. Googling the topic gave a lot of fruitless hits, so here it is.