A weird casestudy in technical research

I used SQL profiler to grab the SQL that a crystal report generated.

Here's a snippet:

AND "Customer"."OrderDate"<{ts '2007-08-17 00:00:01'})

The curly brackets and the 'ts' looked strange to me. Not something I'd used before.

I wasn't sure what sort of thing 'ts' was. I googled it -- and found nothing. I checked SQL Server books on line, but no luck there either. Squiggly brackets and 'ts' are, by their nature, very hard to search for.

So I tried some variations to see what happened:

Select {ts '2007-08-17 00:00:01'} -- returns a date
Select {'2007-08-17 00:00:01'} -- fails (Syntax error or access violation)
Select ts '2007-08-17 00:00:01' --fails (Invalid column name 'ts')
Select ts('2007-08-17 00:00:01') --fails ('ts' is not a recognized function name)
select {ts '1'} -- fails (Syntax error converting datetime from character string)
Select {as '2007-08-17 00:00:01'} -- fails (Syntax error or access violation)


I figured it's some kind of special built in thing. And if this exists, there must be other special built in things I don't know about.

I wrote a short powershell program that generates all the combinations of two letters "aa, ab, ac..." right up to "zz". And then (using WSCG) I generated a monster T-SQL script of this form:

select 'aa'
go
Select {aa 5}
go
select 'ab'
go
Select {ab 5}
go
--(and so on up to 'zz')


Now I ran that script to see if any other two letter combinations stood out, and returned different error messages to the rest.

It turned out there were three such pairs of letters that produced a different error result to the rest:

1. fn
2. oj
3. ts

Now googling those three all together was enough to get me a result, and thus find the meaning and documentation on 'ts'!

'ts' -- incase you didn't guess -- is short for 'time stamp'. These squiggly bracket sequences come to us from the world of ODBC, and crystal presumably uses them because it is trying to be platform (and region) agnostic.

The following is from "Writing International Transact-SQL Statements":

ADO, OLE DB, and ODBC applications should use the ODBC timestamp, date, and time escape clauses of:
{ ts 'yyyy-mm-dd hh:mm:ss[.fff] '} such as: { ts '1998-09-24 10:02:20' }
{ d 'yyyy-mm-dd'} such as: { d '1998-09-24' }
{ t 'hh:mm:ss'} such as: { t '10:02:20'}


And this from google groups...:

ODBC provides a ODBC syntax for dates ( { d 'yyyy-mm-dd'} ), timestamps { ts '...'}), functions ( { fn RIGHT() } ... ) and even outer joins ( { oj .... }). So, if you write using ODBC syntax :

    SELECT * FROM MyTable WHERE MyField = { d '2000-12-31' } 

each ODBC driver translates it into native syntax of its underlying engine before sending the statement. You can see the documentation of ODBC syntax in the Appendixes of ODBC Programmer's Reference (MDAC documentation).

My book "Choose Your First Product" is available now.

It gives you 4 easy steps to find and validate a humble product idea.

Learn more.