Is there a better way to convert SQL datetime from hh:mm:ss to hhmmss?

Question

I have to write an SQL view that returns the time part of a datetime column as a string in the format hhmmss (apparently SAP BW doesn't understand hh:mm:ss).

This code is the SAP recommended way to do this, but I think there must be a better, more elegant way to accomplish this

TIME = case len(convert(varchar(2), datepart(hh, timecolumn)))
             when 1 then       /* Hour Part of TIMES */
               case convert(varchar(2), datepart(hh, timecolumn))
                 when '0' then '24'    /* Map 00 to 24 ( TIMES ) */
                 else '0' + convert(varchar(1), datepart(hh, timecolumn))
               end
            else convert(varchar(2), datepart(hh, timecolumn))
            end
         + case len(convert(varchar(2), datepart(mi, timecolumn)))
              when 1 then '0' + convert(varchar(1), datepart(mi, timecolumn))
              else convert(varchar(2), datepart(mi, timecolumn))
           end
         + case len(convert(varchar(2), datepart(ss, timecolumn)))
              when 1 then '0' + convert(varchar(1), datepart(ss, timecolumn))
              else convert(varchar(2), datepart(ss, timecolumn))
           end

This accomplishes the desired result, 21:10:45 is displayed as 211045.

I'd love for something more compact and easily readable but so far I've come up with nothing that works.

Answer 1

you could use a user-defined function like:

create FUNCTION [dbo].[udfStrippedTime]
(
    @dt datetime
)
RETURNS varchar(32)
AS
BEGIN
    declare @t varchar(32)
    set @t = convert( varchar(32), @dt, 108 )
    set @t = left(@t,2) + substring(@t,4,2)

    RETURN @t
END

then

select dbo.udfStrippedTime(GETDATE())

the logic for the seconds is left as an exercise for the reader

Answer 2

Edit 2: updated to handle 0 --> 24 conversion, and a shorter version:

select replace(left(replace(convert(char,getdate(),8),':',''),2),'00','24') + right(replace(convert(char,getdate(),8),':',''),4)

Back to the slightly longer version :)

Answer 3

SELECT REPLACE('2009-05-27 12:49:19', ':', '') 2009-05-27 124919

Answer 4

Here's a question. Does the formatting need to happen on the Db Server? The server itself really only care about, and is optimized for storing the data. Viewing the data is usually the responsibility of hte layer above the Db (in a strictly academic sense, the real world is a bit more messy)

For instance, if you are outputting the result into an ASP.NET page bound to a GridControl you would just specify your DataFormattingString when you bind to the column. If you were using c# to write it to a text file, when you pull the data you would just pass the format string to the .ToString() function.

If you need it to be on the DbServer specifically, then yeah pretty much every solution is going to be messy because the time format you need, while compact and logical, is not a time format the server will recognize so you will need to manipulate it as a string.

Answer 5

This handles the 00 - > 24 conversion

SELECT CASE WHEN DATEPART(hh,timecolumn) = 0 
    THEN  '24' + SUBSTRING(REPLACE(CONVERT(varchar(8),timecolumn, 108),':',''),3,4)
    ELSE REPLACE(CONVERT(varchar(8),timecolumn, 108),':','') END

Answer 6

SELECT replace(convert(varchar(15),datetimefield, 108), ':','') from Table

Answer 7

NOTE:

The question says that the column is of datatype DATETIME, not the newer (SQL Server 2008) TIME datatype.

ANSWER:

  REPLACE(CONVERT(VARCHAR(8),timecolumn,8),':','')

Let's unpack that.

First, CONVERT formats the time portion of the datetime into a varchar, in format 'hh:mi:ss' (24-hour clock), as specified by the format style value of 8.

Next, the REPLACE function removes the colons, to get varchar in format 'hhmiss'.

That should be sufficient to get a usable string in the format you'd need.

---

FOLLOW-UP QUESTION

(asked by the OP question)

Is an inline expression faster/less server intensive than a user defined function?

The quick answer is yes. The longer answer is: it depends on several factors, and you really need to measure the performance to determine if that's actually true or not.

I created and executed a rudimentary test case:

    -- sample table
    create table tmp.dummy_datetimes (c1 datetime)

    -- populate with a row for every minute between two dates
    insert into tmp.dummy_datetimes
    select * from udfDateTimes('2007-01-01','2009-01-01',1,'minute')

    (1052641 row(s) affected)

    -- verify table contents
    select min(c1) as _max
         , max(c1) as _min
         , count(1) as _cnt
      from tmp.dummy_datetimes

    _cnt    _min                    _max
    ------- ----------------------- -----------------------
    1052641 2007-01-01 00:00:00.000 2009-01-01 00:00:00.000

(Note, the udfDateTimes function returns the set of all datetime values between two datetime values at the specified interval. In this case, I populated the dummy table with rows for each minute for two entire years. That's on the order of a million ( 2x365x24x60 ) rows.

Now, user defined function that performs the same conversion as the inline expression, using identical syntax:

    CREATE FUNCTION [tmp].[udfStrippedTime] (@ad DATETIME)
    RETURNS VARCHAR(6)
    BEGIN
    -- Purpose: format time portion of datetime argument to 'hhmiss'
    -- (for performance comparison to equivalent inline expression)
    -- Modified:
    -- 28-MAY-2009 spencer7593

       RETURN replace(convert(varchar(8),@ad,8),':','')

    END

NOTE: I know the function is not defined to be DETERMINISTIC. (I think that requires the function be declared with schema binding and some other declaration, like the PRAGMA required Oracle.) But since every datetime value is unique in the table, that shouldn't matter. The function is going to have to executed for each distinct value, even if it were properly declared to be DETERMINISTIC.

I'm not a SQL Server 'user defined function' guru here, so there may be something else I missed that will inadvertently and unnecessarily slow down the function.

Okay.

So for the test, I ran each of these queries alternately, first one, then the other, over and over in succession. The elapsed time of the first run was right in line with the subsequent runs. (Often that's not the case, and we want to throw out the time for first run.) SQL Server Management Studio reports query elapsed times to the nearest second, in format hh:mi:ss, so that's what I've reported here.

    -- elapsed times for inline expression
    select replace(convert(varchar(8),c1,8),':','') from tmp.dummy_datetimes

    00:00:10
    00:00:11
    00:00:10

    -- elapsed times for equivalent user defined function
    select tmp.udfStrippedTime(c1) from tmp.dummy_datetimes

    00:00:15
    00:00:15
    00:00:15

For this test case, we observe that the user defined function is on the order of 45% slower than an equivalent inline expression.

HTH