FICO Xpress Optimization Examples Repository
FICO Optimization Community FICO Xpress Optimization Home
Back to examples browserPrevious exampleNext example

Reading and writing external types: dates and time

The types 'date', 'time', and 'datetime' are defined by the Mosel module mmsystem. This module also defines several subroutines for handling these types (see model file dates.mos) and Mosel's text data file format accepts these types. Special functionality for working with the date and time types is also implemented by the ODBC, Excel, and Oracle (OCI) interfaces - their use is shown in this set of examples.
  • Spreadsheets and databases can be accessed from Mosel through an ODBC connection using the odbc driver (datesinout.mos) or with SQL statements (datesinout2.mos).
  • With Microsoft Excel spreadsheets we recommend to use the software-specific driver excel (datesinout3.mos).
  • For Oracle databases a software-specific connection is provided by the module mmoci (datesinout4.mos).
  • Excel spreadsheets can also be accessed from Mosel using a dedicated IO driver xsl from the mmsheet module. The driver supports manipulating excel files on various platforms without having to install Microsoft Excel (datesinout5.mos).
  • Module mmsheet also provides an IO driver for csv files (datesinout6.mos).
Further explanation of this example: Xpress Whitepaper 'Using ODBC and other database interfaces with Mosel', Section Examples - Handling Dates and Time.[download all files]

Source Files

Data Files


   Mosel Example Problems

   file datesinout.mos
   Reading/writing dates from/to
   spreadsheets or databases via ODBC.
   - Using 'initializations from' with odbc IO driver -
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, Nov. 2007, rev. Mar. 2014

model "Dates and times (ODBC)"
 uses "mmsystem", "mmodbc"

                                 ! Use Excel spreadsheet `datetime.xls'
 ! CNCTIO = "mmodbc.odbc:datetime.xls"
                                 ! Use Access database `datetime.mdb'
  CNCTIO = "mmodbc.odbc:debug;datetime.mdb"
                             ! Use mysql database `datetime' (not provided)
 ! CNCTIO = "mmodbc.odbc:debug;DSN=mysql;DB=datetime"
                              ! Use Oracle database `datetime' (not provided)
 ! CNCTIO = "mmoci.oci:debug;myname/mypassword@datetime"
                              ! Use SQLite database 'datetime.sqlite' via ODBC
 ! CNCTIO = "mmodbc.odbc:debug;DSN=sqlite;DATABASE=datetime.sqlite"
                              ! Use SQLite database 'datetime.sqlite' directly
 ! CNCTIO = "mmodbc.odbc:debug;DRIVER=mmsqlite;DB=datetime.sqlite;READONLY=false"
 ! CNCTIO = "mmodbc.odbc:debug;datetime.sqlite"

  T: time
  D: date
  DT: datetime
  Dates: array(1..5) of date
  DList: list of date

! Read in dates / time (make sure to select the format used by the
! spreadsheet/database)
! Access and Excel:
 setparam("timefmt", "%y-%0m-%0d %0H:%0M:%0S")
 setparam("datefmt", "%y-%0m-%0d")
 setparam("datetimefmt", "%y-%0m-%0d %0H:%0M:%0S")
(! mysql:
 setparam("timefmt", "%0H:%0M:%0S")
 setparam("datefmt", "%y-%0m-%0d")
 setparam("datetimefmt", "%y-%0m-%0d %0H:%0M:%0S")
(! Oracle:
 setparam("timefmt", "%0d-%N-%0Y %0h.%0M.%0S.%0s %P")
 setparam("datefmt", "%0d-%N-%0Y")
 setparam("datetimefmt", "%0d-%N-%0Y %0h.%0M.%0S.%0s %P")
(! SQLite:
 setparam("timefmt", "%0H:%0M:%0S")
 setparam("datefmt", "%y-%0m-%0d")
 setparam("datetimefmt", "%y-%0m-%0d %0H:%0M:%0S")

 initializations from CNCTIO
  T as "Time1"
  D as "Date1"
  DT as "DateTime1"
  Dates as "noindex;Dates"
  DList as "Dates"

 setparam("timefmt", "%h:%0M %p")
 writeln(D, ", ", T)

! Read date / time from strings
 setparam("timefmt", "%Hh%0Mm")
 setparam("datefmt", "%dm%0my%0y")
 setparam("datetimefmt", "%dm%0my%0y, %Hh%0Mm")

 initializations from CNCTIO
  T as "Time2"
  D as "Date2"
  DT as "DateTime2"

 writeln(D, ", ", T)

! Use Mosel's default format
 setparam("timefmt", "")
 setparam("datefmt", "")
 setparam("datetimefmt", "")

 writeln(D, ", ", T)

! The following assumes that the database output fields have type string
 initializations to CNCTIO
  T as "TimeOut"
  D as "DateOut"
  DT as "DateTimeOut"


Back to examples browserPrevious exampleNext example