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: - fn
- oj
- 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).
'Mike Gunderloy' on Tue, 21 Aug 2007 00:38:26 GMT, sez: Or you could Google
"sql server" date "ts"
I know, not nearly as much fun as writing PowerShell scripts...
'Matthew' on Tue, 21 Aug 2007 02:28:17 GMT, sez: AND "Customer"."OrderDate"<{ts '2007-08-17 00:00:01'})
is probably even better as:
AND "Customer"."OrderDate"< = '20070817'
'Haacked' on Tue, 21 Aug 2007 04:42:51 GMT, sez: so is this post about your lack of Google Fu?
;)
'lb' on Tue, 21 Aug 2007 04:44:57 GMT, sez: >so is this post about your
>lack of Google Fu?
sure looks that way!
i guess i didn't really google too hard.
i was more excited by the idea that there might be this whole bunch of terse little secret commands in SQL that could perform all kinds of complex things.
'Mike Woodhouse' on Tue, 21 Aug 2007 06:07:15 GMT, sez: Crystal. Bah. I stopped caring when their "wonderful" platform-agnostic SQL generator produced an Oracle outer-join operator for a SQL Server connection. About 1996 it was - geek grudges are forever.
'John Rusk' on Tue, 21 Aug 2007 06:40:48 GMT, sez: I believe it's called the "ODBC Cannonical Datetime Format".
'Steve L' on Tue, 21 Aug 2007 06:54:39 GMT, sez: Story ended too prematurely...
Should have also mentioned what fn and oj was... Which I presume are
fn = function
oj = outter join.
'Simon' on Tue, 21 Aug 2007 07:04:01 GMT, sez: Google schmoogle. It's much more fun to knock up a powershell script just to see what happens. Your enthusiasm and curiosity are a wonder to behold. Secret, no. Geek, yes! Keep it up.
'ReallyVirtual' on Tue, 21 Aug 2007 08:59:00 GMT, sez: Next step... the search for hidden 3 character keywords?
'Eric D. Burdo' on Tue, 21 Aug 2007 10:17:13 GMT, sez: Wait until he hits the hidden 26 character keywords... that's going to take a bit.
:P
'Jon Schneider' on Wed, 22 Aug 2007 15:18:46 GMT, sez: I'm with Simon. I got a much bigger kick out of the cool way that Leon ended up arriving at the answer in this case than from actually learning the answer itself! :-)
'Suraj Barkale' on Wed, 22 Aug 2007 23:24:43 GMT, sez: When you have a scripting language, every problem screams to be scripted :)
'paul stovell' on Thu, 30 Aug 2007 08:38:28 GMT, sez: The mean-spirited side of me hoped you might stumble accross the hidden "format C-drive" operator in your quest. The nice side of me is glad you didn't :)
'RV' on Fri, 09 Nov 2007 11:01:12 GMT, sez: LOL! "so is this post about your lack of Google Fu?"
'andreas birgerson' on Wed, 14 Jul 2010 13:16:52 GMT, sez: This is actually documented in
http://msdn.microsoft.com/en-us/library/ms378045(SQL.100).aspx
|
secretGeek .:dot Nuts about dot Net:.
JSON Query Languages: 5 special purpose editors
.: secretGeek :: Complete Archives
The Best Software Writing I
The Business Of Software (Eric Sink)
bookmark at Del.icio.us!
reddit this