what to do get a two digit number by using datepart () in sql?

Question

I am trying to use DatePart to return a two digit hour. I would like to see 01, 02, 03, ...10, 11, 12 for the hours. How can I do this?

if I entered query as,

select DATEPART(hour,'1900-01-01 07:45:00.010')

then I am getting answer 7. I need to get answer as 07.

Answer 1

Try this for MySql or from SQL Server 2012 -

select FORMAT(DATEPART(hour,'1900-01-01 07:45:00.010'),'00')

For SQL Server before 2012 -

select right('0' + DATEPART(hour,'1900-01-01 07:45:00.010'),2)

Answer 2

I couldn't get the above* solutions to work against SQL Server 2008.

What worked for me was:

select right('0' + convert(varchar,DATEPART(hour,'1900-01-01 07:45:00.010')),2)

returns

07

*all the SQL Server solutions above returned "7" for me, not "07". Instead I used the convert(varchar... to make the "0" + "07" a concatenation of string to string, not string to numeric.

Answer 3

In MySQL,

SELECT LPAD(thing_to_pad, 2, '0')

In MS SQL (I think - I don't use it):

SELECT RIGHT('0' + thing_to_pad, 2)