Time Formats
Microsoft SQL Server 2005 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
SELECT CAST('01/01/2000 14:30' AS datetime)
--2000-01-01 14:30:00.000
SELECT CAST('01/01/2000 14:30:20:999' AS datetime)
--2000-01-01 14:30:21.000
SELECT CAST('01/01/2000 14:30:20.9' AS datetime)
--2000-01-01 14:30:20.900
SELECT CAST('01/01/2000 4am' AS datetime)
--2000-01-01 04:00:00.000
SELECT CAST('01/01/2000 4 PM' AS datetime)
--2000-01-01 16:00:00.000
SELECT CAST('01/01/2000 04:30:20:500AM' AS datetime)
--2000-01-01 04:30:20.500
SELECT CAST('01/01/2000 04:30:20:500 AM' AS datetime)
--2000-01-01 04:30:20.500
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. This is how the hour values are interpreted:
- The hour value of 0 represents the hour after midnight (AM), regardless of whether or not you specify AM. You cannot specify PM when the hour equals 0.
- Hour values from 1 through 11 represent the hours before noon if neither AM nor PM is specified. They also represent the hours before noon when AM is specified. They represent hours after noon if PM is specified.
- The hour value 12 represents the hour that starts at noon if neither AM nor PM is specified. If AM is specified, it represents the hour that starts at midnight. If PM is specified, it represents the hour that starts at noon. For example: 12:01 is 1 minute after noon, as is 12:01 PM, while 12:01 AM is 1 minute after midnight. Specifying 12:01 AM is the same as specifying 00:01 or 00:01 AM.
- Hour values from 13 through 23 represents hours after noon if AM or PM is not specified. They also represent the hours after noon when PM is specified. You cannot specify AM when the hour value is from 13 through 23.
- An hour value of 24 is not valid, use 12:00 AM 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 indicates twenty and one-thousandth seconds past 12:30; 12:30:20.1 indicates twenty and one-tenth seconds past 12:30.