Extract Date from datetime

Here is a quick script to extract date from a date time stamp column:

select CONVERT(DATETIME, DATEDIFF(dd,0,yourcolumn)) from yourtable

So this value in the table: 2009-11-04 13:00:01.000
Will return this value: 2009-11-04 00:00:00.000

Worked well for me. I had a process that dumped data into a history table. The process took a few minutes to run and only ran once a day. I wanted to hook up a ASP page to it and give the end user some dates to chose based on a query of the history table. I ended up using this:

select DISTINCT(CONVERT(DATETIME, DATEDIFF(dd,0,yourcolumn))) from yourtable

This gave me a list of dates in my history table. Found this date hack and other cool tricks on this page: http://wiki.lessthandot.com/index.php/SQL_Server_Programming_Hacks_-_100%2B_List

sysjobhistory run_duration Conversion

Below is a quick script to convert the run_duration column into 3 separate columns for Hours, Minutes, and seconds. The script will also convert the run_date column to a date. Useful for dropping into Excel and running reports.

SELECT
 runDate = CONVERT (DATETIME, RTRIM(run_date)),
 run_duration/10000 Hours, --hours
 run_duration/100%100 Minutes, --minutes
 run_duration%100 Seconds --seconds
 from sysJobHistory

Just add a job_id in your where clause if you want to track a single job. I used this script to generate a report of run times for a specific job. We had a SQL job run long (so we thought) and wanted to drill into the numbers a bit. After some investigation we discovered that the job was pretty consistent and that the long run time was consistent with the job history.

You can also just combine the hours, minutes, and seconds into 1 column like so:

SELECT
 runDate = CONVERT (DATETIME, RTRIM(run_date)),CAST(run_duration/10000 as varchar)  + ':' + CAST(run_duration/100%100 as varchar) + ':' + CAST(run_duration%100 as varchar)
 from sysJobHistory

From there you can paste the data into Excel and chart it out or do whatever else you want to do with that data.