October 30, 2008 at 4:20 am
[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)
October 30, 2008 at 10:47 am
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