How can i convert binary(16) to Integer or numeric?

HI!!!:shocked:

Im trying to convert 0x00085180F0A2D511B69600508BE96424 to Integer or numeric format.

I just tried, At to many forms and combinations of that query
Help me !!!

SELECT CAST(CAST(CAST("field name " AS nvarchar) AS varbinary) AS float)

select cast(cast("field name " as varbinary)as integer)

select convert(int," field name") from FILE

select convert(varchar," field name") from FILE

The only answer that I have is
-1947638748 or or

And if I try with to many rows of the field at the same format,
It Answer me the same: -1947638748 for all the rows.

Thank`s for allcreate table #t1(f1 binary(16))

select *
from #t1

insert into #t1 values(convert(binary(16),'0x00085180F0A2D511B6960 0508BE96424'))

select convert(integer, f1)
from #t1

--The result is 1093813301. I dont see -1947638748

drop table #t1|||:D THANK YOU!!!, forXLDB

Just one more question please

Whats the reason for the space in the middle of the expression?

Before convert

Original Expresin
'0x00085180F0A2D511B69600508BE96424'

Convert expresin
'0x00085180F0A2D511B6960 0508BE96424'|||thank you...|||Sorry, but the actual answer is MUCH larger! You can't express that VARBINARY value as an integer, or even as a NUMERIC(38) which is the largest SQL Server will allow. The following code shows what I mean:DECLARE
@.b VARBINARY(16) -- binary image to convert
, @.i INT -- Which byte we're working on
, @.m FLOAT -- Multiplier for this byte
, @.a FLOAT -- Accumulator

SET @.b = 0x00085180F0A2D511B69600508BE96424
SELECT @.i = DataLength(@.b), @.m = 1, @.a = 0 -- Start with lowest order byte

WHILE 0 < @.i -- While bytes left to process
BEGIN
-- SELECT @.a, @.m, @.i, SubString(@.b, @.i, 1) -- Show your work
SELECT @.a = @.a + (@.m * CAST(SubString(@.b, @.i, 1) AS INT))
SELECT @.m = 256 * @.m, @.i = @.i - 1 -- Prepare for next byte
END

SELECT @.m -- Show results-PatP|||Thanks Pat Phelan

I like that explication step by step.
It really please me.sql

How can I change the format of a date returned from asp:calendar

Hello!

I have a table in an SQL database, in which I have a field in datetime format.

In my aspx page I would like to get the date the user chooses from an asp: calendar I have and submit it to the DB.

I already have all the code ready, the datasource, the gridview, all other fields to submit, and I just added a template field with the asp:calendar so that the user could choose a date.

I′m getting this error when I run the page: "Conversion from type 'Date' to type 'Boolean' is not valid."

It seems to be a problem about the date that is given by the Calendar object (?) and the one I should submit to my DB.

Here′s the part of the code where I have my standard Calendar binded to the correspondant field:

<asp:Calendar ID="Calendar1" runat="server" SelectedDate='<%# Bind("data")%>' Visible='<%# Eval("data")%>'>
</asp:Calendar>

I′m gessing I should probably change the format of the date somehow before submit it to the DB, but how?

Thank you all,

RR

Format(dateVariable,"MM/dd/yyyy")

|||

sorry the noobness, but where can I do that?

in a script section in the beginnig of the page?

|||

You have bound the "data" column to both the SelectedDate and the Visible property. SelectedDate is of type Date, and Visible is of type Boolean. What datatype is the "data" column?

|||

You′re asking about the datatype in the db, right?

It′s datetime. (don′t know if it′s the best datatype, any advise here?) I only need a data like DD-MM-YYYY but when building my table in SQL, I have no format like this...

An update to this issue, I erased the visible property and the page at least runs, but no connection between the calendar and my field... maybe it′s better to explain my objective:

What I would need is a gridview where I can see my records. (done)
In the default view I would see all the fields in normal textboxes, (ok!, done)
When clicking insert new or edit, I would like to let the user choose a date from the calendar!
Can anyone help me to buid a thing like this?

THKS

How can I change a datetime column format?

How can I change a datetime column format?
and How can I make a subtotal'
--
LUIS ESTEBAN VALENCIA
MICROSOFT DCE 2.
MIEMBRO ACTIVO DE ALIANZADEV> How can I change a datetime column format?
Use .NET formatcodes - e.g. on the Format property of a textbox. Details on
MSDN:
Standard datetime format strings:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandarddatetimeformatstrings.asp
Custom datetime format strings:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomdatetimeformatstrings.asp
> and How can I make a subtotal'
Please read this section in RS BOL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_structure_objects_v1_7vi0.asp;
in particular the section of how to add subtotals in report designer.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
news:O%23IAS9e3EHA.2696@.TK2MSFTNGP14.phx.gbl...
> How can I change a datetime column format?
>
> and How can I make a subtotal'
>
> --
> LUIS ESTEBAN VALENCIA
> MICROSOFT DCE 2.
> MIEMBRO ACTIVO DE ALIANZADEV
>sql

How can I append children to parents in a SSIS data flow task?

I need to extract data to send to an external agency in their supplied format. The data is normalised in our system in a one to many relationship. The external agency needs it denormalised.

In our system, the parent p has p_id, p_attribute_1, p_attribute_2, p_attribute_3 and the child has c_id, c_attribute_a, c_attribute_b, c_parent_id_fk

The external agency can only use a delimited file looking like

p_id, p_attribute_1, p_attribute_2, p_attribute_3, c1_attribute_a, c1_attribute_b, c2_attribute_a, c2_attribute_b, ...., cn_attribute_a, cn_attribute_b

where n is the number of children a parent may have. Each parent can have 0 or more children - typically between 1 and 20.

How can I achieve this using SSIS? In the past I have used custom built VB apps with the ADO SHAPE command but this is not ideal as I have to rebuild each time to alter the selection criteria and and VB is not a good SQL tool.

You can use the pivot transform to move rows to columns, but it doesn't handle a variable number of columns very well. I'd try creating a script component that outputs the set of children for a parent as one long string. Then you can append it to the parent value.|||

jwelch wrote:

You can use the pivot transform to move rows to columns, but it doesn't handle a variable number of columns very well. I'd try creating a script component that outputs the set of children for a parent as one long string. Then you can append it to the parent value.

I agree with John.

The way I would approach this is to join the parent and child records together in a SQL statement so you have a row for each parent/child combination. Use this statement in the Source of a Data Flow and run the rows into an asynchronous script transformation component. You'll write code in the script to denormalize the incoming rows into a single output row for each parent. SSIS won't adapt to having an arbitrary number of child columns and I don't think there is a need for it. The output of the script should be a single column (string if it can fit, otherwise text) that is already delimited and can go directly into a flat file destination.

So the script is the only tricky part. It will look at the p_id on each row it receives to determine if this is the same parent as the last input row. If it isn't, then it will write the output row it was working on for the previous p_id to the output and start a new one by appending the p_id and parent and child attributes to a string. If it is the same p_id, it will continue to append the child attributes until it sees the next p_id or the last row.

Something like this oughta do it:

Code Snippet

Public Class ScriptMain
Inherits UserComponent

Dim last_pid As Integer = -1
Dim current_row As StringBuilder = Nothing

Public Overrides Sub FinishOutputs()
' the input is finished, flush the current row
WriteRow()
End Sub

Public Sub WriteRow()
If Not current_row Is Nothing Then
With Output0Buffer
.AddRow()
.OutputLine = current_row.ToString()
' if we use a dt_text column
'.OutputLine.AddBlobData(ASCIIEncoding.ASCII.GetBytes(current_row.ToString()))
End With
End If

End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
If Row.pid <> last_pid Then

' this is a new p_id starting, write the previous one to the output
WriteRow()

' remember this pid for future rows
last_pid = Row.pid

' create new buffer
current_row = New StringBuilder()
' append parent attributes
With current_row
.Append(Row.pid.ToString())
.Append(",")
.Append(Row.pattribute1)
.Append(",")
.Append(Row.pattribute2)
.Append(",")
.Append(Row.pattribute3)
End With
End If

' append child attributes
With current_row
.Append(",")
.Append(Row.cattributea)
.Append(",")
.Append(Row.cattributeb)
End With
End Sub

End Class

Note: The JOIN should group all of the parents together, but just in case you should probably throw an ORDER BY p_id in for that SQL statement.

|||

Phew. I went out 2 hours ago with the express purpose of coming back and answering this but John and Jay have beaten me to it

I was going to suggest exactly what Jay has said. Its kind of hobson's choice on this one - the metadata is unknown at design-time therefore you HAVE to have a single column with all the values concatenated. The fact that you are outputting to a file means that it really doesn't matter anyway.

I would add that this would be a great candidate for a custom component if you can be bothered to build it.

-Jamie