Change in legend of chart based on values?

I am developing a chart with type as column and subtype as stacked. The values (different columns from my dataset) are shown as series in the chart. If I have an entire column with no values, nothing in shown in the graph but the column name comes in the legend. I do not want to show the particular column in legend if all the values in that column are 0 or null. Is it possible through an expression or any other way?

Please let me know.

Thanks in advance !!!

If you use a dynamic series grouping in the chart and you want to get rid of a particular series grouping instance, you could use a filter expression on the series grouping, e.g.
Filter expression: =Sum(Fields!Name.Value, "DynamicSeriesGroupingName")
Filter operator: >
Filter value: =0

Again, the filter approach will only work in the case of dynamic series groupings.

-- Robert

change default value of column

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

Quote:

Originally Posted by sourabhmca

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

try doing an ALTER TABLE|||I tried but it was not working|||

Quote:

Originally Posted by sourabhmca

I tried but it was not working

if this is a one time thing, try creating a new field then drop the existing one.|||oh yeah, you have to rename the field to the old field name after dropping the old one.

IF this is a one time thing...|||

Quote:

Originally Posted by sourabhmca

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

Hey try like this...

create table abc (a int,b smalldatetime constraint df_abc_b default getdate())
insert into abc(a) values(1)
select * from abc

alter table abc drop constraint df_abc_b
alter table abc add constraint df_abc_b default(getdate()+0.435) for b
insert into abc(a) values(1)
select * from abc

Change default value

I need to change current default values from df_curr_user to df_login_user
for about 600 differrent fileds in different tables. Should I do it by using
sp_unbindefault and sp_bindefault for each column? How can I do it
programmatically?
Thanks in advance for any help!
PerayuAre you talking about changing the default constraint name?
AMB
"Perayu" wrote:

