Formula in a View

I'm trying to perform the following within a view. I'm not getting the
values which I would expect. I'm assuming I have a data type problem where
some precision is being dropped. Could someone give me a heads-up on what
I'm doing wrong? I need full precision.
SUM(((MarketPrice+MarketPriceUp100-(2*MarketPriceUp50))/(MarketPriceUp50*((1
00/10000)^2))/100)
* MarketValue)/ SUM(MarketValue) as [Convexity],
Thanks,
Tom Woods
The Baker Group, LP"Tom Woods" <twoods@.jamesbaker.com> wrote in message
news:OgZCcxTvFHA.2396@.TK2MSFTNGP14.phx.gbl...
> I'm trying to perform the following within a view. I'm not getting the
> values which I would expect. I'm assuming I have a data type problem
> where some precision is being dropped. Could someone give me a heads-up
> on what I'm doing wrong? I need full precision.
>
> SUM(((MarketPrice+MarketPriceUp100-(2*MarketPriceUp50))/(MarketPriceUp50*(
(100/10000)^2))/100)
> * MarketValue)/ SUM(MarketValue) as [Convexity],
>
What are the types of those columns?
Integral types use integer division.
David|||All columns in the formula are 9(12,8).
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uyQL80TvFHA.1560@.TK2MSFTNGP09.phx.gbl...
> "Tom Woods" <twoods@.jamesbaker.com> wrote in message
> news:OgZCcxTvFHA.2396@.TK2MSFTNGP14.phx.gbl...
> What are the types of those columns?
> Integral types use integer division.
> David
>|||Hi Tom
This piece of the expression (100/10000) will be carried out as integer
division, and yield 0. If you want decimal results, make at least one of the
operands a decimal (100.0/10000)
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tom Woods" <twoods@.jamesbaker.com> wrote in message
news:OgZCcxTvFHA.2396@.TK2MSFTNGP14.phx.gbl...
> I'm trying to perform the following within a view. I'm not getting the
> values which I would expect. I'm assuming I have a data type problem
> where some precision is being dropped. Could someone give me a heads-up
> on what I'm doing wrong? I need full precision.
>
> SUM(((MarketPrice+MarketPriceUp100-(2*MarketPriceUp50))/(MarketPriceUp50*(
(100/10000)^2))/100)
> * MarketValue)/ SUM(MarketValue) as [Convexity],
> Thanks,
> Tom Woods
> The Baker Group, LP
>|||When I change it to the following I get an error. Could the problem be with
the exponent? Is there a different way of performing the exponential?
((100.0/10000)^2)
BTW, I get the following error when changing 100 to 100.0
--
Microsoft SQL-DMO (ODBC SQLState: 42000)
--
Error 403: Invalid operator for data type. Operator equals boolean XOR, type
equals numeric.
--
OK
--
Thanks,
Tom
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:usIhD4TvFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi Tom
> This piece of the expression (100/10000) will be carried out as integer
> division, and yield 0. If you want decimal results, make at least one of
> the operands a decimal (100.0/10000)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tom Woods" <twoods@.jamesbaker.com> wrote in message
> news:OgZCcxTvFHA.2396@.TK2MSFTNGP14.phx.gbl...
>
>|||^ operator is bitwise exclusive OR operation for integers. For getting
exponential values as results you might want to try POWER function like:
SELECT POWER( 100.0/10000, 2 )
Anith|||Can you use (1.0/10000) instead of ((100.0/10000)^2)?
Perayu
"Tom Woods" <twoods@.jamesbaker.com> wrote in message
news:uVvmS$TvFHA.596@.TK2MSFTNGP12.phx.gbl...
> When I change it to the following I get an error. Could the problem be
> with the exponent? Is there a different way of performing the
> exponential?
> ((100.0/10000)^2)
>
> BTW, I get the following error when changing 100 to 100.0
> --
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> --
> Error 403: Invalid operator for data type. Operator equals boolean XOR,
> type equals numeric.
> --
> OK
> --
> Thanks,
> Tom
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:usIhD4TvFHA.1032@.TK2MSFTNGP12.phx.gbl...
>|||I was able to get it to work by using the POWER function and also changing
the integer values to have precision.
Thanks,
Tom
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:uPKrC0UvFHA.908@.tk2msftngp13.phx.gbl...
> Can you use (1.0/10000) instead of ((100.0/10000)^2)?
> Perayu
> "Tom Woods" <twoods@.jamesbaker.com> wrote in message
> news:uVvmS$TvFHA.596@.TK2MSFTNGP12.phx.gbl...
>

formatting issures using tables and mutiple matrixes

