Welcome to Julian Kuiters Monday, May 21 2018 @ 07:48 AM AEST

Snippet: Select the Time portion from a datetime

  • Contributed by:
SQL Server 2000

Earlier on I wrote about how you can Select just the date part from a DateTime or SmallDateTime data type. (See Snippet: Select a Date or Current Date Without Time)

Using the CONVERT( ... , ... , style ) function we can specify the style attribute, that lets us specify to SQL Server (using style 14 or 114) that we just want the time portion.

-- Get current time (without date) in 24-hour time:
SELECT CONVERT( CHAR(8), GetDate(), 14)

By changing the number of CHAR characters you can select the time with or without milliseconds and seconds.

-- Get current time (without date) in HH:MM:SS:mmm format:
SELECT CONVERT( CHAR(12), GetDate(), 14)

-- Get current time (without date) in HH:MM:SS format:
SELECT CONVERT( CHAR(8), GetDate(), 14)

-- Get current time (without date) in HH:MM format:
SELECT CONVERT( CHAR(6), GetDate(), 14)

Also see Snippet: Select a Date or Current Date Without Time