Archive | SQL RSS for this section

SQL Server R2 RTM

SQL Server 2008 R2 officially RTM’d.

I’ve been working with the SQL CTP and SharePoint Beta, and the BI story is pretty incredible.  Here’s a quote that’s pretty accurrate (it’s not as easy as they make it sound, but the BI capabilities are still pretty impressive).

With the release of SQL Server 2008 R2, Microsoft is introducing powerful new managed self-service BI capabilities, bridging the gap between IT professionals and end users by leveraging familiar tools such as Microsoft Excel 2010 and Microsoft SharePoint Server 2010. With the new BI technology named PowerPivot, end users can bring data from virtually anywhere and manipulate large datasets with ease while still enabling IT to monitor and manage user-generated BI solutions.

As soon as the code his MSDN, it’ll be time to rebuild the SharePoint 2010 SQL server.

TechNet blog found here:  
Press Release found here:

Chopping off the time in a DateTime field in SQL

Sometimes you need to chop off the time piece of a datetime field, usually in a report. The most common and natural way to do this is to set up the report and change the display format to show date and not the times. But what if you need to group your SQL by date, regardless of time? What if you want to sort by date, then by category? If your dates have times in them, the category won’t matter for the most part.

Well I ran across the situation today where we needed items grouped by dates, then sorted by category. 

For SQL Server 2008, you can cast as a Date. Finally.

CAST(MyDateField) AS date

For SQL Server 2005, the key is quite simple. A datetime value in SQL Server is just a float in disguise. So cast your date into a float, floor it, and cast back to a date. It works amazingly well.

cast(floor(cast(MyDateField as float)) as datetime)

Here’s a screenshot of a sql query that shows before and after values, side-by-side.

Let’s say you wanted to remove the date. That is easy as well. The SQL below should explain it. The result is a float, the fraction of the day that represents time. We don’t have  a time fieldtype, so we can show this as a float or convert it to a string. I’ll leave the string conversion up to the reader, since I don’t need it for this exercise.

To verify the output, let’s do a little math. 6am is 25% of the day, so .25 checks out. 8:30 am is 8.5 hours of 24, so .35416666 is confirmed.

So there it is, very simple and very efficient SQL to remove the timestamp from a date field in a query.