Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

COVERTING NUMERIC TO STRING, PRESERVING LEADING ZEROS Expand / Collapse
Author
Message
Posted Thursday, October 30, 2008 4:20 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:38 AM
Points: 405, Visits: 1,136

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
Posted Thursday, October 30, 2008 10:47 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 2:01 PM
Points: 659, Visits: 6,104
Dave, check out a cleaner, reusable version at http://www.sqlservercentral.com/Forums/Topic594387-145-1.aspx

DAB
Post #594543
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse