Convert XML Date in T-SQL

Our persistent objects use an internal dataset to hold their data. When I
save to SQL Server 2000, I do so in one call to the database sending the XML
representation output from the DataSet.GetXml() method and shredding the XML
values into their tables & columns via T-SQL. Everything works great, except
for converting the default XML DateTime output from the DataSet. The output
is like this: 2004-10-20T16:00:00.0000000-05:00. But the list of compatible
T-SQL formats accepted by CONVERT does not show this format (List found here
http://msdn.microsoft.com/library/de...ca-co_2f3o.asp).
The closest format I can see is Style 126: yyyy-mm-dd Thh:mm:ss.mmm. If I
pass the default DataSet XML date format to CONVERT it throws an error. What
I’m currently doing is pre-processing the XML string before sending it to the
database. I iterate through each known date field and convert the value
before sending to SQL Server. This seems unnecessary, but it also seems
unnecessary that the default ADO XML date output is by all appearances
incompatible with SQL Server’s T-SQL CONVERT function. What am I missing
here? Know any ideas?
Hello, MPalmer78!
You wrote on Thu, 21 Oct 2004 09:24:23 -0700:
[Sorry, skipped]
See KB811767.
With best regards, Alex Shirshov.

convert rows to columns

Hi All,

I have two tables

TblEmployee

EmpID,

EmpName

TblAddress

AddressID

EmpID

Address1

Address2

AddressType (will hold values like ‘TP’, ‘HP’ storing different address types)

If I do a simple join assuming 2 different address types exists for each employee. I would be getting two rows. But I need address1 and address2 of different address types as four columns I mean address1 of type ‘TP’, address2 of type ‘TP’, address1 of type ‘HP’, address2 of type ‘HP’

along with employee information.

Can some body help me with the query.select *
from TblEmployee e
inner join (
select EmpID,
TP_Address1 = max(case when AddressType = 'TP' then Address1 end),
TP_Address2 = max(case when AddressType = 'TP' then Address2 end),

HP_Address1 = max(case when AddressType = 'HP' then Address1 end),

HP_Address2 = max(case when AddressType = 'HP' then Address2 end)
from TblAddress
group by EmpID
) a
on e.EmpID = a.EmpID|||Thanks a lot K H Tan

Convert numbers to Month Name

I have a stupid field in my database that doesn't hold a date but I have to use it to determine the month in my Chart in SSRS 2005. So for example, check this out:

http://www.webfound.net/chart_months.jpg

Ok, so how can I change those numbers to the Month Name? I tried MonthName() around my field in the expression builder, but that's only for a datetime field.

Write a UDF with input as int month and return it as varchar month. Use 13 CASE statements (12 for 12 months and 1 for error)|||

How about something like convert(datetime,'2006' + <fieldname> + '01') within your SQL statement?

for padding 0's

select convert(datetime,'2006' + fieldname + '01')
from
( select case <fieldname> when > 9 then <fieldname> else '0' + convert(char(1),<fieldname>) end fieldname ) derivedtable1

|||

The function MonthName, in SRSS2005, accepts an Integer parameter, so I'm not sure I understood the question.

|||hmm, then I wonder why that didn't work...MonthName(fieldname)|||

Paulo X , look at the link to my chart above. Those are integer values coming in from my dataset on a field in our DB table called systemmonth. Don't ask me why they did it that way but I need to take those values and convert them to Month Names....hopeing to do this either through SQL or preferably using a function in Reporting Server 2005 as you stated. I tried wrapping the systemmonth like this but it didn't have any affect:

MonthName(myfieldname)

|||

this is what I'm talking about, I put this in as a category group field and created this expression behind it:

=MonthName(Fields!SystemMonth.Value)

|||

What happens when you use MonthName? Is there any error message?
You say that you have used the MonthName function into the group expression. You must used it also in the Label expression! In fact, you may use MonthName only in the Label, you don't need to use it to group data.

Regards

|||

Hi,

Just try

=Monthname(1) or

=MonthName("1")

Is it working. then assign your field values. the second statement also works.

Amarnath

Convert Image Datatype To Int

Hi,
I have a table which has a field defined as Image. I guess it was defined
that way to hold any value or type.
From what I am reading it seems that I can cast from image to binary and
then from binary to varchar or int...
Whenever I try to convert I keep getting zero as a value. I set up the test
so that I should get an integer value of 5050, but it just does not seem to
work. Is there something wrong with my code:
declare @.c varchar(100)
declare @.trimmed_c varchar(100)
declare @.i int
select @.c = cast(cast(s.VariableValue as varbinary) as varchar)
from SWVariables s where s.VariableName = 'deal_id' and s.ExecutionID = 5
select @.trimmed_c = substring(@.c, 1, CHARINDEX(char(0), @.c, 1) - 1)
select @.i = cast(@.trimmed_c as int)
select @.i as 'deal_id'
Any help would be great, thanks.Depending on how the data was inserted into the image column. Here is an
example that shows successful conversion both ways.
create table tmp(img image default '0x0')
insert tmp values(default)
go
declare @.i int, @.b varbinary(4), @.ptr binary(16)
set @.i=123
set @.b=convert(binary(4),@.i)
select @.ptr=textptr(img)
from tmp
writetext tmp.img @.ptr @.b
go
select img,convert(int,convert(binary(4),img)) [i]
from tmp
go
drop table tmp
-oj
"Dianna" <Dianna@.discussions.microsoft.com> wrote in message
news:F9DA6D91-5256-495A-A7AB-700F955483BD@.microsoft.com...
> Hi,
> I have a table which has a field defined as Image. I guess it was defined
> that way to hold any value or type.
> From what I am reading it seems that I can cast from image to binary and
> then from binary to varchar or int...
> Whenever I try to convert I keep getting zero as a value. I set up the
> test
> so that I should get an integer value of 5050, but it just does not seem
> to
> work. Is there something wrong with my code:
> declare @.c varchar(100)
> declare @.trimmed_c varchar(100)
> declare @.i int
> select @.c = cast(cast(s.VariableValue as varbinary) as varchar)
> from SWVariables s where s.VariableName = 'deal_id' and s.ExecutionID = 5
> select @.trimmed_c = substring(@.c, 1, CHARINDEX(char(0), @.c, 1) - 1)
> select @.i = cast(@.trimmed_c as int)
> select @.i as 'deal_id'
> Any help would be great, thanks.
>