COVERTING NUMERIC TO STRING, PRESERVING LEADING ZEROS

  • [font="Verdana"]

    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][/font]

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

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

    DAB

Viewing 2 posts - 1 through 1 (of 1 total)

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