SQL query add zero if the value is a single digit

Question

I have this part of a query:

        CONVERT(VARCHAR, DATEDIFF(dd, t.startdate,SYSDATETIME())) + '':'' + 
    CONVERT(VARCHAR, DATEDIFF(hh, t.startdate,SYSDATETIME()) % 24) + '':'' + 
CONVERT(VARCHAR, DATEDIFF(mi, t.startdate,SYSDATETIME()) % 60) as Days

which translates to this:

What I want to do is, if there is a single value, for example in 15:9:40, such as 9, I want to put 0 in front of 9, to look something like 15:09:40.

Any idea how can I solve that within the query?

[How to pad with leading/trailing chars in sql server](http://stackoverflow.com/questions/29866779/using-a-sql-function-to-pad-strings-with-spaces/29866897#29866897) — Zohar Peled, Jul 23 '15 at 19:24

Kapol · Accepted Answer · 2015-07-23T19:25:20.367

1

There is a built-in function in T-SQL called REPLICATE. You can use it like this:

REPLICATE('0', 2 - [length of your expression])

In you case the following should work:

REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, DATEDIFF(dd, t.startdate,SYSDATETIME())))) + 
CONVERT(VARCHAR, DATEDIFF(dd, t.startdate,SYSDATETIME())) + '':'' +
REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, DATEDIFF(hh, t.startdate,SYSDATETIME()) % 24))) +
CONVERT(VARCHAR, DATEDIFF(hh, t.startdate,SYSDATETIME()) % 24) + '':'' + 
REPLICATE('0', 2 - LEN(DATEDIFF(mi, t.startdate,SYSDATETIME()) % 60)) +
CONVERT(VARCHAR, DATEDIFF(mi, t.startdate,SYSDATETIME()) % 60) as Days

edited Jul 23 '15 at 19:25

answered Jul 23 '15 at 19:00

Kapol

6,383
3
21
46

Can you please apply that function within my query, I'm not sure how/where to place that. Thx Kapol – Laziale Jul 23 '15 at 19:06
@Laziale I edited my answer. I hope I got the parenthesis right. – Kapol Jul 23 '15 at 19:17
@Laziale Take a look now. – Kapol Jul 23 '15 at 19:25

SQL query add zero if the value is a single digit

1 Answers1