Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

DateTime Puzzle Expand / Collapse
Author
Message
Posted Tuesday, May 8, 2012 8:20 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:57 PM
Points: 3,258, Visits: 1,954
Nice question, even better discussion. Thanks all!
Post #1296489
Posted Tuesday, May 8, 2012 9:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:21 PM
Points: 11,192, Visits: 11,090
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1296561
Posted Wednesday, May 9, 2012 12:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 10, 2014 3:25 PM
Points: 2,133, Visits: 867
The sooner we stop using datetime the better. We now have Date & Time Data Types and DateTime2.

It's fairly straight forward: Dates and Times should be stored as such. If a Timestamp is required then use Datetime2 with its correct level of accuracy.
Post #1296914
Posted Thursday, May 17, 2012 1:14 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, June 19, 2014 10:32 AM
Points: 483, Visits: 243
Easy and straight forward one. Thanks
Post #1302097
Posted Thursday, May 17, 2012 8:06 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:45 PM
Points: 1,589, Visits: 253
Good question. Thanks for submitting.

http://brittcluff.blogspot.com/
Post #1302237
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse