Formula for Record Calculation

I have created two commands in the database expert. And I want to do the calulation of these records.

My situation is:

In table A, I have fields "Country","Amount" and "Currency".
In table B, I have fields "Currency" and "Exchange".

I want get the sum of "Amount"*"Exchange" if table A "Currency" = table B "Currency" that group by "Country"

How to write this formula?

Thank you fro helping me.Have u linked these commands by currency?|||Please tell me how to linkand what I should do after linking?
Thanks very much|||I have linked the currency field of two commands.

My situation is tableB store the currency of all countries in the world. And some of the countries using same currency. If I use a sql statement to join two table, then the records will be duplicated and cannot get the correct exchange rate of each currency.
So that I want to write a formula that caluclate the tableA amount with correct currency. But I don't know how to get the exchange rate of tableB if tableA.currency=tableB.currency.

Do you have any idea to do that?
thanks alot~~~|||I am using mysql 4.0 which is not supported subquery.

If you have any idea other than my method. Please tell me. This problem spent me a week ago~

Really thanks alot~

Formula for calculated column

Hi,
I'm struggling to get a calculated column to work in sql, the fields to be calculated are:
[AdRevenue_a] money
[Admissions_a] int
[DoorPrice_a] smallmoney
[DoorSplit_a] money
And the calculation I require is:
(AdRevenue_a / ( (Admissions_a * DoorPrice_a) - DoorSplit_a )) * 100
This is what I think it should be but it doesn't work...
convert(decimal(6,2), ((AdRevenue_a / ((Admissions_a * DoorPrice_a) - DoorSplit_a))*100) ))

Any suggestions??

(AdRevenue_a / ( (Admissions_a * DoorPrice_a) - DoorSplit_a )) * 100
should work.
In this:
convert(decimal(6,2), ((AdRevenue_a / ((Admissions_a * DoorPrice_a) - DoorSplit_a))*100) ))
you have an extra bracket at the end and it will work if you remove it.|||That didn't fix the issue.
The problematic field seems to be DoorSplit_a, if this is removed the rest of the calculation works??
|||Do you have any sample data that you can provide. Also are any of the columns nullable? IF so you might need to use ISNULL() appropriately.
If I supplied all the values properly it seemed to work:

DECLARE
@.AdRevenue_amoney,
@.Admissions_aint
,@.DoorPrice_asmallmoney
,@.DoorSplit_amoney

select
@.AdRevenue_a= 100
,@.Admissions_a= 50
,@.DoorPrice_a= 2
,@.DoorSplit_a= 25

select(@.AdRevenue_a/((@.Admissions_a* @.DoorPrice_a)- @.DoorSplit_a))* 100

selectconvert(decimal(6,2),((@.AdRevenue_a/((@.Admissions_a* @.DoorPrice_a)- @.DoorSplit_a))*100))

|||All aggregate functions in SQL Server ignore NULL values except COUNT (*), and the ISNULL function will replace NULL with 0 which could give you wrong numbers if [DoorSplit_a] allows NULL. Try the link below for more info. Hope this helps
http://www.akadia.com/services/dealing_with_null_values.html|||Thanks for the replies.
I'm not even getting as far as the data.
The error is thrown by sql when I try to enter the calculation into the formula box.|||Please post the exact formula you are now trying to enter. As Dinakar pointed out, yourfirst example had a mistmatched number of opening and closingparentheses.
Also, for future questions, please use a more specific term than "doesnot work". An exact description of the error you are encounteringwill go a long way towards pinpointing and correcting your problem.

Formula field vs. no formula field

Dear all,
Sounds like a simple question.
I have three fields in my table
FieldA
FieldB
FieldC
FieldD
FieldC = FieldA * FieldB
FieldD = 0.01 * FieldA * FieldB
(actually the scenario is slightly complex that this)
I have two options
1. Declare FieldC and FieldD as formula fields and relax myself by
bothering only about FieldA and FieldB from my asp.net application
2. Worry about updating FieldC and FieldD as "there could be a serious
performance difference"
Updates happen from different screens, and it would make a deal of
difference to me if i can allow sql to take care of the several formula
fields i want to keep. At the same time, i don't want my customers to
come crying saying the system is very slow.
Any advice?
Thanks
Hi
If your program unnecessarily updates column A and B then you will probably
be better off with a view, although you would really want to fix the
programming! It is not so much where your updates occur, but the number and
performance of inserts/updates/selects which decide which way is better!
It also sounds like you are accessing the tables directly, therefore I would
also recommend using stored procedures.
John
"mich_stone@.yahoo.com" wrote:

> Dear all,
> Sounds like a simple question.
> I have three fields in my table
> FieldA
> FieldB
> FieldC
> FieldD
> FieldC = FieldA * FieldB
> FieldD = 0.01 * FieldA * FieldB
> (actually the scenario is slightly complex that this)
> I have two options
> 1. Declare FieldC and FieldD as formula fields and relax myself by
> bothering only about FieldA and FieldB from my asp.net application
> 2. Worry about updating FieldC and FieldD as "there could be a serious
> performance difference"
> Updates happen from different screens, and it would make a deal of
> difference to me if i can allow sql to take care of the several formula
> fields i want to keep. At the same time, i don't want my customers to
> come crying saying the system is very slow.
> Any advice?
> Thanks
>
|||Defining a formula (computed column) such as:
CREATE TABLE t(c1 int, c2 AS c1 * 100)
doesn't store the calculated value. I.e. no penalty for modifications. However, for below search
argument, SQL Server can (probably) not use an index on c1:
WHERE c2 = 10000
So, you can create an index on a computed column where the value is actually stored. And in this
case, you do pay for modifications.
An alternative to computed columns is to create a view with the calculated values and have your
users SELECT from the view.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<mich_stone@.yahoo.com> wrote in message news:1107240862.212907.54340@.c13g2000cwb.googlegro ups.com...
> Dear all,
> Sounds like a simple question.
> I have three fields in my table
> FieldA
> FieldB
> FieldC
> FieldD
> FieldC = FieldA * FieldB
> FieldD = 0.01 * FieldA * FieldB
> (actually the scenario is slightly complex that this)
> I have two options
> 1. Declare FieldC and FieldD as formula fields and relax myself by
> bothering only about FieldA and FieldB from my asp.net application
> 2. Worry about updating FieldC and FieldD as "there could be a serious
> performance difference"
> Updates happen from different screens, and it would make a deal of
> difference to me if i can allow sql to take care of the several formula
> fields i want to keep. At the same time, i don't want my customers to
> come crying saying the system is very slow.
> Any advice?
> Thanks
>

