Time Formats

Microsoft® SQL Server™ recognizes the following formats for time data. Enclose each format with single quotation marks (‘).

14:30

14:30[:20:999]

14:30[:20.9]

4am

4 PM

[0]4[:30:20:500]AM

You can specify a suffix of AM or PM to indicate if the time value is before or after 12 noon. The case of AM or PM is ignored.

Hours can be specified using either a 12-hour or 24-hour clock. Hours in the range 0-12 default to AM, hours in the range 13-23 default to PM. Hours in the range 1-12 represent hours before noon if AM is specified; they represent hours after noon if PM is specified. Hours in the range 13-23 represent hours after noon regardless if PM is specified. It is not legal to specify PM on an hour in the range 0-12. The value 24:00 is not legal, use 12:00AM, 12:00, or 00:00 to represent midnight.

Milliseconds can be preceded by either a colon (:) or a period (.). If preceded by a colon, the number means thousandths-of-a-second. If preceded by a period, a single digit means tenths-of-a-second, two digits mean hundredths-of-a-second, and three digits mean thousandths-of-a-second. For example, 12:30:20:1 means twenty and one-thousandth seconds past 12:30; 12:30:20.1 means twenty and one-tenth seconds past 12:30.

See Also