Alex Le’s Blog

Well, today is my first day working with MSSQL. It’s been quite a learning curve. Truthfully speaking, working with MySQL is so much easier because MySQL does provide a more flexible way of handling different data formats, especially with datetime.

In MSSQL, if we want to extract the date part from a datetime column, here is the (crazy!) conversion:


SELECT (CAST(FLOOR(CAST(DateTimeColumn AS float)) AS myNewAlias) FROM someTable


On the other hand, in MySQL, the query is an elegant SELECT with the call of the appropriate formatting function.

Here comes another question, how do you actually GROUP the records by DATE? For example the dataset contains tons of UserName entries of different times but same date, here is the code that will group and count those entries


SELECT UserName convert(char(10),DateTimeColumn ,111) as groupDate, Count(*) as hits FROM someTable
GROUP BY UserName, convert(char(10),DateTimeColumn ,111)


Notice the repeating convert() in both the SELECT part and the GROUP BY part. If you do

-- this query doesn't work in MsSQL!
SELECT UserName convert(char(10),DateTimeColumn ,111) as groupDate, Count(*) as hits FROM someTable
GROUP BY UserName, groupDate


then you will be yelled at with “invalid column name groupDate”.

More on MsSQL is coming as I’m working with it and … ColdFusion! (I’m slowly building up a CFSCRIPT and PHP comparison chart and hopefully will be able to release it soon)