Formula field vs. no formula field

Dear all,
Sounds like a simple question.
I have three fields in my table
FieldA
FieldB
FieldC
FieldD
FieldC = FieldA * FieldB
FieldD = 0.01 * FieldA * FieldB
(actually the scenario is slightly complex that this)
I have two options
1. Declare FieldC and FieldD as formula fields and relax myself by
bothering only about FieldA and FieldB from my asp.net application
2. Worry about updating FieldC and FieldD as "there could be a serious
performance difference"
Updates happen from different screens, and it would make a deal of
difference to me if i can allow sql to take care of the several formula
fields i want to keep. At the same time, i don't want my customers to
come crying saying the system is very slow.
Any advice?
ThanksHi
If your program unnecessarily updates column A and B then you will probably
be better off with a view, although you would really want to fix the
programming! It is not so much where your updates occur, but the number and
performance of inserts/updates/selects which decide which way is better!
It also sounds like you are accessing the tables directly, therefore I would
also recommend using stored procedures.
John
"mich_stone@.yahoo.com" wrote:

> Dear all,
> Sounds like a simple question.
> I have three fields in my table
> FieldA
> FieldB
> FieldC
> FieldD
> FieldC = FieldA * FieldB
> FieldD = 0.01 * FieldA * FieldB
> (actually the scenario is slightly complex that this)
> I have two options
> 1. Declare FieldC and FieldD as formula fields and relax myself by
> bothering only about FieldA and FieldB from my asp.net application
> 2. Worry about updating FieldC and FieldD as "there could be a serious
> performance difference"
> Updates happen from different screens, and it would make a deal of
> difference to me if i can allow sql to take care of the several formula
> fields i want to keep. At the same time, i don't want my customers to
> come crying saying the system is very slow.
> Any advice?
> Thanks
>|||Defining a formula (computed column) such as:
CREATE TABLE t(c1 int, c2 AS c1 * 100)
doesn't store the calculated value. I.e. no penalty for modifications. Howev
er, for below search
argument, SQL Server can (probably) not use an index on c1:
WHERE c2 = 10000
So, you can create an index on a computed column where the value is actually
stored. And in this
case, you do pay for modifications.
An alternative to computed columns is to create a view with the calculated v
alues and have your
users SELECT from the view.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<mich_stone@.yahoo.com> wrote in message news:1107240862.212907.54340@.c13g2000cwb.googlegroup
s.com...
> Dear all,
> Sounds like a simple question.
> I have three fields in my table
> FieldA
> FieldB
> FieldC
> FieldD
> FieldC = FieldA * FieldB
> FieldD = 0.01 * FieldA * FieldB
> (actually the scenario is slightly complex that this)
> I have two options
> 1. Declare FieldC and FieldD as formula fields and relax myself by
> bothering only about FieldA and FieldB from my asp.net application
> 2. Worry about updating FieldC and FieldD as "there could be a serious
> performance difference"
> Updates happen from different screens, and it would make a deal of
> difference to me if i can allow sql to take care of the several formula
> fields i want to keep. At the same time, i don't want my customers to
> come crying saying the system is very slow.
> Any advice?
> Thanks
>sql

Formula field vs. no formula field

Dear all,
Sounds like a simple question.
I have three fields in my table
FieldA
FieldB
FieldC
FieldD
FieldC = FieldA * FieldB
FieldD = 0.01 * FieldA * FieldB
(actually the scenario is slightly complex that this)
I have two options
1. Declare FieldC and FieldD as formula fields and relax myself by
bothering only about FieldA and FieldB from my asp.net application
2. Worry about updating FieldC and FieldD as "there could be a serious
performance difference"
Updates happen from different screens, and it would make a deal of
difference to me if i can allow sql to take care of the several formula
fields i want to keep. At the same time, i don't want my customers to
come crying saying the system is very slow.
Any advice?
ThanksHi
If your program unnecessarily updates column A and B then you will probably
be better off with a view, although you would really want to fix the
programming! It is not so much where your updates occur, but the number and
performance of inserts/updates/selects which decide which way is better!
It also sounds like you are accessing the tables directly, therefore I would
also recommend using stored procedures.
John
"mich_stone@.yahoo.com" wrote:
> Dear all,
> Sounds like a simple question.
> I have three fields in my table
> FieldA
> FieldB
> FieldC
> FieldD
> FieldC = FieldA * FieldB
> FieldD = 0.01 * FieldA * FieldB
> (actually the scenario is slightly complex that this)
> I have two options
> 1. Declare FieldC and FieldD as formula fields and relax myself by
> bothering only about FieldA and FieldB from my asp.net application
> 2. Worry about updating FieldC and FieldD as "there could be a serious
> performance difference"
> Updates happen from different screens, and it would make a deal of
> difference to me if i can allow sql to take care of the several formula
> fields i want to keep. At the same time, i don't want my customers to
> come crying saying the system is very slow.
> Any advice?
> Thanks
>|||Defining a formula (computed column) such as:
CREATE TABLE t(c1 int, c2 AS c1 * 100)
doesn't store the calculated value. I.e. no penalty for modifications. However, for below search
argument, SQL Server can (probably) not use an index on c1:
WHERE c2 = 10000
So, you can create an index on a computed column where the value is actually stored. And in this
case, you do pay for modifications.
An alternative to computed columns is to create a view with the calculated values and have your
users SELECT from the view.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<mich_stone@.yahoo.com> wrote in message news:1107240862.212907.54340@.c13g2000cwb.googlegroups.com...
> Dear all,
> Sounds like a simple question.
> I have three fields in my table
> FieldA
> FieldB
> FieldC
> FieldD
> FieldC = FieldA * FieldB
> FieldD = 0.01 * FieldA * FieldB
> (actually the scenario is slightly complex that this)
> I have two options
> 1. Declare FieldC and FieldD as formula fields and relax myself by
> bothering only about FieldA and FieldB from my asp.net application
> 2. Worry about updating FieldC and FieldD as "there could be a serious
> performance difference"
> Updates happen from different screens, and it would make a deal of
> difference to me if i can allow sql to take care of the several formula
> fields i want to keep. At the same time, i don't want my customers to
> come crying saying the system is very slow.
> Any advice?
> Thanks
>

Formatting values being passed to Sql

I have a generic page with some fields on it, I would like to be able to "groom" the values so that the data is nice and consistent.

Example: input 1 -- user types in "bob" or "BOB" or "Bob" or "JOE" etc...

the resulting record in Sql would be "Bob", "Fred", "Joe"
nice and formted out.

Any suggestions?string str1, str2 , formattedname
str1 = (name.ToUpper()).SubString(0,1)
str2 = (name.ToLower()).SubString(1)

formattedname = String.concat(str1,str2)

wher name is the string that contains the name to format. hope this helps

Formatting Question

I have an old DB2 app that has date values in 4 fields (i.e. Date1_MO, Date1_DA, Date1_CN, Date1_YR). I have several MSAccess queries that I convert this to a date by doing the following:

CDate(Date1_MO & "/" & Date1_DA & "/" & Date1_CN & Format(Date1_YR,"00"))

Piece of cake...however I am struggling with this in MSSQL.

Mostly I am fighting formatting the Year. As you can see, If I were to concatinate the above values i would come up with something like 3/9/204 for a date of March 9, 2004. (Each field is a numeric value).

I have gotten this far...

select CAST(Date1_MO as varchar(2))+ '/' + CAST(Date1_DA as varchar(2))
+ '/' + CAST(Date1_CN as varchar(2))+ CAST(Date1_YR as varchar(2)) as Date1
From tPrices

I still need to convert the whole string to a date, but more importantly, I cannot figure out how to get the last element (Year) to format as '04' instead of '4'. I can't concatinate a 0 in front of it for obvious reasons. (Athough I was tempted, just joking)

I looked through a lot of the T-SQL docs but have come up dry.

Anyway HELP!!!!!!Try this for the last 2 digits of the year:

right('0'+CAST(Date1_YR as varchar(2)), 2)|||Came to the same conclusion about the same time you replied...

Just playing with the conversion now.

Thanks for your response...

Formatting Parameter Values

I want to enter part of a parameter value as an expression similar to
formatting a global fields e.g. Format(Globals.ExecutionTime, "D").
My parameter is returning XXXX 2006, but I only want the year part of
the parameter passed to the expression.
Can this be done.?If it's a date, then =Datepart(DateInterval.Year, Parameters!myParm.Value)
or you could do the following:
=DatePart(DateInterval.Year,
DateTime.Parse(Parameters!myParam.Value.ToString()))
You could also do this:
=Parameters!myParam.Value.ToString().SubString(4,4) /* assuming 0-based
strings */
"Andy" <andy.williams1971@.ntlworld.com> wrote in message
news:1152626782.474747.226720@.35g2000cwc.googlegroups.com...
>I want to enter part of a parameter value as an expression similar to
> formatting a global fields e.g. Format(Globals.ExecutionTime, "D").
> My parameter is returning XXXX 2006, but I only want the year part of
> the parameter passed to the expression.
> Can this be done.?
>|||This worked a treat!
Thanks
Tim Dot NoSpam wrote:
> If it's a date, then =Datepart(DateInterval.Year, Parameters!myParm.Value)
> or you could do the following:
> =DatePart(DateInterval.Year,
> DateTime.Parse(Parameters!myParam.Value.ToString()))
> You could also do this:
> =Parameters!myParam.Value.ToString().SubString(4,4) /* assuming 0-based
> strings */
> "Andy" <andy.williams1971@.ntlworld.com> wrote in message
> news:1152626782.474747.226720@.35g2000cwc.googlegroups.com...
> >I want to enter part of a parameter value as an expression similar to
> > formatting a global fields e.g. Format(Globals.ExecutionTime, "D").
> >
> > My parameter is returning XXXX 2006, but I only want the year part of
> > the parameter passed to the expression.
> >
> > Can this be done.?
> >|||No worries. Remember that [almost] whatever you can do in VB.NET, you can
do in RS...
-Tim
"Andy" <andy.williams1971@.ntlworld.com> wrote in message
news:1152630530.714237.206660@.h48g2000cwc.googlegroups.com...
> This worked a treat!
> Thanks
>
> Tim Dot NoSpam wrote:
>> If it's a date, then =Datepart(DateInterval.Year,
>> Parameters!myParm.Value)
>> or you could do the following:
>> =DatePart(DateInterval.Year,
>> DateTime.Parse(Parameters!myParam.Value.ToString()))
>> You could also do this:
>> =Parameters!myParam.Value.ToString().SubString(4,4) /* assuming 0-based
>> strings */
>> "Andy" <andy.williams1971@.ntlworld.com> wrote in message
>> news:1152626782.474747.226720@.35g2000cwc.googlegroups.com...
>> >I want to enter part of a parameter value as an expression similar to
>> > formatting a global fields e.g. Format(Globals.ExecutionTime, "D").
>> >
>> > My parameter is returning XXXX 2006, but I only want the year part of
>> > the parameter passed to the expression.
>> >
>> > Can this be done.?
>> >
>

Formatting numeric fields in select-clause

This is propably a very simple question, but I can′t seem to find the answer
to it in the documentation.
I want to format a numeric field so the result is right justified and
zero-filled.
ex select 1 will give the result 01
How do I manage this simple task?
best reguards from sunny Sweden
Magnus B
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.mseq:8236
On Mon, 14 Feb 2005 08:13:01 -0800, Magnus Broman wrote:

>This is propably a very simple question, but I cant seem to find the answer
>to it in the documentation.
>I want to format a numeric field so the result is right justified and
>zero-filled.
>ex select 1 will give the result 01
>How do I manage this simple task?
Hi Magnus,
Formatting is usually done by the presentation layer. In fact, the format
SQL Server uses to send numeric values to the client software is quite
different from what you'll ever see on your screen <g>.
If you have valid reasons for doing the formatting at the server, you can;
but you should be aware that the data is then no longer numeric: after
formatting, it's string data.
DECLARE @.number int
SET @.number = 1
SELECT RIGHT('00' + CAST(@.number AS varchar(2)), 2)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Formatting Issues

Is there a way to create a report where the data appears vertically in
multiple "columns" with lines around each column and multiple fields in each
column of differing data lengths.
I can do this in Crystal Reports but can't seem to find a way to do it in SQL.
The problem is that the data in one column affects the data in another
column as far as the formatting columns. I get extra white space.
I can't concantenate all of the data together because I need to bold the
labels.
Here is what I want:
ColumnHeader1 ColumnHeader2
some data Label 1:
vs. This data will go past
other data one line
Label 2:
This data can also be
multiple lines
Here is what I get:
ColumnHeader1 ColumnHeader2
some data Label 1:
vs. This data will go past
one line
other data
Label 2:
This data can also be
multiple lines
Because the 2nd row in ColumnHeader2 is 2 lines it pushes the data in the
2nd row in ColumnHeader1 down 2 lines as well.
I have tried using tables, list boxes, and subreports. None of them work .
. . especially with trying to add lines around each column. The top line
will appear only on the first page and the bottom line only on the last page.
The left and right border lines also get messed up.On Dec 7, 5:14 pm, Orne <polysilly...@.yahoo.com> wrote:
> On Dec 7, 2:57 pm, Anonymous <Anonym...@.discussions.microsoft.com>
> wrote:
>
> > Is there a way to create a report where the data appears vertically in
> > multiple "columns" with lines around each column and multiple fields in each
> > column of differing data lengths.
> > I can do this in Crystal Reports but can't seem to find a way to do it in SQL.
> > The problem is that the data in one column affects the data in another
> > column as far as the formatting columns. I get extra white space.
> > I can't concantenate all of the data together because I need to bold the
> > labels.
> > Here is what I want:
> > ColumnHeader1 ColumnHeader2
> > some data Label 1:
> > vs. This data will go past
> > other data one line
> > Label 2:
> > This data can also be
> > multiple lines
> > Here is what I get:
> > ColumnHeader1 ColumnHeader2
> > some data Label 1:
> > vs. This data will go past
> > one line
> > other data
> > Label 2:
> > This data can also be
> > multiple lines
> > Because the 2nd row in ColumnHeader2 is 2 lines it pushes the data in the
> > 2nd row in ColumnHeader1 down 2 lines as well.
> > I have tried using tables, list boxes, and subreports. None of them work .
> > . . especially with trying to add lines around each column. The top line
> > will appear only on the first page and the bottom line only on the last page.
> > The left and right border lines also get messed up.
> Create two Rectangle objects side-by-side, then drag all of your
> Column 1 data into the left Rectangle, and drag all of the Column 2
> data into the right rectangle. Make sure that the two are Top
> aligned, and the right of the first rectangle is against the left of
> the second rectangle.
> Rectangles act like DIV tags in HTML, everything inside them will be
> rendered in its own space.
> -- Scott
I agree with Scott. I just wanted to mention that to add report based
columns, in case you don't know, you would do the following:
Click the Layout tab in BIDS >> select the Report drop-down at the top
>> select Report Properties... >> select the Layout tab >> select the
number of Columns and spacing between them.
Of course, if used in this scenario could yield negative formatting
results; however, it could be considered.
Regards,
Enrique Martinez
Sr. Software Consultant|||I created the rectangles but the report only shows the first record. Is
there something else I need to do or will this not work for what I want to do?
There are 53 records and the report should show 1 record per page.
"Orne" wrote:
> On Dec 7, 2:57 pm, Anonymous <Anonym...@.discussions.microsoft.com>
> wrote:
> > Is there a way to create a report where the data appears vertically in
> > multiple "columns" with lines around each column and multiple fields in each
> > column of differing data lengths.
> >
> > I can do this in Crystal Reports but can't seem to find a way to do it in SQL.
> >
> > The problem is that the data in one column affects the data in another
> > column as far as the formatting columns. I get extra white space.
> >
> > I can't concantenate all of the data together because I need to bold the
> > labels.
> >
> > Here is what I want:
> >
> > ColumnHeader1 ColumnHeader2
> > some data Label 1:
> > vs. This data will go past
> > other data one line
> >
> > Label 2:
> > This data can also be
> > multiple lines
> >
> > Here is what I get:
> >
> > ColumnHeader1 ColumnHeader2
> > some data Label 1:
> > vs. This data will go past
> > one line
> > other data
> > Label 2:
> > This data can also be
> > multiple lines
> >
> > Because the 2nd row in ColumnHeader2 is 2 lines it pushes the data in the
> > 2nd row in ColumnHeader1 down 2 lines as well.
> >
> > I have tried using tables, list boxes, and subreports. None of them work .
> > . . especially with trying to add lines around each column. The top line
> > will appear only on the first page and the bottom line only on the last page.
> > The left and right border lines also get messed up.
> Create two Rectangle objects side-by-side, then drag all of your
> Column 1 data into the left Rectangle, and drag all of the Column 2
> data into the right rectangle. Make sure that the two are Top
> aligned, and the right of the first rectangle is against the left of
> the second rectangle.
> Rectangles act like DIV tags in HTML, everything inside them will be
> rendered in its own space.
> -- Scott
>|||On Dec 10, 12:18 pm, Anonymous <Anonym...@.discussions.microsoft.com>
wrote:
> I created the rectangles but the report only shows the first record. Is
> there something else I need to do or will this not work for what I want to do?
> There are 53 records and the report should show 1 record per page.
>
> "Orne" wrote:
> > On Dec 7, 2:57 pm, Anonymous <Anonym...@.discussions.microsoft.com>
> > wrote:
> > > Is there a way to create a report where the data appears vertically in
> > > multiple "columns" with lines around each column and multiple fields in each
> > > column of differing data lengths.
> > > I can do this in Crystal Reports but can't seem to find a way to do it in SQL.
> > > The problem is that the data in one column affects the data in another
> > > column as far as the formatting columns. I get extra white space.
> > > I can't concantenate all of the data together because I need to bold the
> > > labels.
> > > Here is what I want:
> > > ColumnHeader1 ColumnHeader2
> > > some data Label 1:
> > > vs. This data will go past
> > > other data one line
> > > Label 2:
> > > This data can also be
> > > multiple lines
> > > Here is what I get:
> > > ColumnHeader1 ColumnHeader2
> > > some data Label 1:
> > > vs. This data will go past
> > > one line
> > > other data
> > > Label 2:
> > > This data can also be
> > > multiple lines
> > > Because the 2nd row in ColumnHeader2 is 2 lines it pushes the data in the
> > > 2nd row in ColumnHeader1 down 2 lines as well.
> > > I have tried using tables, list boxes, and subreports. None of them work .
> > > . . especially with trying to add lines around each column. The top line
> > > will appear only on the first page and the bottom line only on the last page.
> > > The left and right border lines also get messed up.
> > Create two Rectangle objects side-by-side, then drag all of your
> > Column 1 data into the left Rectangle, and drag all of the Column 2
> > data into the right rectangle. Make sure that the two are Top
> > aligned, and the right of the first rectangle is against the left of
> > the second rectangle.
> > Rectangles act like DIV tags in HTML, everything inside them will be
> > rendered in its own space.
> > -- Scott- Hide quoted text -
> - Show quoted text -
Oh, umm, I assumed you were using Table objects, not TextBox. The
table will repeat whatever is in its details section for every record
in your DataSet. Check the expression in your boxes; if it says
=First( Fields!... ) then it will only return the data in the first
record.
If you want one record per page, create a List object, and drag the
items into the list. right click on it and go to the List Properties,
and check the Page Break at end checkbox. I think that should create
your one record per page.
-- Scott|||On Dec 7, 2:57 pm, Anonymous <Anonym...@.discussions.microsoft.com>
wrote:
> Is there a way to create a report where the data appears vertically in
> multiple "columns" with lines around each column and multiple fields in each
> column of differing data lengths.
> I can do this in Crystal Reports but can't seem to find a way to do it in SQL.
> The problem is that the data in one column affects the data in another
> column as far as the formatting columns. I get extra white space.
> I can't concantenate all of the data together because I need to bold the
> labels.
> Here is what I want:
> ColumnHeader1 ColumnHeader2
> some data Label 1:
> vs. This data will go past
> other data one line
> Label 2:
> This data can also be
> multiple lines
> Here is what I get:
> ColumnHeader1 ColumnHeader2
> some data Label 1:
> vs. This data will go past
> one line
> other data
> Label 2:
> This data can also be
> multiple lines
> Because the 2nd row in ColumnHeader2 is 2 lines it pushes the data in the
> 2nd row in ColumnHeader1 down 2 lines as well.
> I have tried using tables, list boxes, and subreports. None of them work .
> . . especially with trying to add lines around each column. The top line
> will appear only on the first page and the bottom line only on the last page.
> The left and right border lines also get messed up.
Create two Rectangle objects side-by-side, then drag all of your
Column 1 data into the left Rectangle, and drag all of the Column 2
data into the right rectangle. Make sure that the two are Top
aligned, and the right of the first rectangle is against the left of
the second rectangle.
Rectangles act like DIV tags in HTML, everything inside them will be
rendered in its own space.
-- Scott