Hi. I have a report that pulls all info from a single dataset. However, one
field "type" contains either "a" or "b". I have two matrixes, One for the
description and count of type a and another for type b. I also have data
from the dataset that needs to be both before and after each matrix.
When i layout the tables and matrixes, I measure them EXACTLY next to each
other by adding the position of one item and the width of it to determin the
position of the next. Also put them all in a header of another table with
zero padding. I also make the cangrow and canshrink atrributes to false to
keep all my rows aligned.
No matter what I do, the report always comes out unaligned. I've tried
rectangles already and they didnt work either. sometimes the report will
break on the tables for x number of rows and break for the matrixes at y
number of rows.
Does anyone know how to make matrixes and tables line up side by side,
return the same number of rows per page (this hsould be a no brainer because
its getting its data from the same dataset and there are no forced breaks)
AND make sure all the rows are the same width to ensure what is really four
tables looks like one?
I can provied the rdl if anyone wants to see it.Sorry, forgot to mention. SQL2005, Reporting Services 2005
"cr" wrote:
> Hi. I have a report that pulls all info from a single dataset. However, one
> field "type" contains either "a" or "b". I have two matrixes, One for the
> description and count of type a and another for type b. I also have data
> from the dataset that needs to be both before and after each matrix.
> When i layout the tables and matrixes, I measure them EXACTLY next to each
> other by adding the position of one item and the width of it to determin the
> position of the next. Also put them all in a header of another table with
> zero padding. I also make the cangrow and canshrink atrributes to false to
> keep all my rows aligned.
> No matter what I do, the report always comes out unaligned. I've tried
> rectangles already and they didnt work either. sometimes the report will
> break on the tables for x number of rows and break for the matrixes at y
> number of rows.
> Does anyone know how to make matrixes and tables line up side by side,
> return the same number of rows per page (this hsould be a no brainer because
> its getting its data from the same dataset and there are no forced breaks)
> AND make sure all the rows are the same width to ensure what is really four
> tables looks like one?
> I can provied the rdl if anyone wants to see it.
>

Formatting Dates

Why would this not format a date into the dd/mm/yy format ?
CONVERT(datetime, Actioned,103) As Actioned
Actioned is a column of type datetime.
Thanks in advance...Because you're converting it to datetime, not a string. ;)
"McHenry" wrote:

> Why would this not format a date into the dd/mm/yy format ?
> CONVERT(datetime, Actioned,103) As Actioned
>
> Actioned is a column of type datetime.
>
> Thanks in advance...
>
>|||Probably because the ONLY -- repeat for those who never learned
Standard SQL-- the **ONLY** format is based on ISO-8601 (should explain
ISO?).
Duh!
It is also the only one in the rest of the ISO Standards. But you did
your research, before you posted, right?
You are one of the kids I want to hit with a stick!! You think that
your local "hillbilly dialect" is law of the universe.
Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just SQL
and RDBMS.
Violate ISO standard in your applications and not in the database. And
comment your errors, so that a better programmer can find a correct
after you are fired.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145329781.514897.34950@.v46g2000cwv.googlegroups.com...
> Probably because the ONLY -- repeat for those who never learned
> Standard SQL-- the **ONLY** format is based on ISO-8601 (should explain
> ISO?).
>
Joe it's been a while since I've had the pleasure of one of your opinionated
condescending responses, great to hear from you again and thanks for the
input. Last time I had the pleasure of your whine was when you helped with
nested sets, an excellent solution I might add.

> Duh!
> It is also the only one in the rest of the ISO Standards. But you did
> your research, before you posted, right?
> You are one of the kids I want to hit with a stick!! You think that
> your local "hillbilly dialect" is law of the universe.
Joe if hitting kids with sticks is a fantasy I am sure there are
professionals that can help, were you spanked roughly as a child ?

> Why are you formatting data in the back end? The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end. This a more basic programming principle than just SQL
> and RDBMS.
I would have done my formatting in the frond end application however the
rows returned are being displayed in an MS Access list box that does not
allow for formatting and therefor I must format the information at the
server end, not to confuse you with the facts Joe as it would interrupt the
constant flap of your jaw

> Violate ISO standard in your applications and not in the database. And
> comment your errors, so that a better programmer can find a correct
> after you are fired.
>
Personal experience ?|||"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:8C104A08-E6B9-4B10-BC20-805041F19702@.microsoft.com...
> Because you're converting it to datetime, not a string. ;)
> "McHenry" wrote:
>
Thanks Rob, interestingly I have two scenarios where I have made the same
mistake, one formatted the dates as desired however this one didn't...|||"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:8C104A08-E6B9-4B10-BC20-805041F19702@.microsoft.com...
> Because you're converting it to datetime, not a string. ;)
> "McHenry" wrote:
>
Rob, so formatting to 103 should be to type char(8) ?|||>> Joe it's been a while since I've had the pleasure of one of your opiniona
ted
condescending responses, great to hear from you again and thanks for
the
input. Last time I had the pleasure of your whine was when you helped
with
nested sets, an excellent solution I might add. <<
Thank you, Grasshopper :) Oh, my wife should become a Zen Monk this
year. Then she can beat you with a stick :)
Out of the "zen mode"; have you found a good enumerated path set model
to Nested Sets model algorithm? All I have is a enumerated path to
adjacency list to nested sets program. The overhead is bad.|||"McHenry" <mchenry@.mchenry.com> wrote in message
news:44445f25$0$16677$5a62ac22@.per-qv1-newsreader-01.iinet.net.au...
> "Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
> news:8C104A08-E6B9-4B10-BC20-805041F19702@.microsoft.com...
> Rob, so formatting to 103 should be to type char(8) ?
>
Oops meant CHAR(10)|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145331802.606758.250160@.u72g2000cwu.googlegroups.com...
> condescending responses, great to hear from you again and thanks for
> the
> input. Last time I had the pleasure of your whine was when you helped
> with
> nested sets, an excellent solution I might add. <<
> Thank you, Grasshopper :) Oh, my wife should become a Zen Monk this
> year. Then she can beat you with a stick :)
Reading between the lines Joe I think the beating with sticks thing goes
back long before you even started reasearching Zen...

