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

Reading and writing dates and times

Description
This set of examples shows how to work with date and time data types when accessing data in spreadsheets and databases. The basic model version uses an ODBC connection to databases through the odbc driver, model version (2) uses ODBC through SQL statements and version (4) shows the same for Oracle databases accessed via the OCI. Model versions (3) and (5) work with different spreadsheet drivers, and version (6) with CSV data format.

Further explanation of this example: Xpress Whitepaper 'Using ODBC and other database interfaces with Mosel', Section Examples - Handling dates and time.


Source Files

Data Files





datesinout.mos

(!******************************************************
   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. Aug. 2023
*******************************************************!)

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

 parameters
                                 ! 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"
 end-parameters

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

! 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"
 end-initializations

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

! 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`"      ! 'DateTime2' is a reserved word for MS Access
!  DT as "DateTime2"       ! Standard form for other databases
 end-initializations

 writeln(D, ", ", T)
 writeln(DT)

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

 writeln(D, ", ", T)
 writeln(DT)

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

end-model

Back to examples browserPrevious exampleNext example