How to Add Leading Zeros

Question

How to Add Leading Zeros

jonathanwbell

SSC Veteran

Points: 229
More actions
February 8, 2013 at 7:34 am

#268298

Hello,
I am a novice to SQL, I can do the simple queries but at the moment I struggle with writing complex ones. If someone could help me with creating one that would be fantastic.
I have a column called SendTime within my table called SaleItem. The issue with this field is that it set as a number, as an example: 4295818. Now to convert this into an actual TIME I have to do the following query:
SELECT dbo.SaleItem.SendTime / 360000 AS Hour, dbo.SaleItem.SendTime % 360000 / 6000 AS Minute, dbo.SaleItem.SendTime % 6000 / 100 AS Second,
FROM DBNAME
Since the it converts the number into a time it will display incorrect when the number is less than 10. As an example the HOUR column would display 07 as 7. My question is how can I convert the SendTime column from a number into a TIME and have leading zero if the time is below 10?
If there is anything else you need to know or if I have missed anything off please say.
Kind Regards
Jon

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

jonathanwbell (2/8/2013)
Hello,
I am a novice to SQL, I can do the simple queries but at the moment I struggle with writing complex ones. If someone could help me with creating one that would be fantastic.
I have a column called SendTime within my table called SaleItem. The issue with this field is that it set as a number, as an example: 4295818. Now to convert this into an actual TIME I have to do the following query:
SELECT dbo.SaleItem.SendTime / 360000 AS Hour, dbo.SaleItem.SendTime % 360000 / 6000 AS Minute, dbo.SaleItem.SendTime % 6000 / 100 AS Second,
FROM DBNAME
Since the it converts the number into a time it will display incorrect when the number is less than 10. As an example the HOUR column would display 07 as 7. My question is how can I convert the SendTime column from a number into a TIME and have leading zero if the time is below 10?
If there is anything else you need to know or if I have missed anything off please say.
Kind Regards
Jon

Something like this

RIGHT('0'+CONVERT(VARCHAR,(dbo.SaleItem.SendTime / 360000)),2)

As the output is an INT, you need to make it a string to be able to add a 0 to the front, so thats the CONVERT(VARCHAR,(dbo.SaleItem.SendTime / 360000)) part.

Then you add a 0 to the beginning of the string, then select the right 2 values. So if its 1 the gets a string of 01, then gets the right 2 values and displays 01, if its 10 it gets a string of 010, then gets the right 2 values and displays 10.

jonathanwbell SSC Veteran Points: 229 More actions · Answer 2

jonathanwbell

SSC Veteran

Points: 229

February 8, 2013 at 7:53 am

#1585967

Thank you very much - works perfectly.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Are you trying to turn these three values into a time? Try this:

SELECT

d.SendTime / 360000 AS Hour,

d.SendTime % 360000 / 6000 AS Minute,

d.SendTime % 6000 / 100 AS Second,

CAST(DATEADD(ms,d.SendTime*10,CAST(CAST(GETDATE() AS DATE) AS DATETIME)) AS TIME)

FROM (SELECT SendTime = 4295818) d

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

If the integer "times" that you have will be less than 24 hours, then the 4295818 number that you used appears to be "hundreths of seconds". If well multiply that by "10", the we have "thousandths of seconds" or milli-seconds and that makes for a pretty easy conversion including a display conversion.

SELECT CONVERT(CHAR(12),DATEADD(ms,4295818*10,0),114)

Of course, you can add a FROM clause to that and change the 4295818 to the SendTime column name.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 5

Just a side issue, but an important one. I think it was Jeff who some time ago posted something about always validating any string being converted to a datetime datatype. Perhaps just using ISDATE() might be sufficient but I've found the behavior of ISDATE() and the behavior of the several types of datetime datatypes are not always in agreement! If the string you generate won't convert it can cause an execution error.

What I like to do is add something like this snippet below (using Jeff's conversion example) to any procedure where I'm converting strings to dates/times.. Of course you'll have to decide how to handle things when it catches an invalid conversion.

BEGIN TRY

SELECT @dOutputTime = CAST(CONVERT(CHAR(12),DATEADD(ms,@iInputValue*10,0),114) AS TIME)

END TRY

BEGIN CATCH

SELECT @dOutputTime = NULL --[or whatever]

END CATCH

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

That's a good test, Steve, especially for this particular type of conversion. Thanks for posting it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)