COVERTING NUMERIC TO STRING, PRESERVING LEADING ZEROS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » COVERTING NUMERIC TO STRING, PRESERVING...

COVERTING NUMERIC TO STRING, PRESERVING LEADING ZEROS

Rate Topic

Display Mode

Topic Options

Author

Message

dave-dj

Posted Thursday, October 30, 2008 4:20 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, June 13, 2013 5:09 AM
Points: 380, Visits: 1,020

Hi all,

thought this might be helpful to someone else.

if you want to convert a numeric value e.g. 01 to a string 01 then this can be done like so:

select '0' + CAST(01 as varchar(2)) - results in 01

if you want to make this a little more generic so you can accomadate a conversion if required for number ranging 1-20 for example, then you could do this

select CAST(RIGHT('0'+ CAST(10 AS VARCHAR(2)),2) - this will result in 010, but using the right command we can strip this back to the two characters we need - 10.

an example of how I have used this is to construct a date from 3 fields:
OPENDATE - a datetime field holding just the date
OPENHOURS - the hour in which a task was completed for the given date
OPENMINUTES - the minute of the hour in which a task was completed for the given date.

the aim of the select statement is to convert
2008-10-27
09
53

to a datetime field of '2008-10-27 09:53:00'

SELECT convert(VARCHAR(30), SUBSTRING(convert(varchar(20), OPENDATE, 120), 0, 11) + ' ' + CAST(RIGHT(' 0'+ CAST(OPENHOURS AS VARCHAR(2)),2) + ':'+ RIGHT(' 0'+ CAST(OPENMINUTES AS VARCHAR(2)),2) + ':00' AS VARCHAR(10))) AS [Open DT]

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)

Post #594180

Scalability Doug

Posted Thursday, October 30, 2008 10:47 AM

SSChasing Mays

Group: General Forum Members
Last Login: Thursday, April 05, 2012 12:43 PM
Points: 655, Visits: 6,097

Dave, check out a cleaner, reusable version at http://www.sqlservercentral.com/Forums/Topic594387-145-1.aspx

DAB

Post #594543

« Prev Topic | Next Topic »

Permissions