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:

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

Or you could Google

"sql server" date "ts"

I know, not nearly as much fun as writing PowerShell scripts...

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

is probably even better as:

AND "Customer"."OrderDate"< = '20070817'

>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.

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.

Story ended too prematurely...

Should have also mentioned what fn and oj was... Which I presume are
fn = function
oj = outter join.

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.

Wait until he hits the hidden 26 character keywords... that's going to take a bit.

:P

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! :-)

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 :)

This is actually documented in
http://msdn.microsoft.com/en-us/library/ms378045(SQL.100).aspx

A weird casestudy in technical research

NimbleText is a Powerful FREE Tool

Articles

Twitter, baby

Recommended Reading

Recommended blogs

Aggregated Links

Human Link Machines