Possible to avoid repetition of expression in a QUERY

Question

I have to display time in a weird format. For example, if time is 15:30:45.5000, I need to display "153045.5".

To do this, I have the following query:

SELECT  LEFT(CONVERT(varchar(20), GETDATE(), 114), 2) +     
   SUBSTRING(CONVERT(varchar(20), GETDATE(), 114), 4, 2) +   
   SUBSTRING(CONVERT(varchar(20), GETDATE(), 114), 7, 2) + '.' + 
   SUBSTRING(CONVERT(varchar(20), GETDATE(), 114), 10, 1);

Is there anything I can do to avoid repeating the expression CONVERT(varchar(20), GETDATE(), 114)?

Edit:

I saw a really cool answer here which was deleted for some reason after I refreshed the page, but it gave me the idea to think of an alternative solution:

SELECT REPLACE(RIGHT(CONVERT(varchar(21), getdate(), 126), 10), ':', '')

Although this answer doesn't solve the original question in a generic way, it still solves my problem in a different way.

If at all possible; consider formatting the output in the presentation layer. — David Rushton, Dec 04 '15 at 19:32
BTW: The format I am getting when running your code does not match the format you say you are trying to get. — Shannon Severance, Dec 04 '15 at 19:36
You're right. I actually want a '.' to separate the seconds from the milliseconds. I will fix the query. — Jin Kim, Dec 04 '15 at 19:51

score 5 · Answer 1 · edited Dec 05 '15 at 15:34

5

In addition to functions in other answers, you can calculate a partial result in a common table expression (CTE) or inline view:

; WITH gd(getDate_114)
     AS (SELECT CONVERT(VARCHAR(20), GETDATE(), 114))
SELECT LEFT(getDate_114, 2)
    + SUBSTRING(gd.getDate_114, 4, 2) 
    + SUBSTRING(gd.getDate_114, 7, 2)
    + '.'
    + SUBSTRING(gd.getDate_114, 10, 1)
FROM gd

edited Dec 05 '15 at 15:34

Martin Smith

438,706
87
741
845

answered Dec 04 '15 at 19:33

Shannon Severance

18,025
3
46
67

Jakub Lortz · Answer 2 · 2015-12-04T19:53:01.297

Wrap all the formatting code in a function

create function dbo.FormatTime (@time datetime) 
returns varchar(20)
as
begin
    return (select LEFT(CONVERT(varchar(20), @time, 114), 2) 
            + SUBSTRING(CONVERT(varchar(20), @time, 114), 4, 2) 
            + SUBSTRING(CONVERT(varchar(20), @time, 114), 7, 4))
end

And use it as

select dbo.FormatTime(getdate())

To avoid repetition in the function body, you can store the @time converted to varchar in a variable

declare @dateAsVarchar varchar(20) = CONVERT(varchar(20), @time, 114)

return (select LEFT(@dateAsVarchar, 2) 
        + SUBSTRING(@dateAsVarchar, 4, 2) 
        + SUBSTRING(@dateAsVarchar, 7, 4))

Siyual · Answer 3 · 2015-12-04T19:41:41.403

2

Unfortunately, unless I'm mistaken, there isn't a way to minimize this in SQL Server 2008 R2. You'll have to construct it the way you're already doing it. Another option (as already pointed out by @jonasnas) would be to create a function that returns the format of the current date.

If you are able/willing to upgrade to SQL Server 2012, however, you can take advantage of the FORMAT() function to format the string:

Select Format(GetDate(), N'HHmmss.f')

edited Dec 04 '15 at 19:41

answered Dec 04 '15 at 19:29

Siyual

16,415
8
44
58

1

The : is a . or nothing. The existing query from the OP is different than the stated desired output. Still +1 – paparazzo Dec 04 '15 at 19:40

Possible to avoid repetition of expression in a QUERY

3 Answers3