Initializing help system before first use

Reading and writing external types: dates and time
Type: Programming
Rating: 3 (intermediate)
Description: 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).
File(s): datesinout.mos, datesinout2.mos, datesinout3.mos, datesinout4.mos, datesinout5.mos, datesinout6.mos
Data file(s): datetime.csv, datetime.mdb, datetime.xls, datetime.xlsx, datetime.sqlite

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. Mar. 2014
*******************************************************!)

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"
 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
datesinout2.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file datesinout2.mos
   ````````````````````
   Reading/writing dates from/to
   spreadsheets or databases via ODBC.
   - Using SQL commands -
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, Nov. 2007, rev. Sep. 2018
*******************************************************!)

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

 parameters
                              ! Use Excel spreadsheet `datetime.xls'
 ! CNCT = 'DSN=Excel Files;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselData/datetime.xls'
 ! CNCT = 'datetime.xls'
                              ! Use Access database `datetime.mdb'
 ! CNCT = 'DSN=MS Access Database;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselData/datetime.mdb'
  CNCT = 'datetime.mdb'
                              ! Use mysql database `datetime' (not provided)
 ! CNCT = 'DSN=mysql;DB=datetime'
                              ! Use database 'datetime.sqlite' via ODBC 
 ! CNCT = 'DSN=sqlite;DATABASE=datetime.sqlite'
                              ! Use SQLite database 'datetime.sqlite' directly 
 ! CNCT = 'datetime.sqlite'
 end-parameters

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

 setparam("SQLverbose",true)
 SQLconnect(CNCT)

! 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")
!)
(! SQLite:
 setparam("timefmt", "%0H:%0M:%0S")
 setparam("datefmt", "%y-%0m-%0d")
 setparam("datetimefmt", "%y-%0m-%0d %0H:%0M:%0S")
!)

 T:=time(SQLreadstring("select * from Time1"))
 D:=date(SQLreadstring("select * from Date1"))
 DT:=datetime(SQLreadstring("select * from DateTime1"))

 SQLexecute("select * from Dates", Dates)
 SQLexecute("select * from Dates", DList)
 
 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")

 T:=time(SQLreadstring("select * from Time2"))
 D:=date(SQLreadstring("select * from Date2"))
 DT:=datetime(SQLreadstring("select * from DateTime2"))

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

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

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

 SQLexecute("delete from TimeOut")      ! Cleaning up previous results: works
 SQLexecute("delete from DateOut")      ! only for databases, cannot be used
 SQLexecute("delete from DateTimeOut")  ! with spreadsheets (instead, delete
                                        ! previous solutions directly in the
                                        ! spreadsheet file)
 SQLexecute("insert into TimeOut values (?)", [T])
 SQLexecute("insert into DateOut values (?)", [D])
 SQLexecute("insert into DateTimeOut values (?)", [DT])

 SQLdisconnect

end-model
datesinout3.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file datesinout3.mos
   ````````````````````
   Reading/writing dates from/to
   spreadsheets or databases via ODBC.
   - Using 'initializations from' with the excel driver -

!!! If running this model produces the message
!!!   'Unexpected date/time format: ...' 
!!! then the date format used by your spreadsheet is not among the 
!!! preconfigured format settings tested by this model.  
!!! You need to adapt the date and time formats to the format used 
!!! internally by the spreadsheet, according to the date shown with 
!!! the error message output by Mosel. 
!!! (NB: these formats may be different from the spreadsheet display format)
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, Nov. 2007, rev. Sep. 2014
*******************************************************!)

model "Dates and times (Excel)"
 uses "mmsystem", "mmsheet"

 parameters
  CSTR = 'mmsheet.excel:datetime.xls'
 end-parameters

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

! Read a date into a string as a test for the date format: 
! if the date format is recognized, we select the corresponding settings
 initializations from CSTR
   testdate as "skiph;Date1"
 end-initializations
 
 if testdate="2/20/2002" then          ! m/d/y
  setparam("timefmt", "%h:%0M:%0S %P")
  setparam("datefmt", "%m/%d/%y")
  setparam("datetimefmt", "%m/%d/%y %h:%0M:%0S %P")
 elif testdate="20/02/2002" then       ! dd/mm/yyyy
  setparam("timefmt", "%0H:%0M:%0S")
  setparam("datefmt", "%0d/%0m/%y")
  setparam("datetimefmt", "%0d/%0m/%y %0H:%0M:%0S")
 elif testdate="20.02.2002" then       ! dd.mm.yyyy
  setparam("timefmt", "%0H:%0M:%0S")
  setparam("datefmt", "%0d.%0m.%y")
  setparam("datetimefmt", "%0d.%0m.%y %0H:%0M:%0S")
 else
  writeln("Unexpected date/time format: ", testdate)
  exit(0)
 end-if

! Read in dates / time
 initializations from CSTR
  r as "skiph;Time1"                ! Time is stored as a real
  D as "skiph;Date1"
  DT as "skiph;DateTime1"
  Dates as "skiph;noindex;Dates"
  DList as "skiph;Dates"
 end-initializations

 T:=time(round(r*24*3600*1000))
 writeln(D, ", ", T)
 writeln(DT)
 writeln(Dates)
 writeln(DList)

! Read date / time from strings (spreadsheet cells are formatted as text)
 setparam("timefmt", "%Hh%0Mm")
 setparam("datefmt", "%dm%0my%0y")
 setparam("datetimefmt", "%dm%0my%0y, %Hh%0Mm")

 initializations from CSTR
  T as "skiph;Time2"
  D as "skiph;Date2"
  DT as "skiph;DateTime2"
 end-initializations

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

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

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

 initializations to CSTR
  T as "TimeOutE"
  D as "DateOutE"
  DT as "DateTimeOutE"
 end-initializations

end-model
datesinout4.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file datesinout4.mos
   ````````````````````
   Reading/writing dates from/to Oracle databases.
   - Using OCI -
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, Nov. 2007, rev. Mar. 2014
*******************************************************!)

model "Dates and times (OCI)"
 uses "mmsystem", "mmoci"

 parameters
  DB="myname/mypassword@dbname"     ! Login to Oracle database (not provided)
 end-parameters

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

 setparam("OCIdebug",true)
 OCIlogon(DB)
 writeln("Connection number: ",getparam("OCIconnection"))

! Read in dates / time (select the format used by the database)
 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")

 T:=time(OCIreadstring("select * from Time1"))
 D:=date(OCIreadstring("select * from Date1"))
 DT:=datetime(OCIreadstring("select * from DateTime1"))

 OCIexecute("select * from Dates", Dates)
 OCIexecute("select * from Dates", DList)
 
 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")

 T:=time(OCIreadstring("select * from Time2"))
 D:=date(OCIreadstring("select * from Date2"))
 DT:=datetime(OCIreadstring("select * from DateTime2"))

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

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

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

 OCIexecute("delete from TimeOut")      ! Cleaning up previous results
 OCIexecute("delete from DateOut")
 OCIexecute("delete from DateTimeOut")

! The following assumes that the output fields have types date/timestamp;
! if they are just strings (as with 'datesinout.mos') we do not need the
! conversion to Oracle format with TO_... :
 OCIexecute("insert into TimeOut values (TO_TIMESTAMP(:1,'HH24:MI:SS'))", [T])
 OCIexecute("insert into DateOut values (TO_DATE(:1,'YYYY-MM-DD'))", [D])
 OCIexecute("insert into DateTimeOut values (TO_TIMESTAMP(:1,'YYYY-MM-DD\"T\"HH24:MI:SS'))", [DT])

 OCIlogoff

end-model


**************************************************


! Creation of input and output tables in an Oracle database:


 declarations
  tsucc: array ({false,true}) of string
 end-declarations

 tsucc(false):="failed"; tsucc(true):="succeeded"

 OCIexecute("create table Date1 (DateValue date)")
 writeln(" - Create Date1 (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into Date1 (DateValue) values (:1)", ['20-Feb-2002'])
 writeln(" - Insert values in Date1 (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")

 OCIexecute("create table Time1 (DateValue timestamp(3))")
 writeln(" - Create Time1 (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into Time1 (DateValue) values (:1)", ['1-Jan-0 4:00:00pm'])
 writeln(" - Insert values in Time1 (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
 OCIexecute("delete from Time1")
 writeln(" - Delete from Time1 (",tsucc(getparam("OCIsuccess")),")")

 OCIexecute("create table DateTime1 (DateValue timestamp(3))")
 writeln(" - Create DateTime1 (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into DateTime1 (DateValue) values (:1)", ['20-Feb-2002 4:00:00pm'])
 writeln(" - Insert values in DateTime1 (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")

 OCIexecute("create table Dates (DateValue date)")
 writeln(" - Create Dates (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into Dates (DateValue) values (:1)", ['21-Jan-1999','22-Feb-2000','23-Mar-2002','24-Apr-2005','25-May-2010'])
 writeln(" - Insert values in Dates (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")

 OCIexecute("create table Date2 (DateValue varchar(10))")
 writeln(" - Create Date2 (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into Date2 (DateValue) values (:1)", ['20m02y2002'])
 writeln(" - Insert values in Date2 (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")

 OCIexecute("create table Time2 (DateValue varchar(12))")
 writeln(" - Create Time2 (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into Time2 (DateValue) values (:1)", ['16h00m'])
 writeln(" - Insert values in Time2 (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")

 OCIexecute("create table DateTime2 (DateValue varchar(25))")
 writeln(" - Create DateTime2 (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into DateTime2 (DateValue) values (:1)", ['20m02y2002, 16h00m'])
 writeln(" - Insert values in DateTime2 (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")

 OCIexecute("create table DateOut (DateValue date)")
 writeln(" - Create DateOut (",tsucc(getparam("OCIsuccess")),")")

 OCIexecute("create table TimeOut (DateValue timestamp(3))")
 writeln(" - Create TimeOut (",tsucc(getparam("OCIsuccess")),")")

 OCIexecute("create table DateTimeOut (DateValue timestamp(3))")
 writeln(" - Create DateTimeOut (",tsucc(getparam("OCIsuccess")),")")
datesinout5.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file datesinout5.mos
   ````````````````````
   Reading/writing dates from/to spreadsheets.
   - Using 'initializations from' with the xsl driver -
   
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012, rev. Sep. 2014
*******************************************************!)

model "Dates and times (spreadsheet)"
 uses "mmsystem", "mmsheet"

 parameters
  CSTR = 'mmsheet.xls:datetime.xls'
!  CSTR = 'mmsheet.xlsx:datetime.xlsx'
 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 use the default format of mmsystem,
! not the format used for display in the spreadsheet)
 setparam("timefmt", "%0H:%0M:%0S")
 setparam("datefmt", "%.y-%0m-%0d")
 setparam("datetimefmt", "%.y-%0m-%0dT%0H:%0M:%0S")

 initializations from CSTR
  T as "skiph;Time1"
  D as "skiph;Date1"
  DT as "skiph;DateTime1"
  Dates as "skiph;noindex;Dates"
  DList as "skiph;Dates"
 end-initializations

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

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

 initializations from CSTR
  T as "skiph;Time2"
  D as "skiph;Date2"
  DT as "skiph;DateTime2"
 end-initializations

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

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

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

 initializations to CSTR
  T as "TimeOutE"
  D as "DateOutE"
  DT as "DateTimeOutE"
 end-initializations

end-model
datesinout6.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file datesinout6.mos
   ````````````````````
   Reading/writing dates from/to spreadsheets.
   - Using 'initializations from' with the csv driver -
   
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012, rev. Mar. 2014
*******************************************************!)

model "Dates and times (spreadsheet)"
 uses "mmsystem", "mmsheet"

 parameters
  CSTR = 'mmsheet.csv:datetime.csv'
 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 (select the format used in the spreadsheet)
 setparam("timefmt", "%0h:%0M:%0S %P")
 setparam("datefmt", "%0m/%0d/%0Y")
 setparam("datetimefmt", "%0d/%0m/%0Y %0h:%0M %P")

 initializations from CSTR
  T as "[D5]"
  D as "[B5]"
  DT as "[F5]"
  Dates as "noindex;[B13:B17]"
  DList as "[B13:B17]"
 end-initializations

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

! Read date / time from strings (for CSV, this case is just the same as above)
 setparam("timefmt", "%Hh%0Mm")
 setparam("datefmt", "%dm%0my%0y")
 setparam("datetimefmt", "%dm%0my%0y, %Hh%0Mm")

 initializations from CSTR
  T as "[D9]"
  D as "[B9]"
  DT as "[F9]"
 end-initializations

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

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

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

 initializations to CSTR
  T as "[D21]"
  D as "[B21]"
  DT as "[F21]"
 end-initializations

end-model
Parent Topic Mosel language: Examples from the whitepaper 'Using ODBC and other database interfaces with Mosel'

© 2001-2019 Fair Isaac Corporation. All rights reserved. This documentation is the property of Fair Isaac Corporation (“FICO”). Receipt or possession of this documentation does not convey rights to disclose, reproduce, make derivative works, use, or allow others to use it except solely for internal evaluation purposes to determine whether to purchase a license to the software described in this documentation, or as otherwise set forth in a written software license agreement between you and FICO (or a FICO affiliate). Use of this documentation and the software described in it must conform strictly to the foregoing permitted uses, and no other use is permitted.