| |||||||||||||||||||||||||||
| |||||||||||||||||||||||||||
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 By clicking on a file name, a preview is opened at the bottom of this page.
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
| |||||||||||||||||||||||||||
| © Copyright 2024 Fair Isaac Corporation. |