> Out of the "zen mode"; have you found a good enumerated path set model
> to Nested Sets model algorithm? All I have is a enumerated path to
> adjacency list to nested sets program. The overhead is bad.
>
It was quite a while ago and the scenario was a component breakdown into
parts and sub parts, your solution worked perfectly and is very impressive,
once I got my head around it. I did try at a later time to adapt it to a
genealogy application using two parallel nested sets however it simply got
too complicated and as it was a private project I put it to bed...
Thanks again for your help and I often think of your opinionated tones and
smile when I'm photocopying sections from one of your books :)|||CONVERT(carchar(10), Actioned,103) As Actioned
Is it right?
"McHenry"?? ??? ??:

> Why would this not format a date into the dd/mm/yy format ?
> CONVERT(datetime, Actioned,103) As Actioned
>
> Actioned is a column of type datetime.
>
> Thanks in advance...
>
>

Formatting Date as integer

I want to for that format the date in YYYYMMDD and MMDDYY, with no '-' as data type is integer

I have used the following code (not the conversion function as I don’t need Hyphen '-')

for YYYYDDMM

SET @.DATE = CONVERT(INT,(CONVERT(VARCHAR(4),DATEPART(YYYY,GETDATE())) +

CONVERT(VARCHAR(4), DATEPART(MM,GETDATE())) +

CONVERT(VARCHAR(4), DATEPART(DD,GETDATE())) ))

& for MMDDYY

SET @.DATEUS = CONVERT(INT,(CONVERT(VARCHAR(3),DATEPART(MM,GETDATE()))+

CONVERT(VARCHAR(3),DATEPART(DD,GETDATE())) +

SUBSTRING(CONVERT(VARCHAR(4), DATEPART(YY,GETDATE())),3,4) ))

I am getting the result

YYYYMMDD= 200688

MMDDYY =8806

but i want result in

YYYYDDMM = 20060808

MMDDYY = 080806

note: I need to convert in integer, finally, caz database data type is integer.

can any one give me solution

waiting for quick reply

regards,

Anas

Just use convert:

select cast(convert(varchar(8),getdate(),112) as integer)

I use this for our date_dimension/calendar table surrogate keys all of the time.

|||thanx for your quick reply.

Formatting

Hello all, I have a strange problem that i need some advice on. I have the following field called FILENO. It is a SQL 2000 field with the Data Type set to Char (7). The following sql statement works perfectly: SELECT TOP 1 RTRIM(FILENO) AS TEST, RIGHT(DATEPART(Yy, FILENOYEAR), 2) AS YEAR FROM tblRecords WHERE RIGHT(DATEPART(Yy, FILENOYEAR), 2) = '05' ORDER BY FILENO DESC It returns the correct data, 0050. Now, what i'm trying to do is add 1 to the value so i can get the next available number which is 0051. But, when i run the following sql statement, i get 51 instead of 0051. SELECT TOP 1 RTRIM(FILENO+1) AS TEST, RIGHT(DATEPART(Yy, FILENOYEAR), 2) AS YEAR FROM tblRecords WHERE RIGHT(DATEPART(Yy, FILENOYEAR), 2) = '05' ORDER BY FILENO DESC Does anyone have an idea how to solve this? Thanks. Richard M.

do you convert it to Char(7) after you add 1 ... so that will reformat it to the char rather than integer

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.

Formating Ntext

