sql

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.

Open Access .mdb Files with RODBC

Getting data into R can be done by reading colon separated files (.csv) via the read.table() function. It is also possible to access databases directly and send SQL queries directly from R to the database. This has some advantages: Using Sweave the queries get documented in the analysis report, variable formats are retained.

To install the RODBC package:
install.packages("RODBC")

Open a database connection to an Microsoft Access database file, e.g. “MyDataBase.mdb” sitting in the Folder “C:\ MyPath\MyDataBase.mdb”:
channel <- odbcConnectAccess("C:/MyPath/MyDataBase")
note that the Windows backslashes “\” become slashes “/” in R and the extension “.mdb” is omitted.

Getting the database table “MyTable” into the R dataframe “R.Table”
R.Table <- sqlQuery( channel , paste ("select * from MyTable"))
MyTable can also be a sql query in the Access database.

Command line instructions to create a database in MySQL

Still working on a way to port a sloppy Access database to MySQL. In the end it would be lovely to have a shell script doing all steps in a row:

  1. Extract the tables of the database as .csv
  2. Create a new (empty) MySQL database
  3. Create all tables in the new MySQL db
  4. Import the .csvs into those tables

One of the necessary steps is to create a new MySQL database:

$ mysql -u ''adminusername'' -p
mysql> CREATE DATABASE ''databasename'';
mysql> GRANT ALL PRIVILEGES ON ''databasename''.* TO "''username''"@"''hostname''" IDENTIFIED BY "''password''";
mysql> FLUSH PRIVILEGES;
mysql> EXIT

I just collected the code, did not try it out. Just not to forget….