Making dates out of SQL Agent history

I started this post to simply have a reference piece of code on how to combine the run_date and run_time values from the msdb.dbo.sysjobhistory. I could have saved a great deal of time had I known to look at Working With SQL Server Agent Dates/Times (but of course there wouldn't have been the satisfaction of having written this code) The problem is that SQL Server stores dates and times as integer values (YYYYMMDD and HHMMSS) respectively. There isn't a method for concatenating those values into something and casting/converting it into a datetime value. Except there is and it's called msdb.dbo.agent_datetime(@date, @time). My solution was to create a jobhistory view with my code inline while MS provided a scalar function to do the same. An inline TVF might provide better performance but really with the tsql string mashing performance isn't going to be good either way.

CREATE VIEW 
    dbo.SysJobHistory2
AS

SELECT
    JH.*
,   CAST((CAST(JH.run_date / 10000 AS CHAR(4)) + '-' 
    + RIGHT('0' + CAST(((JH.run_date/100) % 100) AS varchar(2)), 2) + '-'
    + RIGHT('0' + CAST(JH.run_date % 100 AS varchar(2)), 2) + 'T'
    + RIGHT('0' + CAST(JH.run_time/10000 AS varchar(2)), 2) + ':' 
    + RIGHT('0' + CAST(((JH.run_time/100) % 100) AS varchar(2)), 2) + ':'
    + RIGHT('0' + CAST(JH.run_time % 100 AS varchar(2)), 2)) AS datetime) AS run_datetime
FROM
    dbo.sysjobhistory JH

Meme Monday for June

This month, Tom LaRock asks dumb sql questions.  Even having had a week to think about this, I'm still stumped on what'd be a good dumb question.  I've been trying to remember some of the dumb things I thought or did when I first started worked in sets but memory eludes me.  Instead, I'll go with a simple one: how do I get SQL Server?  

For those starting out, look at the following pricing and feature list and develop to your heart's content. Do understand your licensing conditions however, I might have known a company that was mistakenly using Developer licenses in production and had a very costly conversion to Enterprise when their error was called out.

• Free - Express edition (10GB storage limit, optional SSRS (reporting) support)
• Free - Compact edition (Ultra-light weight, see this SO discussion) 
• Free - Trial edition (180 day self-expiring full version)
• $37 - Developer edition (full version, not licensed for production environments)

Love what you've seen above? Buy it and move it to a production license.

• $3743 per proc - Workgroup edition (2CPUs, 4GB memory, 524PB storage, vanilla build, has SSRS, no Analysis services)
• $3500 per proc - Web edition (4CPUs, 64GB memory, similar to Workgroup in other regards) 
• $7171 per proc - Standard edition (hardware limitations identical to web edition, basic analysis services, stream insight, fuller version of SSRS  )
• $27495 per proc - Enterprise edition (8CPUs, 2TB memory, full versions of SSIS, SSRS, SSAS)
• $54999 per proc - Datacenter edition (Same as Enterprise + fancier StreamInsight)

SQL Server edition comparison page

Fairly obvious disclaimer. I am not a lawyer, licensing expert and the above prices reflect today's published prices on the MS website and are subject to change.  Also, Azure isn't listed as the cost depends entirely on usage.

Prime take away?  If you don't have your own copy, not work's, of SQL Server, drop a few bucks and make your home machine legal.  I kept going to release parties hoping to win a copy until I saw the developer cost is a paltry sum in comparison to Visual Studio.  At this point in my career however, I've invested in myself and purchased an MSDN license and now I have an all I can eat buffet of MS software. 

Updated 2011-06-09 with information regarding Compact Edition