Hi,
I am using SQLServer 2000
I currently have a table with just one field in it of type (ntext), the
reason for this is there is a very large amount of data in it of an XML
format. The data looks something like below althought this is only a small
sample there is actually a lot more in this field:-
<Main>This is going to be a lot of text.....<Main/><Paragraph>To buy a
house in the UK now requires a very large deposit, more than 5 times the
amount that was necessary in 1999 <Paragraph/><House
Type>Detached<HouseType/><Price>￡300,000<Price/><Location>Berkshire<Locatio
n/>
<Garage>Yes<Garage/><Bedrooms>4<Bedrooms/><
What I want is to break out the data into columns so you have something like
Paragraph HouseType Price Location
Garage Bedrooms
To buy a house... Detached ￡300,000 Berkshire Yes 4
I want to ignore the data between the <Main> Tags
Generally I think I need to break the data up based on the foward slash and
work left from there, I think it's going to be based on a left/charindex
principal but I also have to do a convert to varchar in the first place as
you are unable to do much with the data in it's current data type.
If anyone can help it would be much appreciated.
Thanks PDAre you going to do this exactly once, or regularly?
In either case, I think it would be better to have an app pull the whole
value out, parse it using XML or RegEx or whatever, and generate the INSERT
statement or procedure call necessary to break the data out.
Going forward, it would probably be better to store the individual pieces of
data and build the XML when selecting. Or, if you can move to SQL Server
2005, you can use the new XML datatype, which provides a whole slew of
options to make your implementation choice harder.
A
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:46877615-148D-4912-992D-AAF2DB435243@.microsoft.com...
> Hi,
> I am using SQLServer 2000
> I currently have a table with just one field in it of type (ntext), the
> reason for this is there is a very large amount of data in it of an XML
> format. The data looks something like below althought this is only a
> small
> sample there is actually a lot more in this field:-
> <Main>This is going to be a lot of text.....<Main/><Paragraph>To buy a
> house in the UK now requires a very large deposit, more than 5 times the
> amount that was necessary in 1999 <Paragraph/><House
> Type>Detached<HouseType/><Price>￡300,000<Price/><Location>Berkshire<Locat
ion/>
> <Garage>Yes<Garage/><Bedrooms>4<Bedrooms/><
> What I want is to break out the data into columns so you have something
> like
> Paragraph HouseType Price Location
> Garage Bedrooms
> To buy a house... Detached ￡300,000 Berkshire Yes
> 4
> I want to ignore the data between the <Main> Tags
> Generally I think I need to break the data up based on the foward slash
> and
> work left from there, I think it's going to be based on a left/charindex
> principal but I also have to do a convert to varchar in the first place as
> you are unable to do much with the data in it's current data type.
> If anyone can help it would be much appreciated.
> Thanks PD|||Hi Aaron,
Thanks for the reply, I was going to go down the RegEx route but as this is
only a one of I just wanted to make it as quick and simple as possible so I
wanted to opt for querying it straight out of the field!!!.
Thanks PD
"Aaron Bertrand [SQL Server MVP]" wrote:

> Are you going to do this exactly once, or regularly?
> In either case, I think it would be better to have an app pull the whole
> value out, parse it using XML or RegEx or whatever, and generate the INSER
T
> statement or procedure call necessary to break the data out.
> Going forward, it would probably be better to store the individual pieces
of
> data and build the XML when selecting. Or, if you can move to SQL Server
> 2005, you can use the new XML datatype, which provides a whole slew of
> options to make your implementation choice harder.
> A
>
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:46877615-148D-4912-992D-AAF2DB435243@.microsoft.com...
>
>

Format the Report Manager Parameter headings

Hello,

I was wondering if it would be possible to change the font type and color of the parameter titles in Report Manager? Like can I change it in XML some sort of way...

<QueryParameters>

<QueryParameter Name="@.ProductLang">

<Value>=Parameters!ProductLang.Value</Value>

</QueryParameter>

Ex: @.StartDate is a parmeter, I'd like to change the text's font color and style to BLUE and Italics.

It is not possible to configure this in Report Manager.

You could write a custom ASP.NET page with your desired stylizations that will pass the parameters to the ReportServer.

-Chris

Format text in "parameter"