Formatting for Phone?

In my report I am combing 2 fields as

=Fields!PROPERTY.Value & " - " & Fields!PHONE.Value

this results in PropertyName - 5555551212

How would I add in formatting for phone so my resulting display is (555) 555-1212?

Jim Seidel wrote:

In my report I am combing 2 fields as

=Fields!PROPERTY.Value & " - " & Fields!PHONE.Value

this results in PropertyName - 5555551212

How would I add in formatting for phone so my resulting display is (555) 555-1212?

I'm doing this from memory, but something like this:

= "(" & Left(Fields!PHONE.Value,3) & ") " & Right(Left(Fields!PHONE.Value,6),3) & "-" & Right(Fields!PHONE.Value,4)

The full expression would be this:

= Fields!PROPERTY.Value & " - " & "(" & Left(Fields!PHONE.Value,3) & ") " & Right(Left(Fields!PHONE.Value,6),3) & "-" & Right(Fields!PHONE.Value,4)|||Awesome Memory, it worked perfectly !|||Great, glad I could help. You'll get used to it. Does it make sense?

Getting the middle characters is a little awkward. I can't remember whether there is a Mid function defined or not, so I just use that.|||Yes, like you say, it just comes with practice

Formatting a string with color/bold?

I have a string in which I am combining several fields. Is there a way to add formatting to the string as well

="Baud: " & Fields!BAUDRATE.Value & " DL Status: " & Fields!COMMSTATUS.Value

Example, I want my fields to appear in bold and string in normal.. I can't do this in an individual textbox due to space constraints

I think you are suggesting mixed formatting within a textbox which is not possible. When applying a format style it applies to the entire contents of a textbox.

Formating Date Display

How to I set the =User!Language on a Reporting Services report to display
based on the locale of the user. I'd like to make sure date fields are
displayed using the correct format.On Nov 19, 1:48 am, Greg Larsen <gregalar...@.removeit.msn.com> wrote:
> How to I set the =User!Language on a Reporting Services report to display
> based on the locale of the user. I'd like to make sure date fields are
> displayed using the correct format.
I found an example online that shows it should be something like:
lblTime.Text = String.Format("{0:T}", rightNow);
lblDate.Text = String.Format("{0:d}", rightNow);
-- Scott

FormatCurrency in 2k5 Report Services

I have a test report, unformatted the value one of my fields is -29.5600

When I apply the format: FormatCurrency(Fields!Paid_Amount.Value) the value shows as -($293056). Another sample is -62.2400 which converts to -($626224).

It seems the leading digits are being repeated for some reason. I've tried playing with rounding, the optional tristate paramaters, and regional settings to get the correct output, but have had no luck. Can anyone be of assistance?Figured it out. Seems I have to use: "$#,###.##"|||Another way for formatting numeric values is to use .NET formatcodes in the "Format" property of the textbox (Note: the textbox value expression has to evaluate to a numeric value otherwise the format code has no effect).

The format code for currency would be: C

MSDN has additional information about standard and custom format strings for numeric values:
* http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp
* http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp

--Robert

format when taking two fields away from other

hi there
can someone show me how to format the following please
=(Fields!SLAResolutionDate.Value)-(Fields!CreatedDate.Value)
these are both date/time fields - I want to format to just day - two
digits and just time - two digits
so like this =Format(Fields!SLAResolutionDate.Value)-(Fields!
CreatedDate.Value), dd, hh
but I am unsure where to stop and start the brackets to get the
result.
thanks
jewelNot sure if this is the simplest, but it will work.
Try this:
'This first part finds only the "whole days". The second part determines
the "left over hours".
Format(DATEDIFF(d,
Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
Format(DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
24 - DATEDIFF(hh,
Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00")
Michael
"jewelfire" wrote:
> hi there
> can someone show me how to format the following please
> =(Fields!SLAResolutionDate.Value)-(Fields!CreatedDate.Value)
> these are both date/time fields - I want to format to just day - two
> digits and just time - two digits
>
> so like this =Format(Fields!SLAResolutionDate.Value)-(Fields!
> CreatedDate.Value), dd, hh
> but I am unsure where to stop and start the brackets to get the
> result.
> thanks
> jewel
>|||Correction: the second part should be reversed (hours - days*24):
Format(DATEDIFF(d,
Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
Format(DATEDIFF(hh,
Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value)-
DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
24,"00")
"Michael C" wrote:
> Not sure if this is the simplest, but it will work.
> Try this:
> 'This first part finds only the "whole days". The second part determines
> the "left over hours".
> Format(DATEDIFF(d,
> Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
> Format(DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
> 24 - DATEDIFF(hh,
> Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00")
> Michael
> "jewelfire" wrote:
> > hi there
> >
> > can someone show me how to format the following please
> >
> > =(Fields!SLAResolutionDate.Value)-(Fields!CreatedDate.Value)
> >
> > these are both date/time fields - I want to format to just day - two
> > digits and just time - two digits
> >
> >
> >
> > so like this =Format(Fields!SLAResolutionDate.Value)-(Fields!
> > CreatedDate.Value), dd, hh
> >
> > but I am unsure where to stop and start the brackets to get the
> > result.
> >
> > thanks
> > jewel
> >
> >|||thanks Michael
I get the error 'd' is not declared.
I am putting this in the expression on the layout view.
thanks
jewel
On May 9, 4:29 am, Michael C <Micha...@.discussions.microsoft.com>
wrote:
> Correction: the second part should be reversed (hours - days*24):
> Format(DATEDIFF(d,
> Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
> Format(DATEDIFF(hh,
> Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value)-
> DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
> 24,"00")
>
> "Michael C" wrote:
> > Not sure if this is the simplest, but it will work.
> > Try this:
> > 'This first part finds only the "whole days". The second part determines
> > the "left over hours".
> > Format(DATEDIFF(d,
> > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
> > Format(DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
> > 24 - DATEDIFF(hh,
> > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00")
> > Michael
> > "jewelfire" wrote:
> > > hi there
> > > can someone show me how to format the following please
> > > =(Fields!SLAResolutionDate.Value)-(Fields!CreatedDate.Value)
> > > these are both date/time fields - I want to format to just day - two
> > > digits and just time - two digits
> > > so like this =Format(Fields!SLAResolutionDate.Value)-(Fields!
> > > CreatedDate.Value), dd, hh
> > > but I am unsure where to stop and start the brackets to get the
> > > result.
> > > thanks
> > > jewel- Hide quoted text -
> - Show quoted text -|||Try using dd instead of just d, and be sure there are no quotes around it.
Here is the MSDN file for valid entries!
http://msdn2.microsoft.com/en-us/library/ms189794.aspx
"jewelfire" wrote:
> thanks Michael
> I get the error 'd' is not declared.
> I am putting this in the expression on the layout view.
> thanks
> jewel
>
> On May 9, 4:29 am, Michael C <Micha...@.discussions.microsoft.com>
> wrote:
> > Correction: the second part should be reversed (hours - days*24):
> >
> > Format(DATEDIFF(d,
> > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
> > Format(DATEDIFF(hh,
> > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value)-
> > DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
> > 24,"00")
> >
> >
> >
> > "Michael C" wrote:
> > > Not sure if this is the simplest, but it will work.
> >
> > > Try this:
> > > 'This first part finds only the "whole days". The second part determines
> > > the "left over hours".
> >
> > > Format(DATEDIFF(d,
> > > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
> > > Format(DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
> > > 24 - DATEDIFF(hh,
> > > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00")
> >
> > > Michael
> >
> > > "jewelfire" wrote:
> >
> > > > hi there
> >
> > > > can someone show me how to format the following please
> >
> > > > =(Fields!SLAResolutionDate.Value)-(Fields!CreatedDate.Value)
> >
> > > > these are both date/time fields - I want to format to just day - two
> > > > digits and just time - two digits
> >
> > > > so like this =Format(Fields!SLAResolutionDate.Value)-(Fields!
> > > > CreatedDate.Value), dd, hh
> >
> > > > but I am unsure where to stop and start the brackets to get the
> > > > result.
> >
> > > > thanks
> > > > jewel- Hide quoted text -
> >
> > - Show quoted text -
>
>|||hi Michael
tried as below
=Format(DATEDIFF(dd,Fields!SLAResolutionDate.Value,Fields!
CreatedDate.Value),"00") & " " &
Format(DATEDIFF(hh,Fields!SLAResolutionDate.Value,Fields!
CreatedDate.Value)-
DATEDIFF(dd, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value)
*
24,"00")
dd - this is underlined and in red but the 2nd instance is not.
showing red on
"00") & " " &
"00")
probably something very simple! thanks for your help
thanks
jewel
On May 10, 4:24 am, Michael C <Micha...@.discussions.microsoft.com>
wrote:
> Try using dd instead of just d, and be sure there are no quotes around it.
> Here is the MSDN file for valid entries!
> http://msdn2.microsoft.com/en-us/library/ms189794.aspx
>
> "jewelfire" wrote:
> > thanks Michael
> > I get the error 'd' is not declared.
> > I am putting this in the expression on the layout view.
> > thanks
> > jewel
> > On May 9, 4:29 am, Michael C <Micha...@.discussions.microsoft.com>
> > wrote:
> > > Correction: the second part should be reversed (hours - days*24):
> > > Format(DATEDIFF(d,
> > > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
> > > Format(DATEDIFF(hh,
> > > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value)-
> > > DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
> > > 24,"00")
> > > "Michael C" wrote:
> > > > Not sure if this is the simplest, but it will work.
> > > > Try this:
> > > > 'This first part finds only the "whole days". The second part determines
> > > > the "left over hours".
> > > > Format(DATEDIFF(d,
> > > > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
> > > > Format(DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
> > > > 24 - DATEDIFF(hh,
> > > > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00")
> > > > Michael
> > > > "jewelfire" wrote:
> > > > > hi there
> > > > > can someone show me how to format the following please
> > > > > =(Fields!SLAResolutionDate.Value)-(Fields!CreatedDate.Value)
> > > > > these are both date/time fields - I want to format to just day - two
> > > > > digits and just time - two digits
> > > > > so like this =Format(Fields!SLAResolutionDate.Value)-(Fields!
> > > > > CreatedDate.Value), dd, hh
> > > > > but I am unsure where to stop and start the brackets to get the
> > > > > result.
> > > > > thanks
> > > > > jewel- Hide quoted text -
> > > - Show quoted text -- Hide quoted text -
> - Show quoted text -

Format Files - Bcp

Hi,
I want to write a BCP format file, which uses one data column Value to map to
Two Column Fields in the table.
For Example:
Data File: Pin Value â' â'5600055â'
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MVThis type of functionality is best done with DTS.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value â' â'5600055â'
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV|||HI Tom,
But I want this on BCP not on DTS.
Regards
Govardhan MV
"Tom Moreau" wrote:
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value â' â'5600055â'
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||What are the datatypes and what are the widths, i.e. how do you map the
5600055 to the two values?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
This type of functionality is best done with DTS.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value â' â'5600055â'
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV|||Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:
> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value â' â'5600055â'
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Yes you can define a fixed length file with fixed length fields.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value - "5600055"
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Hi Wayne Snyder,
How do we write the format file for this,
Can you please explane me in detail.
Data File contain
560030
Table abc
a1
b1
are teh columns
I want to load 560030 to a1, b1 using BCP . please Help me in writting
Format file for this.
Regards
Govardhan MV
"Wayne Snyder" wrote:
> Yes you can define a fixed length file with fixed length fields.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> > Hi,
> >
> > I want to write a BCP format file, which uses one data column Value to map
> > to
> > Two Column Fields in the table.
> >
> > For Example:
> > Data File: Pin Value - "5600055"
> > Table Columns
> > 1) tmp_pin
> > 2) per_pin
> >
> > Is the above possible to specify in the format file? Does this flexibility
> > is provided by BCP Utility to load the data.
> >
> > Regards
> > Govardhan MV
> >
>
>|||That's nice. What do you mean by "same" - int, char(5)... ? How about
giving us DDL and how you want the string 5600055 to be divided up?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:18508099-5AA8-4C0C-9444-B6198F3DC099@.microsoft.com...
Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:
> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value â' â'5600055â'
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
>> What are the datatypes and what are the widths, i.e. how do you map
>> the 5600055 to the two values?
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> ..
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
>> This type of functionality is best done with DTS.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> ..
>> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in
>> message news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
>> Hi,
>> I want to write a BCP format file, which uses one data column Value
>> to map to
>> Two Column Fields in the table.
>> For Example:
>> Data File: Pin Value â' â'5600055â'
>> Table Columns
>> 1) tmp_pin
>> 2) per_pin
>> Is the above possible to specify in the format file? Does this
>> flexibility is provided by BCP Utility to load the data.
>> Regards
>> Govardhan MV|||If that's the original intent, I'd wonder myself. Let's get some real specs
and then we can figure it out.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:uYu%23CUcZFHA.1412@.TK2MSFTNGP12.phx.gbl...
Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
>> What are the datatypes and what are the widths, i.e. how do you map
>> the 5600055 to the two values?
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> ..
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
>> This type of functionality is best done with DTS.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> ..
>> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in
>> message news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
>> Hi,
>> I want to write a BCP format file, which uses one data column Value
>> to map to
>> Two Column Fields in the table.
>> For Example:
>> Data File: Pin Value â' â'5600055â'
>> Table Columns
>> 1) tmp_pin
>> 2) per_pin
>> Is the above possible to specify in the format file? Does this
>> flexibility is provided by BCP Utility to load the data.
>> Regards
>> Govardhan MV

Format Files - Bcp

Hi,
I want to write a BCP format file, which uses one data column Value to map to
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV
This type of functionality is best done with DTS.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV
|||HI Tom,
But I want this on BCP not on DTS.
Regards
Govardhan MV
"Tom Moreau" wrote:

> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>
|||What are the datatypes and what are the widths, i.e. how do you map the
5600055 to the two values?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
This type of functionality is best done with DTS.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV
|||Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:

> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>
|||Yes you can define a fixed length file with fixed length fields.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value - "5600055"
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>
|||Hi Wayne Snyder,
How do we write the format file for this,
Can you please explane me in detail.
Data File contain
560030
Table abc
a1
b1
are teh columns
I want to load 560030 to a1, b1 using BCP . please Help me in writting
Format file for this.
Regards
Govardhan MV
"Wayne Snyder" wrote:

> Yes you can define a fixed length file with fixed length fields.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
>
>
|||That's nice. What do you mean by "same" - int, char(5)... ? How about
giving us DDL and how you want the string 5600055 to be divided up?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:18508099-5AA8-4C0C-9444-B6198F3DC099@.microsoft.com...
Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:

> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>
|||Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
[vbcol=seagreen]
|||If that's the original intent, I'd wonder myself. Let's get some real specs
and then we can figure it out.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:uYu%23CUcZFHA.1412@.TK2MSFTNGP12.phx.gbl...
Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
[vbcol=seagreen]

Format Files - Bcp

Hi,
I want to write a BCP format file, which uses one data column Value to map t
o
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MVThis type of functionality is best done with DTS.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV|||HI Tom,
But I want this on BCP not on DTS.
Regards
Govardhan MV
"Tom Moreau" wrote:

> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||What are the datatypes and what are the widths, i.e. how do you map the
5600055 to the two values?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
This type of functionality is best done with DTS.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV|||Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:

> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Yes you can define a fixed length file with fixed length fields.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value - "5600055"
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Hi Wayne Snyder,
How do we write the format file for this,
Can you please explane me in detail.
Data File contain
560030
Table abc
a1
b1
are teh columns
I want to load 560030 to a1, b1 using BCP . please Help me in writting
Format file for this.
Regards
Govardhan MV
"Wayne Snyder" wrote:

> Yes you can define a fixed length file with fixed length fields.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
>
>|||That's nice. What do you mean by "same" - int, char(5)... ? How about
giving us DDL and how you want the string 5600055 to be divided up?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:18508099-5AA8-4C0C-9444-B6198F3DC099@.microsoft.com...
Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:

> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
[vbcol=seagreen]
>|||If that's the original intent, I'd wonder myself. Let's get some real specs
and then we can figure it out.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:uYu%23CUcZFHA.1412@.TK2MSFTNGP12.phx.gbl...
Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
[vbcol=seagreen]
>

Format Decimal number conditionally

My 'Hour' Fields are retrieved from stored procedure always with 1 decimal
place.
(ie: 2.0 hours, 3.5 hours)
I wish to display, (ie: 2, 3.5 respectively), the first value (2.0) without
the ".0".
So far I have this solution:
=IIF(Abs(Fields!Hours.Value) < Fields!Hours.Value, Fields!Hours.Value ,
Abs(Fields!Hours.Value) )
Is there a better solution? thanks!I found an even simpler solution:
I changed the Custom Format of my table cell to:
=Format(Cdbl(Fields!Hours.Value))
"SQT" wrote:
> My 'Hour' Fields are retrieved from stored procedure always with 1 decimal
> place.
> (ie: 2.0 hours, 3.5 hours)
> I wish to display, (ie: 2, 3.5 respectively), the first value (2.0) without
> the ".0".
> So far I have this solution:
> =IIF(Abs(Fields!Hours.Value) < Fields!Hours.Value, Fields!Hours.Value ,
> Abs(Fields!Hours.Value) )
> Is there a better solution? thanks!
>|||Apology.
Do not use the Custom Format.
Change the Text Value Property to, ie: =Format(Cdbl(Fields!Hours.Value))
"SQT" wrote:
> I found an even simpler solution:
> I changed the Custom Format of my table cell to:
> =Format(Cdbl(Fields!Hours.Value))
>
> "SQT" wrote:
> >
> > My 'Hour' Fields are retrieved from stored procedure always with 1 decimal
> > place.
> > (ie: 2.0 hours, 3.5 hours)
> >
> > I wish to display, (ie: 2, 3.5 respectively), the first value (2.0) without
> > the ".0".
> > So far I have this solution:
> > =IIF(Abs(Fields!Hours.Value) < Fields!Hours.Value, Fields!Hours.Value ,
> > Abs(Fields!Hours.Value) )
> >
> > Is there a better solution? thanks!
> >

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!!