HOWTO: SAP Sybase IQ / SQL Anywhere : searching date, datetime, time fields

If you’re new to IQ (or SQL Anywhere) you may run into an oddity that if you connect with ISQL the following code works but doesn’t work in DBISQL:

SELECT col1_date
FROM mytable
WHERE col1_date >= '06/01/2014'

This is because any connection to IQ using the native connection (SQL Anywhere connection) that is used by dbisql and the like expects string to datetime format of “YYYY-MM-DD HH:NN:SS.SSS”. This is not changeable by setting an option. The string needs to be converted using a style (datetime string style # 103) prior to being used as a SARG.

SELECT col1_date
FROM mytable
WHERE col1_date >= convert(datetime, '06/01/2014', 103)

Even those of us that have been around for eons tend to forget ‘little’ gotchas like this.

From :

Table 2-3: Date format conversions using the style parameter
Without century (yy) With century (yyyy) Standard Output
Key “mon” indicates a month spelled out, “mm” the month number or minutes. “HH ”indicates a 24-hour clock value, “hh” a 12-hour clock value. The last row, 23, includes a literal “T” to separate the date and time portions of the format.
0 or 100 Default mon dd yyyy hh:mm AM (or PM)
1 101 USA mm/dd/yy
2 2 SQL standard
3 103 English/French dd/mm/yy
4 104 German
5 105 dd-mm-yy
6 106 dd mon yy
7 107 mon dd, yy
8 108 HH:mm:ss
9 or 109 Default + milliseconds mon dd yyyy hh:mm:ss AM (or PM)
10 110 USA mm-dd-yy
11 111 Japan yy/mm/dd
12 112 ISO yymmdd
13 113 yy/dd/mm
14 114 mm/yy/dd
14 114 hh:mi:ss:mmmAM(or PM)
15 115 dd/yy/mm
16 or 116 mon dd yyyy HH:mm:ss
17 117 hh:mmAM
18 118 HH:mm
19 hh:mm:ss:zzzAM
20 hh:mm:ss:zzz
21 yy/mm/dd HH:mm:ss
22 yy/mm/dd HH:mm AM (or PM)
23 yyyy-mm-ddTHH:mm:ss

The default values (style 0 or 100), and style 9 or 109 return the century (yyyy). When converting to char or varchar from smalldatetime, styles that include seconds or milliseconds show zeros in those positions.

Share Button

Astronomy, IVOA , Simple Application Messaging Protocol (SAMP) and Perl

When Alasdair Allan, Thomas Boch, Mike Fitzpatrick, Luigi Paioro, John Taylor, International Virtual Observatory AllianceMark Taylor and Doug Tody of the International Virtual Observatory Alliance (IVOA) wrote up the draft specification to the Simple Application Messaging Protocol, Alasdair Allan deemed it possible and necessary to implement the draft specification.

SAMP is a messaging protocol that enables astronomy software tools to interoperate and communicate. IVOA members have recognised that building a monolithic tool that attempts to fulfil all the requirements of all users is impractical, and it is a better use of our limited resources to enable individual tools to work together better. One element of this is defining common file formats for the exchange of data between different applications. Another important component is a messaging system that enables the applications to share data and take advantage of each other’s functionality. SAMP is intended to build on the success of a prior messaging protocol, PLASTIC, which has been in use since 2006 in over a dozen astronomy applications and has proven popular with users and developers. SAMP is an IVOA-endorsed standard that builds on this success. It is also intended to form a framework for more general messaging requirements. – excerpt

Alasdair chose to work with Perl for his implementation of the draft specification. What I Alasdair Allanfind interesting, is that while it is designed for astronomy tools, it is generic enough for it to be implemented outside of that scope.  (pun intended)  I can easily see it being used as in a data federation type application.  The protocol is XML-RPC based and is easily understood.

Great work! 🙂

Oh, you want to know what he has called his Perl implementation?  It’ll cost ya!  Just joking..  it is called Perl::Samp and consists of:

  • SAMP::Transport::HTTP::Daemon
  • SAMP::Discovery
  • SAMP::Hub
  • SAMP::Hub::Util

Perl::Samp requires you to install the following modules from CPAN:

At the time of this blog post, it is not on CPAN but Alasdair is willing to put it there.  Let him know if you wish it to be on CPAN.

Share Button