Check if column (int) is true or false (bool)

Is there some way I may "convert" an int column to boolean with the
SQL query? I have a database with an int column containing only 0s and
1s - using it as a Boolean. Now, I need the Boolean values false and
true in order to use the .net checkbox.
I have tried the following:
SELECT userID, (chkName = 1) AS bChkName, name
FROM User
When chkName = 1 it will return true, and vice versa when it equals 0.
This should produce something like this:
userID bChkName name
2 true Peter
3 true Linda
4 false John Doe
according to the int in chkName.
This of course didn't work and now I am hoping for your help... do you
know of any solution?
royend.
This really does not solve my problem, as it returns a string and not
a Boolean value.
The asp:checkbox requires a boolean in order to become checked or
unchecked.
In worst case scenario I'll have to code inside some of Visual's
predefined and automatically added code...
Still, I am a bit surprised that SQL cannot return a bool value for
one of its column based on a simple test.
Thanks for your answer though.
royend
On 25 Jun, 13:02, "vt" <vinu.t.1...@.gmail.com> wrote:
> royend
> there is no direct way to do this
> select userID, case when chkName =1 then 'true' else 'false',name from user
> Regards
> VT
> Knowledge is power, share it...http://oneplace4sql.blogspot.com/"royend" <roy...@.gmail.com> wrote in message
> news:1182768635.843541.88960@.c77g2000hse.googlegro ups.com...
>
>
>
>
> - Vis sitert tekst -
|||> This really does not solve my problem, as it returns a string and not
> a Boolean value.
And ASP.Net can't turn 'true' or 'false' into Boolean values? Isn't that an
ASP.Net problem, not a SQL Server problem?
|||On Mon, 25 Jun 2007 04:44:18 -0700, royend wrote:

>This really does not solve my problem, as it returns a string and not
>a Boolean value.
Hi royend,
Unfortunately, SQL Server doesn't support boolean values. Quite logical,
if you consider that all predicate logic in an RDBMS is three-valued,
whereas boolean algebra is two-valued. Put a PITA in some situations
nonetheless.
The closest SQL Server has to offer would be bit. Something like
CASE WHEN chkName = 1 THEN CAST(1 as bit) ELSE CAST(0 AS bit) END
But then you'd have to rely on the conversion from bit to boolean by ASP
treating 1 as true and 0 as false - frankly, I'd rather use code that
returns a string 'True' or 'False' (maybe shortened to 'T' and 'F') and
convert it with explicit ASP code then to rely on the implicit
conversion from SQL bit to ASP bool (that is, as far as I know,
undocumented) doing what I hope it does, and still doing it next year.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Check if column (int) is true or false (bool)

Is there some way I may "convert" an int column to boolean with the
SQL query? I have a database with an int column containing only 0s and
1s - using it as a Boolean. Now, I need the Boolean values false and
true in order to use the .net checkbox.
I have tried the following:
SELECT userID, (chkName = 1) AS bChkName, name
FROM User
When chkName = 1 it will return true, and vice versa when it equals 0.
This should produce something like this:
userID bChkName name
2 true Peter
3 true Linda
4 false John Doe
according to the int in chkName.
This of course didn't work and now I am hoping for your help... do you
know of any solution?
royend.royend
there is no direct way to do this
select userID, case when chkName =1 then 'true' else 'false',name from user
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"royend" <royend@.gmail.com> wrote in message
news:1182768635.843541.88960@.c77g2000hse.googlegroups.com...
> Is there some way I may "convert" an int column to boolean with the
> SQL query? I have a database with an int column containing only 0s and
> 1s - using it as a Boolean. Now, I need the Boolean values false and
> true in order to use the .net checkbox.
> I have tried the following:
> SELECT userID, (chkName = 1) AS bChkName, name
> FROM User
> When chkName = 1 it will return true, and vice versa when it equals 0.
> This should produce something like this:
> userID bChkName name
> 2 true Peter
> 3 true Linda
> 4 false John Doe
> according to the int in chkName.
> This of course didn't work and now I am hoping for your help... do you
> know of any solution?
> royend.
>|||This really does not solve my problem, as it returns a string and not
a Boolean value.
The asp:checkbox requires a boolean in order to become checked or
unchecked.
In worst case scenario I'll have to code inside some of Visual's
predefined and automatically added code...
Still, I am a bit surprised that SQL cannot return a bool value for
one of its column based on a simple test.
Thanks for your answer though.
royend
On 25 Jun, 13:02, "vt" <vinu.t.1...@.gmail.com> wrote:
> royend
> there is no direct way to do this
> select userID, case when chkName =1 then 'true' else 'false',name from user
> Regards
> VT
> Knowledge is power, share it...http://oneplace4sql.blogspot.com/"royend" <roy...@.gmail.com> wrote in message
> news:1182768635.843541.88960@.c77g2000hse.googlegroups.com...
>
> > Is there some way I may "convert" an int column to boolean with the
> > SQL query? I have a database with an int column containing only 0s and
> > 1s - using it as a Boolean. Now, I need the Boolean values false and
> > true in order to use the .net checkbox.
> > I have tried the following:
> > SELECT userID, (chkName = 1) AS bChkName, name
> > FROM User
> > When chkName = 1 it will return true, and vice versa when it equals 0.
> > This should produce something like this:
> > userID bChkName name
> > 2 true Peter
> > 3 true Linda
> > 4 false John Doe
> > according to the int in chkName.
> > This of course didn't work and now I am hoping for your help... do you
> > know of any solution?
> > royend.- Skjul sitert tekst -
> - Vis sitert tekst -|||Try this...
select userID, convert(bit,chkName) as BoolName,name from user|||> This really does not solve my problem, as it returns a string and not
> a Boolean value.
And ASP.Net can't turn 'true' or 'false' into Boolean values? Isn't that an
ASP.Net problem, not a SQL Server problem?|||On Mon, 25 Jun 2007 04:44:18 -0700, royend wrote:
>This really does not solve my problem, as it returns a string and not
>a Boolean value.
Hi royend,
Unfortunately, SQL Server doesn't support boolean values. Quite logical,
if you consider that all predicate logic in an RDBMS is three-valued,
whereas boolean algebra is two-valued. Put a PITA in some situations
nonetheless.
The closest SQL Server has to offer would be bit. Something like
CASE WHEN chkName = 1 THEN CAST(1 as bit) ELSE CAST(0 AS bit) END
But then you'd have to rely on the conversion from bit to boolean by ASP
treating 1 as true and 0 as false - frankly, I'd rather use code that
returns a string 'True' or 'False' (maybe shortened to 'T' and 'F') and
convert it with explicit ASP code then to rely on the implicit
conversion from SQL bit to ASP bool (that is, as far as I know,
undocumented) doing what I hope it does, and still doing it next year.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelissql

