marlon.seton (5/8/2012)
What I find more disturbing than this reliance on implicit conversion and thinking of a DATETIME value being held as a float is the reference to 12.00 AM. Surely everyone knows midnight and noon are neither PM nor AM.
This article sums the situation up quite well: http://en.wikipedia.org/wiki/12-hour_clock#Confusion_at_noon_and_midnight
Even if we take the position that AM and PM, taken literally from their meanings in Latin, do not apply to midnight and noon, there are still certain practicalities to consider. For example, certain CONVERT styles specify AM and PM. By convention, we set midnight as AM and noon as PM:
SELECT CONVERT(char(26), CONVERT(datetime, '2012-07-11 00:00:00', 120), 109);
SELECT CONVERT(char(26), CONVERT(datetime, '2012-07-11 12:00:00', 120), 109);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi