Separating Date and Time in T-SQL
This usually isn't necessary, but I'm setting up some Views for a customer doing some reports in Access and the customer needed two fields, one with just the Date and another with just the Time. At first search on Google, it came up with using the DATEPART Function to get each part of the date or time and concatinating those parts together to get what I wanted. Not only was that pretty disgusting, it didn't pad the minutes or seconds (couldn't find an easy way to pad them myself either). So, after doing some more searching, I found that CONVERT can take a 3rd optional parameter of style. Unfortunately you can't do your own custom styles, but right off the bad it takes care of the date and doing some extra work I can get the time format I want like this...
CONVERT(char(10), MyDateTime, 101) AS [Date],
WHEN DATEPART([hour], MyDateTime) > 12 THEN CONVERT(varchar(2), (DATEPART([hour], MyDateTime) - 12))
WHEN DATEPART([hour], MyDateTime) = 0 THEN '12'
ELSE CONVERT(varchar(2), DATEPART([hour], MyDateTime))
) + ':' +
CONVERT(char(2), SUBSTRING(CONVERT(char(5), MyDateTime, 108), 4, 2)) + ' ' +
WHEN DATEPART([hour], MyDateTime) > 12 THEN 'PM'
) AS [Time]
I'm not saying this is the best way, just something I did that seems to work! :) I'm more posting this for myself when I forget how to do it later, but maybe it will help someone as well (this is the first time I've ever needed to do this).