Check if column (int) is true or false (bool)

Is there some way I may "convert" an int column to boolean with the
SQL query? I have a database with an int column containing only 0s and
1s - using it as a Boolean. Now, I need the Boolean values false and
true in order to use the .net checkbox.
I have tried the following:
SELECT userID, (chkName = 1) AS bChkName, name
FROM User
When chkName = 1 it will return true, and vice versa when it equals 0.
This should produce something like this:
userID bChkName name
2 true Peter
3 true Linda
4 false John Doe
according to the int in chkName.
This of course didn't work and now I am hoping for your help... do you
know of any solution?
royend.royend
there is no direct way to do this
select userID, case when chkName =1 then 'true' else 'false',name from user
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"royend" <royend@.gmail.com> wrote in message
news:1182768635.843541.88960@.c77g2000hse.googlegroups.com...
> Is there some way I may "convert" an int column to boolean with the
> SQL query? I have a database with an int column containing only 0s and
> 1s - using it as a Boolean. Now, I need the Boolean values false and
> true in order to use the .net checkbox.
> I have tried the following:
> SELECT userID, (chkName = 1) AS bChkName, name
> FROM User
> When chkName = 1 it will return true, and vice versa when it equals 0.
> This should produce something like this:
> userID bChkName name
> 2 true Peter
> 3 true Linda
> 4 false John Doe
> according to the int in chkName.
> This of course didn't work and now I am hoping for your help... do you
> know of any solution?
> royend.
>|||This really does not solve my problem, as it returns a string and not
a Boolean value.
The asp:checkbox requires a boolean in order to become checked or
unchecked.
In worst case scenario I'll have to code inside some of Visual's
predefined and automatically added code...
Still, I am a bit surprised that SQL cannot return a bool value for
one of its column based on a simple test.
Thanks for your answer though.
royend
On 25 Jun, 13:02, "vt" <vinu.t.1...@.gmail.com> wrote:
> royend
> there is no direct way to do this
> select userID, case when chkName =1 then 'true' else 'false',name from us
er
> Regards
> VT
> Knowledge is power, share it...http://oneplace4sql.blogspot.com/"royend"
<roy...@.gmail.com> wrote in message
> news:1182768635.843541.88960@.c77g2000hse.googlegroups.com...
>
>
>
>
>
>
> - Vis sitert tekst -|||Try this...
select userID, convert(bit,chkName) as BoolName,name from user|||> This really does not solve my problem, as it returns a string and not
> a Boolean value.
And ASP.Net can't turn 'true' or 'false' into Boolean values? Isn't that an
ASP.Net problem, not a SQL Server problem?

chart x-axis y-axis

I'm trying to convert an old chart to Reporting Services.
We have used the same dataset for years, it is very very simple. But it seems to me that Reporting Services can't deal with this simple dataset.

My question is: How can I get create a line chart using this dataset.

The data set consists of 1 row with 96 columns (a day divided into 96 sets of 15 minutes). Each column records how many events occurs within the 15 minutes. Along the x axis I would like to display a vertical line every 4 hours. The y axis should be numbered 0 to the highest value in the results.

I could not make this dataset any more simple than it is. But Reporting Services just chokes on this.

