Laziale Laziale - 6 months ago 10
SQL Question

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:
enter image description here

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?

Thanks, Laziale

Answer

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
Comments