CODEWITHSUNDEEP
sql-serverhtmldatetimeinputtime
DreamTeK
DreamTeK

Reputation: 34177

MS SQL : Convert datetime to web standards complient TIME format

Official formatting for time input fields according to the web specification.

W3 seems to indicate that accepted formats are:

23:20:50.52
17:39:57

http://www.w3.org/TR/html-markup/input.time.html

QUESTIONS

  1. Should time input fields should be populated fully with seconds and milliseconds or is 00:00 acceptable?

  1. From MS SQL iS ISO8601 CONVERT 126 the correct format or is SQL CONVERT 108 hh:mm:ss acceptible?

    CONVERT(VARCHAR(10),[MyDate]),108) AS [MyDate]

Click for list of SQL CONVERT() Functions

  1. What is an appropriate way to pull a correctly formatted time field from an MSSQL datetime field using convert 126?

Upvotes: 0

Views: 1090

Answers (1)

Andriy M
Andriy M

Reputation: 77657

The document clearly states the accepted format for time values:

Value: A valid partial-time as defined in [RFC 3339].

The relevant part of RFC 3339 has the following definitions:

time-hour       = 2DIGIT  ; 00-23
time-minute     = 2DIGIT  ; 00-59
time-second     = 2DIGIT  ; 00-58, 00-59, 00-60 based on leap second
                          ; rules
time-secfrac    = "." 1*DIGIT

partial-time    = time-hour ":" time-minute ":" time-second
                  [time-secfrac]

which means that partial-time consists of hours (2 digits, 24-hour notation), followed by a colon (:), followed by minutes (2 digits), followed by another colon, followed by seconds, then optionally followed by a decimal fraction (a decimal point followed by 1 or more digits).

So, seconds are mandatory, but the fractional part is optional, which means that 00:00 is not a valid partial-time as defined by RFC 3339, but 00:00:00 is fine, as are 00:00:00.0, 00:00:00.000, 00:00:00.0000 and other variations.

As for the CONVERT function, the 108 style matches the specification and if you do not need milliseconds (or nanoseconds or whatever), it is all right to use it. If you want milliseconds, you can use style 114 and replace the last : with a . or you can use styles 121 or 126 and extract just the time portion. How to extract it? Well, CONVERT would give you a string, so just apply SUBSTRING to it with correct arguments.

Upvotes: 1

Related Questions