Here is a sample of the dataset :
1 2 0 4 5 6 7 8 9 10... 96It looks as thought what I may need is to reset the dataset to look like this:
time events
00-14 1
15-29 2
30-44 0
...

The only way I could do that with the existing tables would be 96 unions.

96 Unions!
Microsoft I must say that I am not so impressed with reporting services. Dragging fields to a myesterous chart "category" or "series" was dumb to begin with.

chart label mapping to strings

How can I convert chart axis labels to strings. For example instead of
showing 1, 2, 3.. on x axis, how can I show gold, silver etc? Thanks.You can either add a CASE statement to the SQL query and use that for
your label field, or you can add a calculated field to the dataset.
-Josh
bhanoji wrote:
> How can I convert chart axis labels to strings. For example instead of
> showing 1, 2, 3.. on x axis, how can I show gold, silver etc? Thanks.|||I can't create a case statement for label field because if the value is 1 I
say gold and 2 will be silver etc. However if the data is 1.5 I can't map
that to a string. Thanks.
"Josh" wrote:
> You can either add a CASE statement to the SQL query and use that for
> your label field, or you can add a calculated field to the dataset.
> -Josh
> bhanoji wrote:
> > How can I convert chart axis labels to strings. For example instead of
> > showing 1, 2, 3.. on x axis, how can I show gold, silver etc? Thanks.
>|||What do you want it to display if the value is 1.5? Nothing? The actual
value?
-Josh
bhanoji wrote:
> I can't create a case statement for label field because if the value is 1 I
> say gold and 2 will be silver etc. However if the data is 1.5 I can't map
> that to a string. Thanks.
> "Josh" wrote:
> >
> > You can either add a CASE statement to the SQL query and use that for
> > your label field, or you can add a calculated field to the dataset.
> >
> > -Josh
> >
> > bhanoji wrote:
> > > How can I convert chart axis labels to strings. For example instead of
> > > showing 1, 2, 3.. on x axis, how can I show gold, silver etc? Thanks.
> >
> >|||I have the following data
City Speakers
A 8
B 15
C 3
D 7
The graph is showing A, B, C, D on X axis and 5, 10, 15 on Y axis as major
interval is 5. How can I show Bronze, Silver, Gold instead of Y axis labels
5, 10, 15.
Thanks Josh.
"Josh" wrote:
> What do you want it to display if the value is 1.5? Nothing? The actual
> value?
> -Josh
>
> bhanoji wrote:
> > I can't create a case statement for label field because if the value is 1 I
> > say gold and 2 will be silver etc. However if the data is 1.5 I can't map
> > that to a string. Thanks.
> >
> > "Josh" wrote:
> >
> > >
> > > You can either add a CASE statement to the SQL query and use that for
> > > your label field, or you can add a calculated field to the dataset.
> > >
> > > -Josh
> > >
> > > bhanoji wrote:
> > > > How can I convert chart axis labels to strings. For example instead of
> > > > showing 1, 2, 3.. on x axis, how can I show gold, silver etc? Thanks.
> > >
> > >
>|||I found another thread that discusses this. Check it out at:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/1a7ac279edf86082/c8207d4c72f15e26?lnk=st&q=%22values+on+y-axis%22&rnum=1#c8207d4c72f15e26
-Josh
bhanoji wrote:
> I have the following data
> City Speakers
> A 8
> B 15
> C 3
> D 7
> The graph is showing A, B, C, D on X axis and 5, 10, 15 on Y axis as major
> interval is 5. How can I show Bronze, Silver, Gold instead of Y axis labels
> 5, 10, 15.
> Thanks Josh.
>
> "Josh" wrote:
> >
> > What do you want it to display if the value is 1.5? Nothing? The actual
> > value?
> >
> > -Josh
> >
> >
> > bhanoji wrote:
> > > I can't create a case statement for label field because if the value is 1 I
> > > say gold and 2 will be silver etc. However if the data is 1.5 I can't map
> > > that to a string. Thanks.
> > >
> > > "Josh" wrote:
> > >
> > > >
> > > > You can either add a CASE statement to the SQL query and use that for
> > > > your label field, or you can add a calculated field to the dataset.
> > > >
> > > > -Josh
> > > >
> > > > bhanoji wrote:
> > > > > How can I convert chart axis labels to strings. For example instead of
> > > > > showing 1, 2, 3.. on x axis, how can I show gold, silver etc? Thanks.
> > > >
> > > >
> >
> >|||In Y axis tab of the chart, I checked show label and tried to put an
expression in the format code box.
expression in formatcode does not work. expression
"=iif(fields!abc.value=10, "gold",
iif(fields!abc.value=20, "bronze", "silver"))" generates the following error
Report item expressions can only refer to fields within the current data
scope or,
if inside an aggregate, the specified data set scope. Thanks.
"Josh" wrote:
> I found another thread that discusses this. Check it out at:
> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/1a7ac279edf86082/c8207d4c72f15e26?lnk=st&q=%22values+on+y-axis%22&rnum=1#c8207d4c72f15e26
> -Josh
>
> bhanoji wrote:
> > I have the following data
> > City Speakers
> > A 8
> > B 15
> > C 3
> > D 7
> >
> > The graph is showing A, B, C, D on X axis and 5, 10, 15 on Y axis as major
> > interval is 5. How can I show Bronze, Silver, Gold instead of Y axis labels
> > 5, 10, 15.
> >
> > Thanks Josh.
> >
> >
> >
> > "Josh" wrote:
> >
> > >
> > > What do you want it to display if the value is 1.5? Nothing? The actual
> > > value?
> > >
> > > -Josh
> > >
> > >
> > > bhanoji wrote:
> > > > I can't create a case statement for label field because if the value is 1 I
> > > > say gold and 2 will be silver etc. However if the data is 1.5 I can't map
> > > > that to a string. Thanks.
> > > >
> > > > "Josh" wrote:
> > > >
> > > > >
> > > > > You can either add a CASE statement to the SQL query and use that for
> > > > > your label field, or you can add a calculated field to the dataset.
> > > > >
> > > > > -Josh
> > > > >
> > > > > bhanoji wrote:
> > > > > > How can I convert chart axis labels to strings. For example instead of
> > > > > > showing 1, 2, 3.. on x axis, how can I show gold, silver etc? Thanks.
> > > > >
> > > > >
> > >
> > >
>

