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)
You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.
Subscribe in a reader. Enjoy reading my blog? Subscribe to my RSS to read new post whenever I publish something new.
yeah, but
SELECT UserName convert(char(10),DateTimeColumn ,111) as groupDate, Count(*) as hits FROM someTable
GROUP BY UserName, convert(char(10),DateTimeColumn ,111)
is the same as
SELECT UserName convert(char(10),DateTimeColumn ,111) as groupDate, Count(*) as hits FROM someTable
GROUP BY UserName, DateTimeColumn
as the convert in the GRUP BY does not really group by that dateformat. (111, whatever that means..).. so the magic is really in the select..
for example try some crazy substringing around your group by to make it nonexistent like:
SUBSTRING(Convert(varchar(max), DateTimeColumn, 103), 0, 0)
it will still do the group by DateTimeColumn, it is the select format that makes the difference!!
Whhhhhhatttttt!