sql query add zero if the value is a single digit

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.

我想要做的是,如果有一个值,例如15:9:40,比如9,我想把0放在9之前,看起来像是15:09:40。

Any idea how can I solve that within the query?

任何想法如何在查询中解决?

Thanks, Laziale

1 个解决方案

#1

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

T-SQL中有一个名为REPLICATE的内置函数。你可以像这样使用它:

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

#1