Char to Bit

I am importing a table where I need to convert a char(1) with the
values of 't' or 'f' into a bit field with valies of 1 or 0. Is there
a built-in function that does that? I've been searching, but I can't
find an answer.On 15 Apr 2005 14:25:24 -0700, imani_technology_spam@.yahoo.com wrote:

>I am importing a table where I need to convert a char(1) with the
>values of 't' or 'f' into a bit field with valies of 1 or 0. Is there
>a built-in function that does that? I've been searching, but I can't
>find an answer.

Hi imani,

The best answer is to store it as a CHAR(1) column with values 't' and
'f' and to forget aboout converting to BIT - what do you expect to gain
from it?

The second best answer is to use a CASE expression.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Do not use bits in SQL. You cannot use them as Booleans in other
Microsoft host languages. They are proprietary. And a good SQL
porgrammer does not write with flags anyway. Do some searching about
bits for the details.|||I don't have a choice in the matter. I have been told to convert a
char(1) to a bit. So what is the best way to do it within a UDF?

--CELKO-- wrote:
> Do not use bits in SQL. You cannot use them as Booleans in other
> Microsoft host languages. They are proprietary. And a good SQL
> porgrammer does not write with flags anyway. Do some searching about
> bits for the details.|||The #2 answer is a CASE expression with CAST() functions to be safe.
You will need to document that for the next guy because this is suicide
and you do not want to be blamed for it.

Did you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
consistently within Microsoft's own proprietary languages? You have to
know what the host language will be to do the mapping from Boolean to
bits. Once that decision is made you cannot use another incompatible
host language. And watch out for CLR later.|||Thanks for the info. I am aware that MS isn't consistent with their
own Booleans. However, I'm the new guy on a very large team, so I have
to deter to them on that issue. Also, I didn't know you could CAST
from char(1) to a bit. I thought the two data types were incompatible.|||On 17 Apr 2005 09:33:44 -0700, imani_technology_spam@.yahoo.com wrote:

>Thanks for the info. I am aware that MS isn't consistent with their
>own Booleans. However, I'm the new guy on a very large team, so I have
>to deter to them on that issue. Also, I didn't know you could CAST
>from char(1) to a bit. I thought the two data types were incompatible.

Hi imani,

You're right, you can't just CAST a char(1) to bit, unless the char(1)
holds only '0' and '1' - and even for that case, I'd run a test before
betting any money on it :-)

That's why both Joe (Celko) and I (in my previous reply in this thread)
suggest using a CASE. Joe's suggestion to *ALSO* use a CAST is actually
quite good - not really needed in SQL Server, but it better documents
what you're doing:

CASE WHEN CharColumn = 't' THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Char to Base64 conversion

