How to Add Leading Zeros

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » How to Add Leading Zeros

How to Add Leading Zeros

Rate Topic

Display Mode

Topic Options

Author

Message

jonathanwbell

Posted Friday, February 08, 2013 7:34 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 2:20 AM
Points: 3, Visits: 9

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

Post #1417703

anthony.green

Posted Friday, February 08, 2013 7:40 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

jonathanwbell (2/8/2013)

SELECT dbo.SaleItem.SendTime / 360000 AS Hour, dbo.SaleItem.SendTime % 360000 / 6000 AS Minute, dbo.SaleItem.SendTime % 6000 / 100 AS Second,
FROM DBNAME

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.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1417707

jonathanwbell

Posted Friday, February 08, 2013 7:53 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 2:20 AM
Points: 3, Visits: 9

Thank you very much - works perfectly.

Post #1417712

ChrisM@Work

Posted Friday, February 08, 2013 8:48 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 5,608, Visits: 10,970

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
Exploring Recursive CTEs by Example Dwain Camps

Post #1417753

Jeff Moden

Posted Saturday, February 09, 2013 2:54 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 32,906, Visits: 26,789

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1418055

Steven Willis

Posted Saturday, February 09, 2013 10:54 PM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 7:19 AM
Points: 283, Visits: 1,239

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

Post #1418079

Jeff Moden

Posted Sunday, February 10, 2013 1:59 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 32,906, Visits: 26,789

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

--Jeff Moden

Post #1418135

« Prev Topic | Next Topic »

Permissions