JeffreyBarke.net

I was asked to sort a table of lease data by floor in descending order today—simple, right? But after updating the query with ORDER BY floor DESC, I noticed the results were wrong. The 9^th floor was always at the top and the 10^th floor and above were between the second and first floors.

It was immediately obvious—the floor field was not stored as numeric data, but as character data. This struck me as odd, so I investigated the DB structure and values. The floor field was definitely being stored as character data, but why? The reason: the client wanted to store certain floors as LL.

So given this structure, how could I quickly and easily sort the floors? The answer: MySQL's CAST function. By casting the floor field as an integer, the numeric floors would sort correctly. Even better, the character data LL would cast to 0, preserving a correct sort.

I updated the query with ORDER BY CAST(floor as UNSIGNED) DESC and obtained the desired results. Learn more about MySQL's cast functions and operators.

Google I/O '08 Keynote by Marissa Mayer

Marissa Mayer has been with Google for nine years, helping to build Google into one of the world's most popular web services. As the VP of Search and User Experience, her team is behind some of Google's most popular and successful products including core web search, images, news, books, maps, iGoogle, toolbar, desktop and health. This talk is a glimpse from inside the trenches of how Google builds products (including practical insights on how to build the best products), how to prioritize your efforts especially under resource constraints and how to think about strategy.

Source: YouTube

Working with Microsoft SQL Server for the first time in two years, so a few notes to self:

1. Escape single quotes with two single quotes, not with addslashes().
2. @@TEXTSIZE returns the current value of the TEXTSIZE option of the SET statement, which specifies the maximum length, in bytes, of text or image data that a SELECT statement returns.

An example of using TEXTSIZE:

SELECT @@TEXTSIZE
SET TEXTSIZE 2048
SELECT @@TEXTSIZE

Which results in:

------------------------
64512

------------------------
2048

From Journey to SQL Authority with Pinal Dive and MSDN.

Based on Matt Faus' post, I know I'm not the first to struggle with this simple syntactical problem: how to get the current date and time via a function in Microsoft's SQL Server?

In MySQL, we use NOW(), but it didn't work here. However, GETDATE() does!

To return the current system date and time in SQL Server:

SELECT GETDATE();

Read the full documentation at MSDN.

Solution via The Matt Faus Blog

Nor is it pronounced "sequel." SQL is just "ess-que-elle." Why? Because ANSI says so in document x3.135-1992, Database Language—SQL.