Hello. Anyone know a way to convert a char string into
BASE64? I'm loading some data to Active Directory from
SQL Server using an LDIF script and since there are
carriage returns imbedded, I BELEIVE the only way to do
this is BASE64 encoding. We wrote a function that loops
thru every character and converts it, but when doing a
large select, that encoding function takes too long for
each row.
INPUT CHAR STRING = "Hello World"
OUTPUT BASE64 STRING = "SGVsbG8gV29ybGQ="
Any ideas or hidden functions that do this conversion in
SQL Server? THanks, BruceCheckout the "FOR XML, BINARY BASE64" of the Select statement and see if it
helps you!
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:427201c49029$2a59bec0$a301280a@.phx.gbl...
> Hello. Anyone know a way to convert a char string into
> BASE64? I'm loading some data to Active Directory from
> SQL Server using an LDIF script and since there are
> carriage returns imbedded, I BELEIVE the only way to do
> this is BASE64 encoding. We wrote a function that loops
> thru every character and converts it, but when doing a
> large select, that encoding function takes too long for
> each row.
> INPUT CHAR STRING = "Hello World"
> OUTPUT BASE64 STRING = "SGVsbG8gV29ybGQ="
> Any ideas or hidden functions that do this conversion in
> SQL Server? THanks, Bruce|||wow, NICE!!! I've been doing some reading on that, and it
doesn't appear that it will convert a CHAR type field into
BINARY BASE64 but if you do the convert something like
this below, then you get it back as BASE64, very cool!
BUT, I'd like to strip the XML tags off, probably a
command to get it with just the straight BASE64 code?
Thanks a LOT, that was a great help!!
select top 1 convert(varbinary(8000),name) as 'name_binary'
from sysobjects
for XML RAW, BINARY BASE64
Thanks, Bruce
>--Original Message--
>Checkout the "FOR XML, BINARY BASE64" of the Select
statement and see if it
>helps you!
>
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:427201c49029$2a59bec0$a301280a@.phx.gbl...
>> Hello. Anyone know a way to convert a char string into
>> BASE64? I'm loading some data to Active Directory from
>> SQL Server using an LDIF script and since there are
>> carriage returns imbedded, I BELEIVE the only way to do
>> this is BASE64 encoding. We wrote a function that loops
>> thru every character and converts it, but when doing a
>> large select, that encoding function takes too long for
>> each row.
>> INPUT CHAR STRING = "Hello World"
>> OUTPUT BASE64 STRING = "SGVsbG8gV29ybGQ="
>> Any ideas or hidden functions that do this conversion in
>> SQL Server? THanks, Bruce
>
>.
>|||Hi Harman, Hi Bruce,
I am currently running into the same problem. I can convert it using the
technique you already mentione, but I can not put it into a variable to
strip off the XML-tags.
Do you have an idea how this could be done?
I tried to select it into a variable (not allowed) and tried to get it tino
a temp-table without success.
Maybe you could be so kind and give me a hint on that ;)
Thanks a lot in advance,
Andreas Bretl
andreas.bretl@.brainlab.com|||Andy, yes, the XML method was sooooooooo close... but no
cigar for the SAME reasons you mentioned! Amazing those
points you found that we did (and a zillion other did
also!). Maybe MS will fix that in the next release...
ANYWAY....... We ended up writing a SQL function, that
went thru the not-so-easy task of converting every
character to BASE64. There may be a better way with the
XML option, but we were ok with the function method for
our purposes. It's also pretty amazing that we have to
jump thru these hoops to stick a carriage return inside
an address block of lines, to send to AD, via an LDIF
script. BASE64 doesn't make for readable code at all,
strange that was needed, but it's working fine after we
figured out all the klooge... Bruce
>--Original Message--
>Hi Harman, Hi Bruce,
>I am currently running into the same problem. I can
convert it using the
>technique you already mentione, but I can not put it
into a variable to
>strip off the XML-tags.
>Do you have an idea how this could be done?
>I tried to select it into a variable (not allowed) and
tried to get it tino
>a temp-table without success.
>Maybe you could be so kind and give me a hint on that ;)
>Thanks a lot in advance,
>Andreas Bretl
>andreas.bretl@.brainlab.com
>
>.
>|||Hi Bruce,
I totally agree, that one was really close at all ;)
I ended up in a COM-DLL I installed on the server where I implemented the
Base64 algo. called via sp_OAMethod.
It works fine but it is strange that such a "simple" thing causes so much
workaround.
Do you think there is a chance to see how you handled it with the SQL
function?
I totally understand if you have to keep it "secret" but I just thought
asking could't harm anybody ;)
Seize the Day
Andy
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:114301c4a5c0$23054bb0$a601280a@.phx.gbl...
> Andy, yes, the XML method was sooooooooo close... but no
> cigar for the SAME reasons you mentioned! Amazing those
> points you found that we did (and a zillion other did
> also!). Maybe MS will fix that in the next release...
> ANYWAY....... We ended up writing a SQL function, that
> went thru the not-so-easy task of converting every
> character to BASE64. There may be a better way with the
> XML option, but we were ok with the function method for
> our purposes. It's also pretty amazing that we have to
> jump thru these hoops to stick a carriage return inside
> an address block of lines, to send to AD, via an LDIF
> script. BASE64 doesn't make for readable code at all,
> strange that was needed, but it's working fine after we
> figured out all the klooge... Bruce
> > >

Char to Base64 conversion