hi,
i've got a "parameter" that is of date type, but is there any way so that it
only shows the "short date" format -- don't want the time.
thanks,Maersa,
yeah, u can write a small function is custom code that takes string
as a parameter and return the same. in that function u can write
conversion functionality. and use this function is expresssion of
textbox or wherever u want.
textbox = Code.GetShortDate(Parameters!Name.Value)
and in custom code write a fuction with VB
Note: function declaration here might not be synteticaly correct.
Shared fuction GetShortDate(byval date a string) as String
write code here to convert date to DateTime and back to string
return shortdate
end function
hope this helps.
Thanks,
mahesh|||thanks mahesh,
how would i write a .NET assembly and then use it within my report ?
thanks,
"Mahesh" <gaware@.gmail.com> wrote in message
news:1121998689.809184.145380@.f14g2000cwb.googlegroups.com...
> Maersa,
> yeah, u can write a small function is custom code that takes string
> as a parameter and return the same. in that function u can write
> conversion functionality. and use this function is expresssion of
> textbox or wherever u want.
> textbox = Code.GetShortDate(Parameters!Name.Value)
> and in custom code write a fuction with VB
> Note: function declaration here might not be synteticaly correct.
> Shared fuction GetShortDate(byval date a string) as String
> write code here to convert date to DateTime and back to string
> return shortdate
> end function
> hope this helps.
> Thanks,
> mahesh
>|||There is no way you can do this. You can use the data format elsewhere and
strip the time as the other posting was saying but you cannot have report
manager change how it functions. What you can do is to use string parameter
type instead of date parameter type. That is what I do when I don't want the
time. Of course, then they could put in something that is not a date.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"maersa" <masa_iwasa@.hotmail.com> wrote in message
news:OqI9PGmjFHA.3164@.TK2MSFTNGP15.phx.gbl...
> hi,
> i've got a "parameter" that is of date type, but is there any way so that
> it only shows the "short date" format -- don't want the time.
> thanks,
>|||The code he is talking about can exist in the report itself with no outside
dependencies... Go to Report->Code from the top menu item and add your
functions there...I also have a example that does not use a code function
( if I remember correctly) at www.msbicentral.com
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"maersa" <masa_iwasa@.hotmail.com> wrote in message
news:OqI9PGmjFHA.3164@.TK2MSFTNGP15.phx.gbl...
> hi,
> i've got a "parameter" that is of date type, but is there any way so that
> it only shows the "short date" format -- don't want the time.
> thanks,
>|||I have a report that i did in visual studio that calls a stored procedure.
the @.AsOfDate is of datetime format in the procedure. In visual studio, i
made it a string. I even defaulted a string value of 12/31/2007. When i
deploy it in reporting services, it shows up as string, which is correct, but
it has a default of 12/31/2007 12:00:00 AM. Is there anyway i can get rid of
the time?
"Bruce L-C [MVP]" wrote:
> There is no way you can do this. You can use the data format elsewhere and
> strip the time as the other posting was saying but you cannot have report
> manager change how it functions. What you can do is to use string parameter
> type instead of date parameter type. That is what I do when I don't want the
> time. Of course, then they could put in something that is not a date.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "maersa" <masa_iwasa@.hotmail.com> wrote in message
> news:OqI9PGmjFHA.3164@.TK2MSFTNGP15.phx.gbl...
> > hi,
> >
> > i've got a "parameter" that is of date type, but is there any way so that
> > it only shows the "short date" format -- don't want the time.
> >
> > thanks,
> >
> >
>
>|||Can't you just use FieldName.Value.ToShortDateString ?
--
"Everyone knows something you don't know"
"Wayne Snyder" wrote:
> The code he is talking about can exist in the report itself with no outside
> dependencies... Go to Report->Code from the top menu item and add your
> functions there...I also have a example that does not use a code function
> ( if I remember correctly) at www.msbicentral.com
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "maersa" <masa_iwasa@.hotmail.com> wrote in message
> news:OqI9PGmjFHA.3164@.TK2MSFTNGP15.phx.gbl...
> > hi,
> >
> > i've got a "parameter" that is of date type, but is there any way so that
> > it only shows the "short date" format -- don't want the time.
> >
> > thanks,
> >
> >
>
>|||Where are you making it a string? Click somewhere blank on your report.
Menu->Report->Report Parameters. Click on the parameter and change the data
type to string.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"deniseamat" <deniseamat@.discussions.microsoft.com> wrote in message
news:296463D8-01F3-4906-BB0B-C0F0DAFB9490@.microsoft.com...
>I have a report that i did in visual studio that calls a stored procedure.
> the @.AsOfDate is of datetime format in the procedure. In visual studio, i
> made it a string. I even defaulted a string value of 12/31/2007. When i
> deploy it in reporting services, it shows up as string, which is correct,
> but
> it has a default of 12/31/2007 12:00:00 AM. Is there anyway i can get rid
> of
> the time?
> "Bruce L-C [MVP]" wrote:
>> There is no way you can do this. You can use the data format elsewhere
>> and
>> strip the time as the other posting was saying but you cannot have report
>> manager change how it functions. What you can do is to use string
>> parameter
>> type instead of date parameter type. That is what I do when I don't want
>> the
>> time. Of course, then they could put in something that is not a date.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "maersa" <masa_iwasa@.hotmail.com> wrote in message
>> news:OqI9PGmjFHA.3164@.TK2MSFTNGP15.phx.gbl...
>> > hi,
>> >
>> > i've got a "parameter" that is of date type, but is there any way so
>> > that
>> > it only shows the "short date" format -- don't want the time.
>> >
>> > thanks,
>> >
>> >
>>

Format Parameter Error

Hi all, i am a newbie to reporting services. Hope someone can help me with this date problem.

I have a parameter date which i declared as type string in the report manager. when i entered the date as eg) "31/12/2003" and clicked on the 'preview' tab in the report manager, it gives me error saying that 'Cast from string "31/12/2003" to type 'Date' is not valid.'

So i use the cultureinfo function to set the date to 'en-US' format.... something like the one below:

CStr( Format(DateTime.Parse( Parameters!strDateTo.Value , New System.Globalization.CultureInfo("en-US")), "dd-MMM-yyyy"))

This works perfectly in my preview tab. But when i deployed to the server and when viewed on IE, it gives me an error msg -> "#Error" instead. Why is that so?