> I need to change current default values from df_curr_user to df_login_user
> for about 600 differrent fileds in different tables. Should I do it by usi
ng
> sp_unbindefault and sp_bindefault for each column? How can I do it
> programmatically?
> Thanks in advance for any help!
>
> Perayu
>
>|||Not only the default constraint name. I want to replace it to a new one.
Actually, I can't change the name or update current used df_curr_user
because it is bound to columns. So, I defined a new one as df_login_user and
try to replace it. But have no idea how to do it programmatically.
Thanks.
Perayu
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:654DA86C-7C7B-4DBF-8DCD-99599075D685@.microsoft.com...
> Are you talking about changing the default constraint name?
>
> AMB
> "Perayu" wrote:
>|||See if this helps.
use northwind
go
create default df_current_user as current_user
go
create default df_login_user as suser_sname()
go
create table t1 (
c1 nvarchar(256)
)
go
create table t2 (
c1 nvarchar(256)
)
go
create table t3 (
c1 nvarchar(256)
)
go
create table t4 (
c1 nvarchar(256)
)
go
exec sp_bindefault 'df_current_user', 't1.c1'
exec sp_bindefault 'df_current_user', 't2.c1'
exec sp_bindefault 'df_current_user', 't3.c1'
exec sp_bindefault 'df_current_user', 't4.c1'
go
select
table_name,
column_name,
column_default
from
information_schema.columns
where
objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
and column_default like '%create default df_current_user as %'
go
declare @.sql nvarchar(4000)
declare c cursor local fast_forward
for
select
'exec sp_unbindefault ''' + table_name + '.' + column_name + '''' as cmd
from
information_schema.columns
where
objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
and column_default like '%create default df_current_user as%'
open c
while 1 = 1
begin
fetch next from c into @.sql
if @.@.error != 0 or @.@.fetch_status != 0 break
exec sp_executesql @.sql
set @.sql = replace(@.sql, 'unbindefault', 'bindefault ''df_login_user'',')
exec sp_executesql @.sql
end
close c
deallocate c
go
select
table_name,
column_name,
column_default
from
information_schema.columns
where
objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
and column_default like '%create default df_login_user as %'
go
drop table t1, t2, t3, t4
go
drop default df_current_user, df_login_user
go
AMB
"Perayu" wrote:

> Not only the default constraint name. I want to replace it to a new one.
> Actually, I can't change the name or update current used df_curr_user
> because it is bound to columns. So, I defined a new one as df_login_user a
nd
> try to replace it. But have no idea how to do it programmatically.
> Thanks.
> Perayu
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:654DA86C-7C7B-4DBF-8DCD-99599075D685@.microsoft.com...
>
>|||It works like a charm!
Thank you so much.
Perayu
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E94A4876-0EF7-470B-8577-BF702B21B53F@.microsoft.com...
> See if this helps.
> use northwind
> go
> create default df_current_user as current_user
> go
> create default df_login_user as suser_sname()
> go
> create table t1 (
> c1 nvarchar(256)
> )
> go
> create table t2 (
> c1 nvarchar(256)
> )
> go
> create table t3 (
> c1 nvarchar(256)
> )
> go
> create table t4 (
> c1 nvarchar(256)
> )
> go
> exec sp_bindefault 'df_current_user', 't1.c1'
> exec sp_bindefault 'df_current_user', 't2.c1'
> exec sp_bindefault 'df_current_user', 't3.c1'
> exec sp_bindefault 'df_current_user', 't4.c1'
> go
> select
> table_name,
> column_name,
> column_default
> from
> information_schema.columns
> where
> objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> and column_default like '%create default df_current_user as %'
> go
> declare @.sql nvarchar(4000)
> declare c cursor local fast_forward
> for
> select
> 'exec sp_unbindefault ''' + table_name + '.' + column_name + '''' as cmd
> from
> information_schema.columns
> where
> objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> and column_default like '%create default df_current_user as%'
> open c
> while 1 = 1
> begin
> fetch next from c into @.sql
> if @.@.error != 0 or @.@.fetch_status != 0 break
> exec sp_executesql @.sql
> set @.sql = replace(@.sql, 'unbindefault', 'bindefault ''df_login_user'',')
> exec sp_executesql @.sql
> end
> close c
> deallocate c
> go
> select
> table_name,
> column_name,
> column_default
> from
> information_schema.columns
> where
> objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> and column_default like '%create default df_login_user as %'
> go
> drop table t1, t2, t3, t4
> go
> drop default df_current_user, df_login_user
> go
>
> AMB
> "Perayu" wrote:
>|||btw, does anyone know a 'clean' way to get the default value of a
default-bound column ?
for example:
create table t1(col varchar(10))
create default s as 'none'
sp_bindefault s, 't1.col'
I can find this value from information_schema.columns, like this:
select column_default
from information_schema.columns
where table_name = 't1'
and it returns:
create default s as 'none'
however, I believe extracting the string after the 'as' to get default
value isn't the best way. Can't imagine that sql server performs
inserts and calculates a default value on-the-flight
thanks,
Tam|||Sometime, the default value is not always a constant and must be done
on-the-flight. Like what I have is using SUSER_SNAME(), which will depends
on the login name.
Perayu
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1125429897.170823.60630@.g43g2000cwa.googlegroups.com...
> btw, does anyone know a 'clean' way to get the default value of a
> default-bound column ?
> for example:
> create table t1(col varchar(10))
> create default s as 'none'
> sp_bindefault s, 't1.col'
> I can find this value from information_schema.columns, like this:
> select column_default
> from information_schema.columns
> where table_name = 't1'
> and it returns:
> create default s as 'none'
> however, I believe extracting the string after the 'as' to get default
> value isn't the best way. Can't imagine that sql server performs
> inserts and calculates a default value on-the-flight
> thanks,
> Tam
>|||Perayu - I agree, but there're times that default values are constant,
would it do on the flight anyway ?sql

Cell Calculation for cells that already has values

Hi,

I have the table like this

Jan Feb .. Dec

account 50 20 ...

On the cube i would like to make it using calculated cells and that look like this

account 50 70 ...

Same cells has before.

I have tried a few stuff but i cant put values on top of the ones that are already there, so i get the values in the cell and not the values in the calculated cell.

Funny, though, i have put a constant (1) i the 'AS' part of the calculated cell syntax and it adds the cell value with the constant. But if i try to add with the value time.lag(1) SSAS just ignores the value.

Is there a way to do this by calculated cell?

Thank you

Have you considered simply creating a calculated measure using the YTD() or PeriodsToDate() functions? If you did not want the end users to see the underlying measure you could set it's visible property to false.|||

Hello,

The problem with calculated members is that the user would have to change the query when browsing on the cube.

What i would like is that, for the users, browsing on the this account or any other i could use the same measure. And for that reason i need it to be a calculated cell.

Is there a way to solve this?

|||

You did not mention which version of Analysis Services you are using. I am assuming from the SSAS acronym that you are using 2005, not 2000.

You would not have to change any queries if you renamed the current measure and then created a new calculated measure with the same name as the original. This would be my preferred approach as you could still get to both the cumulative and the incremental amounts.

However, you could replace the value of a measure using an MDX Script assignment.

I am not clear on exactly what your business requirements are, but the following is a sample bit of MDX Script which works against the Adventure Works database to simply adds the previous member to the current member for the [Sales Amount] measure.

([Measures].[Sales Amount]

,[Date].[Fiscal].Members) = ([Date].[Fiscal].Prevmember)

+ ([Date].[Fiscal].CurrentMember);

|||

You did not mention which version of Analysis Services you are using. I am assuming from the SSAS acronym that you are using 2005, not 2000.

You would not have to change any queries if you renamed the current measure and then created a new calculated measure with the same name as the original. This would be my preferred approach as you could still get to both the cumulative and the incremental amounts.

However, you could replace the value of a measure using an MDX Script assignment.

I am not clear on exactly what your business requirements are, but the following is a sample bit of MDX Script which works against the Adventure Works database to simply adds the previous member to the current member for the [Sales Amount] measure.

([Measures].[Sales Amount]

,[Date].[Fiscal].Members) = ([Date].[Fiscal].Prevmember)

+ ([Date].[Fiscal].CurrentMember);

|||

Hello,

This was a good solution, inline with the other solution in the conversation.

I think that i could try altough it will clash a bit with all my other calculated cell. I did not remember to rename the calculated member to the actual measer name.

With all this i assume that the cell calculation doesn't allow this task.

Thank you

Catching return values of a SP

I have calling a stored procedure that returns two values, and I want to catch these values and to store them into a variable.

Here is a piece of my SP inside SQL Server that shows the returned values:

…
SELECT @.Id = SCOPE_IDENTITY()
SELECT @.Id AS user_id
SELECT 1 AS Value
END
GO

In my aspx page I am trying to call the first value like this:

Dim nID
CmdInsert.Parameters.Add(New SqlParameter("@.RETURN_VALUE", SqlDbType.bigint, 8, "user_id"))
 CmdInsert.Parameters("@.RETURN_VALUE").Direction = ParameterDirection.ReturnValue
CmdInsert.Parameters("@.RETURN_VALUE").Value = nID

And to check if the right value is returned I use:

strConnection.open()
 cmdInsert.ExecuteNonQuery
 'Set the value of a textbox
 ident.text = nID
 strConnection.close()

But now no value appears in the textbox, How can I achieve it? What is wrong?You are sort of combining a few different approaches to solving this problem. Since only one ReturnValue can be returned from a stored procedure and you need 2 values, that approach won't work. And since you only have 2 values, I think that you should use OUTPUT parameters.

The stored procedure would look like this:

CREATE PROCEDURE
 myProcedure
AS
 @.myInput1 varchar(50),
 @.myInput2 varchar(50),
 @.myOutput1 bigint OUTPUT,
 @.myOutput2 bigint OUTPUT
INSERT <etc etc
SET @.myOutput1 = SCOPE_IDENTITY
SET @.myOutput2 = 2

Your aspx page code would look like this:

CmdInsert.Parameters.Add("@.myOutput1", SqlDbType.bigint)
CmdInsert.Parameters("@.myOutput1").Direction = ParameterDirection.Output
CmdInsert.Parameters.Add("@.myOutput2", SqlDbType.bigint)
CmdInsert.Parameters("@.myOutput2").Direction = ParameterDirection.Output
strConnection.open()
cmdInsert.ExecuteNonQuery
'Set the value of a textbox
ident.text = CmdInsert("@.myOutput1")
strConnection.close()

Terri|||I have followed all your steps, and now this error message appears:

BC30367: Class 'System.Data.SqlClient.SqlCommand' cannot be indexed because it has no default property.

What does it mean?|||Sorry, the line afected is this:

Line 105: ident.text = CmdInsert("@.Id")|||I'm the one who's sorry. The correct syntax for that line is:

ident.text = CmdInsert.Parameters("@.Id").Value

Terri|||i think its something like :

ident.text=convert.toint32(CmdInsert.Parameters("@.Id").Value)

hth|||Good! now runs fine

Thank you very much,
Cesar

Cat insert textboxes values into a database table

Hello, my problem is that I have 2 textboxes and when the user writes somthing and clicks the submit button I want these values to be stored in a database table. I am using the following code but nothing seems to hapen. Do I have a problem with the Query (Insert)? Or do I miss something else. Please respond as I can't find this.

<%@.PageLanguage="C#"AutoEventWireup="true"CodeFile="Manufacturer2.aspx.cs"Inherits="Manufacturer2" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>Untitled Page</title>

</head>

<body>

<formid="form1"runat="server">

<div>

<asp:LabelID="Label1"runat="server"Text="Name"></asp:Label>

<asp:TextBoxID="TextBox1"runat="server"></asp:TextBox><br/>

<asp:LabelID="Label2"runat="server"Text="Password"></asp:Label>

<asp:TextBoxID="TextBox2"runat="server"></asp:TextBox>

<br/>

<br/>

<asp:ButtonID="Button1"runat="server"Text="Submit"OnClick="Button1_Click"/>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="INSERT INTO Manufacturer(Name, Password) VALUES (@.TextBox1, @.TextBox2)">

<SelectParameters>

<asp:ControlParameterControlID="TextBox1"Name="TextBox1"PropertyName="Text"/>

<asp:ControlParameterControlID="TextBox2"Name="TextBox2"PropertyName="Text"/>

</SelectParameters>

</asp:SqlDataSource>

</div>

</form>

</body>

</html>

erom:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="INSERT INTO Manufacturer(Name, Password) VALUES (@.TextBox1, @.TextBox2)">

user insert command not select command

InsertCommandType="Text" InsertCommand="INSERT INTO Manufacturer(Name, Password) VALUES (@.TextBox1, @.TextBox2)"

Hope this will help.|||

Hi u can go with the first reply which is secured one

or u can write like this

st="insert into x values(" & trim(t1.value & "," & t2.value & ")"

cmd.executenonquery

cmd is the command object

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

I tried this one but it didn't work. I don't know what is happening. Again no stored data in my database. Do I also have to write something in .cs file??

Thank you for your reply

|||

erom:

o I also have to write something in .cs file??

You need to call the Insert method of the DataSource in the button click event.

SqlDataSource1.Insert()

below is the .aspx page code.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" InsertCommand="INSERT INTO Manufacturer(Name, Password) VALUES ( @.T1 , @.T2 )" ConnectionString="<%$ ConnectionStrings:xxxx%>"> <InsertParameters> <asp:ControlParameter ControlID="TextBox1" DefaultValue="" PropertyName="Text" Name="T1" /> <asp:ControlParameter ControlID="TextBox2" DefaultValue="" PropertyName="Text" Name="T2" /> </InsertParameters> </asp:SqlDataSource>

|||

Unfortunately, I still can't store the values inthe table. My code is the above (for the .aspx file) - Tell me if what am I missing:

<asp:LabelID="Label1"runat="server"Text="Name"></asp:Label>

<asp:TextBoxID="TextBox1"runat="server"></asp:TextBox><br/>

<asp:LabelID="Label2"runat="server"Text="Password"></asp:Label>

<asp:TextBoxID="TextBox2"runat="server"></asp:TextBox>

<br/>

<br/>

<asp:ButtonID="Button1"runat="server"Text="Submit"/>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"InsertCommand='INSERT INTO Manufacturer(Name, Password) VALUES (@.TextBox1, @.TextBox2)'

ConnectionString="<%$ ConnectionStrings:ConnectionString %>">

<InsertParameters>

<asp:ControlParameterControlID="TextBox1"DefaultValue=""Name="TextBox1"PropertyName="Text"/>

<asp:ControlParameterControlID="TextBox2"DefaultValue=""Name="TextBox2"PropertyName="Text"/>

</InsertParameters>

</asp:SqlDataSource>

And for the .cs file:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

publicpartialclassManufacturer2 : System.Web.UI.Page

{

protectedvoid Page_Load(object sender,EventArgs e)

{

}

protectedvoid Button1_Click(object sender,EventArgs e)

{

SqlDataSource1.Insert();

}

}

|||

The page and the code looks fair enough. the only thing I suppose you are missing is the InsertCommandType=Text. This is just a small piece of try out. I'm interested in what response do you get when you press the button ? Has any error occurred or something like that?

CASTing a datatype returned by CASE Statement

I realize that the CASE statement doesn't like different datatypes as return values but if I want to format the "0" in the second WHEN condition below to "000", how do I do that? I have a "Region" that is "000" and would like it to show up that way at the very top of my report. I have the GROUP BY and ORDER BY to work fine, it just shows up as "0" and I can't change it. I realize it is being read as an int but am having trouble with the CAST and where to place it. Thanks again, you guys are great.

ddave

SELECT Region =
CASE WHEN branch_num IN(48,53,78,173,186,198,208,212,257,286,287,317,35 3,398,440,
478,571,572,610,1069) THEN 44
WHEN branch_num IN(484,532,841,864,7001,7101,7102,7103,7104,9031) THEN 0
ELSE 999
ENDThat depends (doesn't it always?) on what you really want. If you want the other regions to show using normal INT formatting, but 0 to be a special case That is one thing, if you want all the region numbers to be zero filled, that is something different. If you want something I haven't thought of yet, then that's probably different too.

The quick and dirty would be to use:SELECT Region =
CASE
WHEN branch_num IN(48,53,78,173,186,198,208,212,257,286,287,317,35 3,398,440,
478,571,572,610,1069) THEN ' 44'
WHEN branch_num IN(484,532,841,864,7001,7101,7102,7103,7104,9031) THEN '000'
ELSE '999'
END

-PatP|||Pat,

Once again, "You da Man!!". It works perfectly. I decided to use '000', ' 1', ' 78', etc. I spent over an hour on it and I knew it was something easy. I mean I don't expect a medal or anything but you can be lost w/o "the little details". Thanks again.

ddave|||If I want the format to show the Region field just once, is there a way to do that? My current report has a Region field immediately to the left of BranchNo. Branches are contained within the Regions. I got it to list Region every time I show a record but just in case the manager wants it formatted the way I mention I want to be prepared. The example I was to follow has Region just once.

This is an example of what I have now:

code:--------------------
Region BranchNo OrderNo ErrorCode1 ErrorCode2 ErrorCode3
000 478 111 0 1 1
000 478 112 0 0 0
000 478 113 1 0 0
001 610 119 0 0 0
001 610 120 1 0 0
----------------------

This is an example of what I wish to try:

code:--------------------
Region BranchNo OrderNo ErrorCode1 ErrorCode2 ErrorCode3
000 478 111 0 1 1
478 112 0 0 0
478 113 1 0 0
001 610 119 0 0 0
610 120 1 0 0
----------------------

ddave|||What reporting tool are you using? Hopefully this isn't 100% Transact-SQL based, right?

-PatP|||Well, I am looking at the data in Query Analyzer but that is a good question. I guess the real answer is that we haven't decided yet. I can use Access though I have to figure out the mechanics which I know won't be difficult. I can even stick it on an Excel spreadsheet as long as it looks good. I say Access because that is "what the others did" but it is not an issue.

ddave|||It's a presntation issue, and Access is very good at it, and can easily do what your asking...

I'd love to setup reporting services though...

Anyone seen it?

What's the installation like?

What's the interface?

Can you use the same box as sql server?

PS. If they say Crystal...run...|||Reporting Services is quite cool, but it is rather complex and it requires Visual Studio to develop reports.

MS-Access would be beauteous, and would make the formatting, grouping, etc rather simple. I'm not nearly as alergic to Crystal Reports as most folks around here seem to be, but I would STRONGLY advise using Access unless you have another tool of choice.

-PatP

cast from float to varchar

Hi,

Can I convert from float to varchar without trunc the values? Can I use any mask like '#.##'?

from -> cast ( 123.44 as varchar(256) )

result = '123.44'

thanks,

Hi Alessandro,

As long as the varchar type you are casting to is long enough, no truncation of the float value will occur.

For example, cast(23.444 as varchar(3)) will result in a overflow, where cast(23.444 as varchar(6)) will return correctly.

Is that what you meant?

Cheers,

Rob

Cast datetime to smalldatetime error

I have a table that I converted from MS Access and one of the colums I use t
o
store time values into has been converted to datetime and the year 1899 was
added to all existing records. I want to just display the time portion and
have tried the following code in the function I use to display the data:
CAST(ESR_CLOSE_TIME AS smalldatetime)
When I try to run the function I get an error saying the conversion resulted
in an overflow error. Any Ideas. Thanksyes - cast as datetime instead
the lowest smalldatetime value possible is Jan 1, 1900, so 1899 is out
of range.
AkAlan wrote:
> I have a table that I converted from MS Access and one of the colums I use
to
> store time values into has been converted to datetime and the year 1899 wa
s
> added to all existing records. I want to just display the time portion and
> have tried the following code in the function I use to display the data:
> CAST(ESR_CLOSE_TIME AS smalldatetime)
> When I try to run the function I get an error saying the conversion result
ed
> in an overflow error. Any Ideas. Thanks|||SELECT CONVERT(char(8), ESR_CLOSE_TIME , 108) --24hr time with seconds
SELECT RIGHT(CONVERT(char(19), ESR_CLOSE_TIME , 0),7) --with AM/ PM
The datetime datatype includes the date (year, month, day) and time with an
accuracy of 3/1000 of a second.
--
"AkAlan" wrote:

> I have a table that I converted from MS Access and one of the colums I use
to
> store time values into has been converted to datetime and the year 1899 wa
s
> added to all existing records. I want to just display the time portion and
> have tried the following code in the function I use to display the data:
> CAST(ESR_CLOSE_TIME AS smalldatetime)
> When I try to run the function I get an error saying the conversion result
ed
> in an overflow error. Any Ideas. Thanks

Cast and convert

How do I convert a colomn that is of data type varbinary to varchar so that the hex values saved there are shown as readable text usable with crystal reports.In SQL2K there is a function
master.dbo.fn_varbintohexstr (@.pbinin varbinary(8000) ) returns
nvarchar(4000)
Don't know why @.pbinin is varbinary(8000), since nvarchar(4000) can only
handle 2000 bytes!
It's just pretty simple code for doing the translation; if nvarchar(4000)
doesn't suit you, the source for the function is easy to modify (to create a
different function!)
"shaun" <shaun@.discussions.microsoft.com> wrote in message
news:F8EAF61A-2844-4C7C-BE22-46C72B9A7A77@.microsoft.com...
> How do I convert a colomn that is of data type varbinary to varchar so
that the hex values saved there are shown as readable text usable with
crystal reports.