Hello. Anyone know a way to convert a char string into
BASE64? I'm loading some data to Active Directory from
SQL Server using an LDIF script and since there are
carriage returns imbedded, I BELEIVE the only way to do
this is BASE64 encoding. We wrote a function that loops
thru every character and converts it, but when doing a
large select, that encoding function takes too long for
each row.
INPUT CHAR STRING = "Hello World"
OUTPUT BASE64 STRING = "SGVsbG8gV29ybGQ="
Any ideas or hidden functions that do this conversion in
SQL Server? THanks, BruceCheckout the "FOR XML, BINARY BASE64" of the Select statement and see if it
helps you!
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:427201c49029$2a59bec0$a301280a@.phx.gbl...
> Hello. Anyone know a way to convert a char string into
> BASE64? I'm loading some data to Active Directory from
> SQL Server using an LDIF script and since there are
> carriage returns imbedded, I BELEIVE the only way to do
> this is BASE64 encoding. We wrote a function that loops
> thru every character and converts it, but when doing a
> large select, that encoding function takes too long for
> each row.
> INPUT CHAR STRING = "Hello World"
> OUTPUT BASE64 STRING = "SGVsbG8gV29ybGQ="
> Any ideas or hidden functions that do this conversion in
> SQL Server? THanks, Bruce|||wow, NICE!!! I've been doing some reading on that, and it
doesn't appear that it will convert a CHAR type field into
BINARY BASE64 but if you do the convert something like
this below, then you get it back as BASE64, very cool!
BUT, I'd like to strip the XML tags off, probably a
command to get it with just the straight BASE64 code?
Thanks a LOT, that was a great help!!
select top 1 convert(varbinary(8000),name) as 'name_binary'
from sysobjects
for XML RAW, BINARY BASE64
Thanks, Bruce

>--Original Message--
>Checkout the "FOR XML, BINARY BASE64" of the Select
statement and see if it
>helps you!
>
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:427201c49029$2a59bec0$a301280a@.phx.gbl...
>
>.
>|||Hi Harman, Hi Bruce,
I am currently running into the same problem. I can convert it using the
technique you already mentione, but I can not put it into a variable to
strip off the XML-tags.
Do you have an idea how this could be done?
I tried to select it into a variable (not allowed) and tried to get it tino
a temp-table without success.
Maybe you could be so kind and give me a hint on that ;)
Thanks a lot in advance,
Andreas Bretl
andreas.bretl@.brainlab.com|||Andy, yes, the XML method was sooooooooo close... but no
cigar for the SAME reasons you mentioned! Amazing those
points you found that we did (and a zillion other did
also!). Maybe MS will fix that in the next release...
ANYWAY....... We ended up writing a SQL function, that
went thru the not-so-easy task of converting every
character to BASE64. There may be a better way with the
XML option, but we were ok with the function method for
our purposes. It's also pretty amazing that we have to
jump thru these hoops to stick a carriage return inside
an address block of lines, to send to AD, via an LDIF
script. BASE64 doesn't make for readable code at all,
strange that was needed, but it's working fine after we
figured out all the klooge... Bruce

>--Original Message--
>Hi Harman, Hi Bruce,
>I am currently running into the same problem. I can
convert it using the
>technique you already mentione, but I can not put it
into a variable to
>strip off the XML-tags.
>Do you have an idea how this could be done?
>I tried to select it into a variable (not allowed) and
tried to get it tino
>a temp-table without success.
>Maybe you could be so kind and give me a hint on that ;)
>Thanks a lot in advance,
>Andreas Bretl
>andreas.bretl@.brainlab.com
>
>.
>|||Hi Bruce,
I totally agree, that one was really close at all ;)
I ended up in a COM-DLL I installed on the server where I implemented the
Base64 algo. called via sp_OAMethod.
It works fine but it is strange that such a "simple" thing causes so much
workaround.
Do you think there is a chance to see how you handled it with the SQL
function?
I totally understand if you have to keep it "secret" but I just thought
asking could't harm anybody ;)
Seize the Day
Andy
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:114301c4a5c0$23054bb0$a601280a@.phx.gbl...[vbcol=seagreen]
> Andy, yes, the XML method was sooooooooo close... but no
> cigar for the SAME reasons you mentioned! Amazing those
> points you found that we did (and a zillion other did
> also!). Maybe MS will fix that in the next release...
> ANYWAY....... We ended up writing a SQL function, that
> went thru the not-so-easy task of converting every
> character to BASE64. There may be a better way with the
> XML option, but we were ok with the function method for
> our purposes. It's also pretty amazing that we have to
> jump thru these hoops to stick a carriage return inside
> an address block of lines, to send to AD, via an LDIF
> script. BASE64 doesn't make for readable code at all,
> strange that was needed, but it's working fine after we
> figured out all the klooge... Bruce

Char to Base64 conversion

