Formatting Question

I have an old DB2 app that has date values in 4 fields (i.e. Date1_MO, Date1_DA, Date1_CN, Date1_YR). I have several MSAccess queries that I convert this to a date by doing the following:

CDate(Date1_MO & "/" & Date1_DA & "/" & Date1_CN & Format(Date1_YR,"00"))

Piece of cake...however I am struggling with this in MSSQL.

Mostly I am fighting formatting the Year. As you can see, If I were to concatinate the above values i would come up with something like 3/9/204 for a date of March 9, 2004. (Each field is a numeric value).

I have gotten this far...

select CAST(Date1_MO as varchar(2))+ '/' + CAST(Date1_DA as varchar(2))
+ '/' + CAST(Date1_CN as varchar(2))+ CAST(Date1_YR as varchar(2)) as Date1
From tPrices

I still need to convert the whole string to a date, but more importantly, I cannot figure out how to get the last element (Year) to format as '04' instead of '4'. I can't concatinate a 0 in front of it for obvious reasons. (Athough I was tempted, just joking)

I looked through a lot of the T-SQL docs but have come up dry.

Anyway HELP!!!!!!Try this for the last 2 digits of the year:

right('0'+CAST(Date1_YR as varchar(2)), 2)|||Came to the same conclusion about the same time you replied...

Just playing with the conversion now.

Thanks for your response...

Formating XML field in report

Hi:
The database we are reporting from (DB2 running on AS/400) has a field with
the data type of CLOB. We would like to perform an XSLT on this field within
the same report that contains more "normal" data (strings, numeric, etc.)
types.
This database contains error and status messages that are generated as
various processes are run. We would like to be able to present the CLOB data
on the report based on the type of process that produced the data. In other
words, if process A produced the entery in to the database, on the report for
process A, format the CLOB data this way, if process B, then the CLOB data is
represented in another way. The data in the CLOB is XML and each process has
its own schema.
Right now I am stumped. Is this possible? Can you point me in the right
direction?
Thanks.You're probably going to need to write a custom function (or perhaps a
custom assembly if you're going to need to read XSLTs from disk) to do this.
You could pass the data along with the process to your custom function which
would apply the transform and return the string to display in the textbox.
E.g. =Code.CLOBClass.Convert(Fields!CLOBData.Value,Fields!Process.Value)
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
in message news:95B8ADFD-2A23-4DD5-A039-971AC84B5AF9@.microsoft.com...
> Hi:
> The database we are reporting from (DB2 running on AS/400) has a field
with
> the data type of CLOB. We would like to perform an XSLT on this field
within
> the same report that contains more "normal" data (strings, numeric, etc.)
> types.
> This database contains error and status messages that are generated as
> various processes are run. We would like to be able to present the CLOB
data
> on the report based on the type of process that produced the data. In
other
> words, if process A produced the entery in to the database, on the report
for
> process A, format the CLOB data this way, if process B, then the CLOB data
is
> represented in another way. The data in the CLOB is XML and each process
has
> its own schema.
> Right now I am stumped. Is this possible? Can you point me in the right
> direction?
> Thanks.