I have also checked my pc regional settings . It's set to United States and the date format is also "dd/MM/yyyy". Have also checked the settings in the server and it's also set to the same format. But i am still getting this error.

By the way... i am using windows 2003 with SRS 2000 SP2.

Any help will be appreciated. Thks !

First, why did you create a string parameter and not a date parameter? Second, the interpretation of the input is based on the language specified by the browser, which is why you got something different in the designer vs. when you deployed. Finally, if you want to evaluate strings based on a specific locale, you can just set the locale of the report (report property) to "en-US". You don't need to use a CultureInfo object.

Format of DateTime types in Select vs Open Table

From within the SQL Server Management Studio, if I select a table and displa
y
it (right click Open Table), columns of type DateTime are displayed with the
mm/dd/yyyy hh:mm:ss format. However, if I SELECT * FROM the same table from
a
query in the Query Analyzer (or New Query in 2005), the same column is
displayed with a yyyy-mm-dd hh:mm:ss format.
Why is there a difference in display format between opening the table in the
Management Studio and SELECT'ing in a query?
Short of using a CONVERT in the SELECT, is there a way of controlling the
display format of DateTime types in both scenarios?
Michael
--
Michael Hocksteinmichael (howlinghound@.nospam.nospam) writes:
> From within the SQL Server Management Studio, if I select a table and
> display it (right click Open Table), columns of type DateTime are
> displayed with the mm/dd/yyyy hh:mm:ss format. However, if I SELECT *
> FROM the same table from a query in the Query Analyzer (or New Query in
> 2005), the same column is displayed with a yyyy-mm-dd hh:mm:ss format.
> Why is there a difference in display format between opening the table in
> the Management Studio and SELECT'ing in a query?
Open Table respects the regional settings, while by default Query Analyzer
does not. You can change this for QA by going into Tools->Options->
Connections and check "Use regional settings...". There does not seem to be
any similar option for Management Studio. but query results always
apparently uses ISO format. (I can't really tell from here, because my
regional settings are the ISO format.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you!
Michael Hockstein
"Erland Sommarskog" wrote:

> michael (howlinghound@.nospam.nospam) writes:
> Open Table respects the regional settings, while by default Query Analyzer
> does not. You can change this for QA by going into Tools->Options->
> Connections and check "Use regional settings...". There does not seem to b
e
> any similar option for Management Studio. but query results always
> apparently uses ISO format. (I can't really tell from here, because my
> regional settings are the ISO format.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

Format of DateTime Column in SELECT query

I am using SQL2005 and ASP.NET 2.0

I have one column in database called DateTime and it is defined like type datetime.It is formated like: day.month.year hour:minutes:seconds

My question is: I want to get date from Column DateTime in format day.month.year in SELECT query, not in stored procedure.

thanks

SELECT

CONVERT(NVARCHAR(10),getdate(), 104)as yourDate

replace the getdate() function with your datetime field in your select statement.

|||I just want to remind you that Column Name DateTime is the keyword of sql. You have to put it in [] or ""|||thanx, i managed it somehow.I found instructions in book about CONVERT so i saw how it works

Format expression returned a data type that is not valid

Hi,
I'm new on Reporting Services 2005 and would apreciate your help.
I'm trying to format a textbox in a matrix report to present an ABS value.
I've tried several formulas but I'm getting this warning :
"The Format expression used in textbox â'SALDO_PERIODOâ' returned a data type
that is not valid."
The textbox value is Sum(Fields!SALDO_PERIODO.Value and the format formula
I'm using is =Abs(Sum(Fields!SALDO_PERIODO.Value, "SALDOS"))
Thanks,
--
NSThe format code property has to evaluate to a string. Also, the format code
property uses .NET formatcode string. Please check MSDN for details:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nuno Santos" <NunoSantos@.discussions.microsoft.com> wrote in message
news:E6AC8CC1-61A1-4418-9982-691755C7C550@.microsoft.com...
> Hi,
> I'm new on Reporting Services 2005 and would apreciate your help.
> I'm trying to format a textbox in a matrix report to present an ABS value.
> I've tried several formulas but I'm getting this warning :
> "The Format expression used in textbox 'SALDO_PERIODO' returned a data
> type
> that is not valid."
> The textbox value is Sum(Fields!SALDO_PERIODO.Value and the format formula
> I'm using is =Abs(Sum(Fields!SALDO_PERIODO.Value, "SALDOS"))
> Thanks,
> --
> NS

Format Datetime type in column

Hi
Can you please help me on this matter please?

I am trying to input UK(for example: 25/09/2007 ) format datetime in sql server. Is there any change that I can format my column to UK datetime. At the moment there is in US format(09/25/2007).

I have a table, one of the column has type datetime.

I use the insert command and the data i want to into is '25/09/2007'

It come back with error that the date is out of range. I change that to 09/09/2007 then it work.
The currently date and time format is MM/DD/YYYY.
I don't know how can i define the format to be DD/MM/YYYY

Thank you very much for your help
i really do appreciate that.

Best regards

Your question is not clear to me .Can you elaborate your question little bit more.
thanx|||Thanks a lot for your reply

I have a table, one of the column has type datetime.

I use the insert command and the data i want to into is '25/09/2007'

It come back with error that the date is out of range. I change that to 09/09/2007 then it work.
The currently date and time format is MM/DD/YYYY.
I don't know how can i define the format to be DD/MM/YYYY

Hope that make it clear.
Once again thanks a lot|||

Have a look at SET DATEFORMAT in Books Online. This sets the order of the dateparts in your date string.

eg

Code Snippet

set dateformat dmy

declare @.datetime datetime

set @.datetime = '12/05/2007'

select datepart(day, @.datetime) as day, datepart(month, @.datetime) as month

set dateformat mdy

declare @.datetime datetime

set @.datetime = '12/05/2007'

select datepart(day, @.datetime) as day, datepart(month, @.datetime) as month

Note, its a good idea to use the ISO format for dates to remove any ambiguity ie 20070512.

HTH!

|||

It's a common misunderstanding that you can insert and store a date into a datetime datatype in a certain format or style.
You can't, so it's actually a waste of time trying to 'force' a specific format such as '12/05/2007' or '20071205' or any other variation.

Whichever format you send to the server for storage, will not be stored in anyting that resembles a date to us humans.

A datetime is stored as two integers internally, it doesn't look like a date at all.

So, whatever date we send, it's not going to be stored in that format.

What is important, though, is how a date is seen and parsed by the server. We want to be sure that there's no misunderstandings - that the server understands the format we send the date in to be the same as we intend.

Just about all formats, such as '12/05/2007' or '05/12/2007' (btw - which date *is* that anyway? may or december?)
are dependant on language and/or datesettings. This is pretty unsafe and opens up for unexpected conversions.

The only 'safe format' is the one mentioned earlier in the thread, ssyymmdd - if you use that as input, you can be sure that nothing weird may happen that cause the date to change. However, it's still stored as two integers...

To answer the question: if you need a special format when displaying a date, you don't do this at insert time, you do it when it's retrieved for display. In T-SQL you use CONVERT with the style parameter of your choice. If you don't use CONVERT and supply a style, then the format you'll see will be in the style that is the default for the tool that is used.

/Kenneth

|||

Kenneth,

Dates can be a little tricky to graspe sometimes due to the varied formats in which they come in. I think the problem is often that people believe what they put in is what they'll get out.

Typically, the actual battle is "how to i get SQL Server to correctly understand my date input/output" and this is where there is a valid use for CONVERT, DATEFORMAT etc. Hence, I used 05/12/2007 specifically to illustrate the point of how DATEFORMAT can effect the output (Of course, its Eddie the Eagle Edwards birthday in Britain )

You're right to point out the difference between storing and parsing dates and the effects of local/global settings and i agree its important to understand this.

Cheers

Format Datetime type in column

Hi
Can you please help me on this matter please?

I am trying to input UK(for example: 25/09/2007 ) format datetime in sql server. Is there any change that I can format my column to UK datetime. At the moment there is in US format(09/25/2007).

I have a table, one of the column has type datetime.

I use the insert command and the data i want to into is '25/09/2007'

It come back with error that the date is out of range. I change that to 09/09/2007 then it work.
The currently date and time format is MM/DD/YYYY.
I don't know how can i define the format to be DD/MM/YYYY

Thank you very much for your help
i really do appreciate that.

Best regards

Your question is not clear to me .Can you elaborate your question little bit more.
thanx|||Thanks a lot for your reply

I have a table, one of the column has type datetime.

I use the insert command and the data i want to into is '25/09/2007'

It come back with error that the date is out of range. I change that to 09/09/2007 then it work.
The currently date and time format is MM/DD/YYYY.
I don't know how can i define the format to be DD/MM/YYYY

Hope that make it clear.
Once again thanks a lot|||

Have a look at SET DATEFORMAT in Books Online. This sets the order of the dateparts in your date string.

eg

Code Snippet

set dateformat dmy

declare @.datetime datetime

set @.datetime = '12/05/2007'

select datepart(day, @.datetime) as day, datepart(month, @.datetime) as month

set dateformat mdy

declare @.datetime datetime

set @.datetime = '12/05/2007'

select datepart(day, @.datetime) as day, datepart(month, @.datetime) as month

Note, its a good idea to use the ISO format for dates to remove any ambiguity ie 20070512.

HTH!

|||

It's a common misunderstanding that you can insert and store a date into a datetime datatype in a certain format or style.
You can't, so it's actually a waste of time trying to 'force' a specific format such as '12/05/2007' or '20071205' or any other variation.

Whichever format you send to the server for storage, will not be stored in anyting that resembles a date to us humans.

A datetime is stored as two integers internally, it doesn't look like a date at all.

So, whatever date we send, it's not going to be stored in that format.

What is important, though, is how a date is seen and parsed by the server. We want to be sure that there's no misunderstandings - that the server understands the format we send the date in to be the same as we intend.

Just about all formats, such as '12/05/2007' or '05/12/2007' (btw - which date *is* that anyway? may or december?)
are dependant on language and/or datesettings. This is pretty unsafe and opens up for unexpected conversions.

The only 'safe format' is the one mentioned earlier in the thread, ssyymmdd - if you use that as input, you can be sure that nothing weird may happen that cause the date to change. However, it's still stored as two integers...

To answer the question: if you need a special format when displaying a date, you don't do this at insert time, you do it when it's retrieved for display. In T-SQL you use CONVERT with the style parameter of your choice. If you don't use CONVERT and supply a style, then the format you'll see will be in the style that is the default for the tool that is used.

/Kenneth

|||

Kenneth,

Dates can be a little tricky to graspe sometimes due to the varied formats in which they come in. I think the problem is often that people believe what they put in is what they'll get out.

Typically, the actual battle is "how to i get SQL Server to correctly understand my date input/output" and this is where there is a valid use for CONVERT, DATEFORMAT etc. Hence, I used 05/12/2007 specifically to illustrate the point of how DATEFORMAT can effect the output (Of course, its Eddie the Eagle Edwards birthday in Britain )

You're right to point out the difference between storing and parsing dates and the effects of local/global settings and i agree its important to understand this.

Cheers

Format date in a Stored Procedure

Dear friends,

I have a stored procedure that returns some fiels. One of the fields is a datetime type.

The field return in the follow format : 2006-11-13 0:00:00

How can I return only 2006-11-13? How can I use the format function?

regards!!!

declare @.someDate datetime
set @.someDate = getdate()
select cast(datepart(yyyy, @.someDate) as varchar) + '-' + cast(datepart(mm, @.someDate) as varchar) + '-' + cast(datepart(dd, @.someDate) as varchar)

result: 2006-11-13

|||

Or...

Select Convert(varchar(10), GetDate(), 120)

Lookup the convert function in Books On Line for more formats.

|||

Here is another method:

select DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))

This keeps the datatype as a datetime while removing the date from the string. It is best practice to format the data in the UI.

|||

Dear friens,

First, let me thank for all your support.

And the last question about this problem, How can get the system current time in format hh:mm? (ex: 12:30)

Thanks

|||You should not care about formatting the date on the server this is a thing for the presentation layer.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||ok, but how I return the time value of the system?|||Do you mean at the presentation layer ? That depends on your used coding language, with .NET you will date various options on the Date type.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

For example: I want to create the follow stored procedure:

CREATE PROCEDURE TEST

@.ID INT

AS

UPDATE TABLE1 SET MyFieldTime=@.MySystemTime WHERE MyFieldID=@.ID

Understood?

I want to save in my database th system time...

Thanks!!

|||Depending on which datatype you use in the column you can′t separate the date and the time. Datetime is a combined type storing date as well as time. Is it against any rules storing the date additionally ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I save the time field in teh database is a nchar(5) as for example: 12:30, 22:30, 08h00

Thanks!!

Format a number to be a date format

I have a number field that looks like this:
8 characters long yyyymmdd
ie: 20051201
I can not change its type to a date field. In Access and Crystal I was able
to manipulate the number to make it look like a date ie: 12/01/2005
Can anyone help me figure out how to format the field like this?
thanks so much
--
Jeanne Conde, MCPWell, i guess it can be achieved by code snipet, pass value as argument
in code block and through VB syntax manipulate string in ne form.
Hope it helps|||The VB.NET function Data.Parse will be the way to go.|||Right click the field, click on properties.
In the 'format' section, choose cuistom and enter dd/mm/yyyy
That should do the trick
"Jeanne Conde" wrote:
> I have a number field that looks like this:
> 8 characters long yyyymmdd
> ie: 20051201
> I can not change its type to a date field. In Access and Crystal I was able
> to manipulate the number to make it look like a date ie: 12/01/2005
> Can anyone help me figure out how to format the field like this?
> thanks so much
> --
> Jeanne Conde, MCP

Form type reports... Possible?

I need to create data reporting forms that does not necesserily have
repeating rows, but are composed of field names (test labels) and values
(read/calculated from database fields) scattered on page (similar to a manual
data entry form). I can do this with Cristal Reports but is this possible
with the SQL Server Reportsing Services? Thank you very much for your
opinions.
DDAbsolutely - just set up you dataset (or datasets) and drag the fields from
the field browser directly onto the page. Add labels and you're done!
"Dursun" wrote:
> I need to create data reporting forms that does not necesserily have
> repeating rows, but are composed of field names (test labels) and values
> (read/calculated from database fields) scattered on page (similar to a manual
> data entry form). I can do this with Cristal Reports but is this possible
> with the SQL Server Reportsing Services? Thank you very much for your
> opinions.
> DD