Hello. Anyone know a way to convert a char string into
BASE64? I'm loading some data to Active Directory from
SQL Server using an LDIF script and since there are
carriage returns imbedded, I BELEIVE the only way to do
this is BASE64 encoding. We wrote a function that loops
thru every character and converts it, but when doing a
large select, that encoding function takes too long for
each row.
INPUT CHAR STRING = "Hello World"
OUTPUT BASE64 STRING = "SGVsbG8gV29ybGQ="
Any ideas or hidden functions that do this conversion in
SQL Server? THanks, Bruce
Checkout the "FOR XML, BINARY BASE64" of the Select statement and see if it
helps you!
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:427201c49029$2a59bec0$a301280a@.phx.gbl...
> Hello. Anyone know a way to convert a char string into
> BASE64? I'm loading some data to Active Directory from
> SQL Server using an LDIF script and since there are
> carriage returns imbedded, I BELEIVE the only way to do
> this is BASE64 encoding. We wrote a function that loops
> thru every character and converts it, but when doing a
> large select, that encoding function takes too long for
> each row.
> INPUT CHAR STRING = "Hello World"
> OUTPUT BASE64 STRING = "SGVsbG8gV29ybGQ="
> Any ideas or hidden functions that do this conversion in
> SQL Server? THanks, Bruce
|||wow, NICE!!! I've been doing some reading on that, and it
doesn't appear that it will convert a CHAR type field into
BINARY BASE64 but if you do the convert something like
this below, then you get it back as BASE64, very cool!
BUT, I'd like to strip the XML tags off, probably a
command to get it with just the straight BASE64 code?
Thanks a LOT, that was a great help!!
select top 1 convert(varbinary(8000),name) as 'name_binary'
from sysobjects
for XML RAW, BINARY BASE64
Thanks, Bruce

>--Original Message--
>Checkout the "FOR XML, BINARY BASE64" of the Select
statement and see if it
>helps you!
>
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:427201c49029$2a59bec0$a301280a@.phx.gbl...
>
>.
>
|||Hi Harman, Hi Bruce,
I am currently running into the same problem. I can convert it using the
technique you already mentione, but I can not put it into a variable to
strip off the XML-tags.
Do you have an idea how this could be done?
I tried to select it into a variable (not allowed) and tried to get it tino
a temp-table without success.
Maybe you could be so kind and give me a hint on that ;)
Thanks a lot in advance,
Andreas Bretl
andreas.bretl@.brainlab.com
|||Andy, yes, the XML method was sooooooooo close... but no
cigar for the SAME reasons you mentioned! Amazing those
points you found that we did (and a zillion other did
also!). Maybe MS will fix that in the next release...
ANYWAY....... We ended up writing a SQL function, that
went thru the not-so-easy task of converting every
character to BASE64. There may be a better way with the
XML option, but we were ok with the function method for
our purposes. It's also pretty amazing that we have to
jump thru these hoops to stick a carriage return inside
an address block of lines, to send to AD, via an LDIF
script. BASE64 doesn't make for readable code at all,
strange that was needed, but it's working fine after we
figured out all the klooge... Bruce

>--Original Message--
>Hi Harman, Hi Bruce,
>I am currently running into the same problem. I can
convert it using the
>technique you already mentione, but I can not put it
into a variable to
>strip off the XML-tags.
>Do you have an idea how this could be done?
>I tried to select it into a variable (not allowed) and
tried to get it tino
>a temp-table without success.
>Maybe you could be so kind and give me a hint on that ;)
>Thanks a lot in advance,
>Andreas Bretl
>andreas.bretl@.brainlab.com
>
>.
>
|||Hi Bruce,
I totally agree, that one was really close at all ;)
I ended up in a COM-DLL I installed on the server where I implemented the
Base64 algo. called via sp_OAMethod.
It works fine but it is strange that such a "simple" thing causes so much
workaround.
Do you think there is a chance to see how you handled it with the SQL
function?
I totally understand if you have to keep it "secret" but I just thought
asking could't harm anybody ;)
Seize the Day
Andy
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:114301c4a5c0$23054bb0$a601280a@.phx.gbl...[vbcol=seagreen]
> Andy, yes, the XML method was sooooooooo close... but no
> cigar for the SAME reasons you mentioned! Amazing those
> points you found that we did (and a zillion other did
> also!). Maybe MS will fix that in the next release...
> ANYWAY....... We ended up writing a SQL function, that
> went thru the not-so-easy task of converting every
> character to BASE64. There may be a better way with the
> XML option, but we were ok with the function method for
> our purposes. It's also pretty amazing that we have to
> jump thru these hoops to stick a carriage return inside
> an address block of lines, to send to AD, via an LDIF
> script. BASE64 doesn't make for readable code at all,
> strange that was needed, but it's working fine after we
> figured out all the klooge... Bruce

changing timeframes with stock data

I have stock data in 1 min intervals and would like to convert it into other timeframes (e.g., 10 min, daily, monthly).

Here's is some sample data and my final goal:

[DateTime] [Open] [High] [Low] [Close] [Volume]
10-Feb-05 12:10:00 3.88 3.88 3.87 3.87 10
10-Feb-05 12:11:00 3.87 3.87 3.87 3.87 2
10-Feb-05 12:12:00 3.86 3.86 3.86 3.86 1
10-Feb-05 12:13:00 3.85 3.87 3.84 3.85 23
10-Feb-05 12:14:00 3.85 3.85 3.85 3.85 6
10-Feb-05 12:15:00 3.86 3.86 3.86 3.86 1
10-Feb-05 12:16:00 3.85 3.85 3.85 3.85 1
10-Feb-05 12:18:00 3.85 3.85 3.85 3.85 3
10-Feb-05 12:19:00 3.85 3.85 3.85 3.85 3

[DateTime] [Open] [High] [Low] [Close] [Volume]
10-Feb-05 12:10:00 3.88 3.88 3.84 3.85 50 *

*sum

Assuming your intervals will never span midnight,
you can do this by joining to an intervals table. This
has all minutes in one day with any intervals you want
and can be set up like this

CREATE TABLE MinuteIntervals(Mins INT NOT NULL PRIMARY KEY,
HMS CHAR(8) NOT NULL,
Interval10Mins INT NOT NULL,
Interval1Hour INT NOT NULL)

DECLARE @.i INT
SET @.i=0
WHILE @.i < 24*60
BEGIN
INSERT INTO MinuteIntervals(Mins,HMS,Interval10Mins,Interval1Hour)
SELECT @.i, CONVERT(CHAR(8),DATEADD(minute,@.i,'19000101'),108),
@.i/10, @.i/60

SET @.i=@.i+1
END

Now you need to join to this to your stock table
grouping by whatever interval you want.

SELECT MIN(t.[DateTime]) AS [DateTime],
(SELECT t2.[Open]
FROM stocktable t2
WHERE t2.[DateTime]=MIN(t.[DateTime])) AS [Open],
MAX(t.[High]) AS [High],
MIN(t.[Low]) AS [Low],
(SELECT t2.[Open]
FROM stocktable t2
WHERE t2.[DateTime]=MAX(t.[DateTime])) AS [Close],
SUM(t.[Volume]) AS [Volume]
FROM stocktable t
INNER JOIN MinuteIntervals m ON m.HMS=CONVERT(CHAR(8),t.[DateTime],108)
GROUP BY m.Interval10Mins

|||

Correction, the group by should read

GROUP BY m.Interval10Mins,CONVERT(CHAR(8),t.[DateTime],112)

|||

Of course this will only work for intervals less that one day.
Should have read the question fully!

For daily or longer intervals, don't join
to the MinuteIntervals table, simply group by the relevant amount
e.g. for daily

SELECT MIN(t.[DateTime]) AS [DateTime],
(SELECT t2.[Open]
FROM stocktable t2
WHERE t2.[DateTime]=MIN(t.[DateTime])) AS [Open],
MAX(t.[High]) AS [High],
MIN(t.[Low]) AS [Low],
(SELECT t2.[Close]
FROM stocktable t2
WHERE t2.[DateTime]=MAX(t.[DateTime])) AS [Close],
SUM(t.[Volume]) AS [Volume]
FROM stocktable t
GROUP BY CONVERT(CHAR(8),t.[DateTime],112)

For monthly

GROUP BY LEFT(CONVERT(CHAR(8),t.[DateTime],112),6)

For yearly

GROUP BY LEFT(CONVERT(CHAR(8),t.[DateTime],112),4)

|||

I very much appreciate your help. If I understand the roles of tables t and t2, it appears the code only works if two different tables are used.

Does that mean I have to first create a copy of the original table in order to run the script correctly?

|||Those are not tables they are just aliases for tables. Since you are joining with the same table you need to use table aliases to make the names unique for referencing columns in the query. See the SELECT statement topic in Books Online for the syntax details.|||

Yes, you are so right . That's what I get for trying to cut back on my caffeine.

It works like a charm . Thanks to all who have helped. I really do appreciate it.

changing time format

I have a datetime field that looks like this:
2004-04-15 09:31:37.000
I used the convert function (convert(char(8),executiontime,108)) to produce this:
09:31:37
How do I make the result return:
093137
Thanks
You could use REPLACE()
select CONVERT(char(8),REPLACE(convert(char(8),executiont ime,108), ':', =
'')) from foo
--=20
Keith
"Chambers" <anonymous@.discussions.microsoft.com> wrote in message =
news:4041CE38-C011-4CB7-BBBC-C651870B1C37@.microsoft.com...
>=20
>=20
> I have a datetime field that looks like this:
>=20
> 2004-04-15 09:31:37.000
>=20
> I used the convert function (convert(char(8),executiontime,108)) to =
produce this:
>=20
> 09:31:37
>=20
> How do I make the result return:
>=20
> 093137
>=20
> Thanks

changing time format

I have a datetime field that looks like this:
2004-04-15 09:31:37.000
I used the convert function (convert(char(8),executiontime,108)) to produce
this:
09:31:37
How do I make the result return:
093137
ThanksYou could use REPLACE()
select CONVERT(char(8),REPLACE(convert(char(8),
executiontime,108), ':', =
'')) from foo
--=20
Keith
"Chambers" <anonymous@.discussions.microsoft.com> wrote in message =
news:4041CE38-C011-4CB7-BBBC-C651870B1C37@.microsoft.com...
>=20
>=20
> I have a datetime field that looks like this:
>=20
> 2004-04-15 09:31:37.000
>=20
> I used the convert function (convert(char(8),executiontime,108)) to =
produce this:
>=20
> 09:31:37
>=20
> How do I make the result return:
>=20
> 093137
>=20
> Thanks