Leading zeros in an integer field

[PJSWEAT]

Is there anyway to make an integer field retain its leading zeros? In a transformation process one of my procedures has to change a time into an integer so any time that is only minutes 0023 is coming through as 23.

 

[cheyney]

An integer field stores only the numeric value of a number. Displaying leading zeros is a formatting issue and has to be dealt with when the number is printed out. An integer field will store 23 and 0023 in exactly the same way, theres no way you can change this. You could store the number as a string field if you want to preserve leading zeros, or you could add them when you print out the number.

Cheyney

 

[PJSWEAT]

here is the catch. It's not for printing I am actually calculating a time and because the field is imported as an integer it is seeing any time less the 0100 (example 0023)as 23 hours instead of 23 minutes.........recommendation on handling?

 

[cheyney]

something like this?

takes a base date (presumably 00:00) and adds the appropriate number of minutes and hours to it. Updates a column in tblDates called Date with the basedate + the appropriate number of hours and minutes, represented in the column intfield.

DECLARE @BaseDate datetime
SET @BaseDate = whatever

Update tblDates
SET Date = dateadd(mi, right(cast(intfield as varchar), 2), dateadd(hh, intfield/100, @BaseDate))


Cheyney

 

[cheyney]

sorry, the set line needs an extra cast to convert the varchar back to int

like this:

SET Date = dateadd(mi, cast(right(cast(intfield as varchar), 2) as int), dateadd(hh, intfield/100, @BaseDate))


theres alot of ways to do this..

 

[tlbroadbent]

You can easily add leading zeros with the following.

Select CharVal=replace(str(IntCol,4),' ','0')

You can insert ":" using the Stuff function.

Select CharVal=Stuff(replace(str(IntCol,4),' ','0'),3,0,':')

The STR function is handy because it right justifies whereas convert and cast to char or varchar left justifies the number. If you want to get the best answer for your question read faq183-874.

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm