Monday, March 10, 2014

Cool trick to remove times from datetime data types in MSSQL

​A frequent request in SQL is to roll up a bunch of records by date.  Often when you dig into the data you find that the column in question has that snarky time added to the end of all of the dates and nothing will roll up.  So what do you do?
A common approach is to pull out the CONVERT funtion and start treating your dates as strings and go from there.  Unfortunately, that's not really efficient.
I ran across this little gem:
SELECT DATEADD(dd, DATEDIFF(dd, 0, dbo.MYDATETIME), 0) AS MYDATETIME
What this does is take your date and figure out how many days are in it from your base date (usually 1/1/1900) and then add that many days back to your base date.  The time value is stripped off because you're working in days.
Date math in sql works with integers and is very fast.  Give it a try the next time you need to strip off that time.

Friday, March 7, 2014

Quest for the digital Grail

Neat! I'm getting an opportunity to start looking at a project or 2 in Grails.  It should be fun.

It's funny how a few years ago, I was really skeptical of ORMs (Object Relational Mapping frameworks) and how they built the persistence layer in these web frameworks.  Now, I find that most of them are just great once you get over the initial learning bit.  It's nice to be able to just do a simple select with a good reference tool handy.  Build up a little bit at a time and the next thing you know, you're doing complex joins in your model, managing caches, and thinking about server performance.

Grails is using Hibernate to power its GORM (Grails ORM), so I have pretty strong confidence that most of the crufty stuff has been worked out.

I'll do a few posts if I find out anything interesting.