Format Text Box With Parentheses If Negative
May 24, 2007The users want to see negative values like this:
(-$1,000)
How do I format negative values that way without some major iif statement?
ADVERTISEMENT
Please Help: HOW TO FORMAT CURRENCY TO NEGATIVE AMOUNT
Aug 23, 2004Please help!!!
I'm building a database that I have a form. This form allows user input the invoice amount field which is the currency field. My ultimate goal is allow user put in the postive numbers and it will store as negative amount. Please let me know if there is the way to do that.
Thanks in advance.
Linh
Can't Convert Negative Numbers From String Format
Oct 11, 2007Hello,
I'm having problems with a column of numeric string data coming from a tab delimited CSV file. When a number in the column is negative, it is expressed this way; 1,240.52-
The negative symbol occurs at the end of the numeric string. The destination column is in a SQL Server 2005 table. I've tried to change the data type of the table column, and three or four different data types in a data conversion task, but nothing has worked so far to bring the data over.
Any ideas?
Thank you for your help!
cdun2
Using Pipe Like Symbols To Add Parentheses??
Nov 20, 2007Hi all,
I was told recently that doing the following:
SELECT ProductName ||' , '|| Category FROM PRODUCTS
would add parentheses around the ProductName field data. However, after trying it out for myself I found it did no such thing.
Does this look familiar to anyone? Is it syntax I have wrong or something?
Butterfly82
Parentheses In A Check Constraint
Jun 10, 2007Can we use parentheses in a check constraint in MS-SQL-server DDL?e.g. I'm having a problem with the following statement:ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [CK_MyTable_TimesDataOK]CHECK (([TimeOn] IS NULL AND [TimeOff] IS NULL)OR([ShiftCode] IS NOT NULL AND [TimeOn] IS NOT NULL AND [TimeOff] IS NOTNULL));The statement appears to run fine, but when I look at my tabledefinition afterwards, it appears that SQL-server ignored theparentheses in my constraint; it shows the constraint expression as:(([TimeOn] IS NULL AND [TimeOff] IS NULL OR [ShiftCode] IS NOT NULL AND[TimeOn] IS NOT NULL AND [TimeOff] IS NOT NULL))My intention is that if there's (non null) data in either of the columnsTimeOn or TimeOff is not null, all three of the columns TimeOn, TimeOffand ShiftCode must have non null data.OK, I realise I could enforce this by altering my table setup in otherways. Right now I'm just trying to figure out if this I'm just upagainst a difference between dialects of SQL in check constraints here.Am I missing something obvious with parentheses?BTW the DDL for the table I'm testing on:CREATE TABLE [dbo].[MyTable]([FNname] [nvarchar](50) NOT NULL,[ShiftDate] [datetime] NOT NULL,[ShiftCode] [nchar](2) NULL,[TimeOn] [nchar](4) NULL,[TimeOff] [nchar](4) NULL);
View 3 Replies View RelatedNeed SQL Help - Trim Parentheses In Column Of Data
Sep 6, 2006Hi All, I am sure someone has done this before. Trying to write a SQL statement to UPDATE a column named Prod_Model. The table name is tbl_MASTER. Wanting to trim the parentheses out of the data and update back to the table. Any help on the SQL statement?
View 2 Replies View RelatedIs It Possible To Format (Text) In This Way
Jun 14, 2007hi,
i want to display something is this fashion
Roth 401(k) Contribution
jfkajfkdjfdjfkdj ldkfdlkfl;dkfldkfld;fkdl
in a table row, but i am not sure how to do it. can some one pls give me some ideas.
Regards,
Karen
Convert Text File Format
Mar 30, 2007Hi,
How can I convert a text file (.txt) into SQL in ASP.net 2.0 ? The sample of the file format is like that ...
09/03/2007 08:41 "Fung, Kitty" Granted Access D1 Main 2354 111
09/03/2007 08:42 "Ng, Jaclyn" Granted Access D1 Main 21906 18
09/03/2007 08:42 "Leung, Agnes" Granted Access D1 Main 21920 18
Cheers
Store Xml Output In Text Format
Jun 4, 2008I have the following Query which gives the output in XML format.
select * from <Table_Name> For XML auto
I wanted to store this output in a .Xml file at my local machine.
I would prefer to have a proc when called generates the xml and stores it on the local m/c. Any ideas ?
Thanks,
Dependency Tree In Text Format?
Mar 7, 2000Hi,
I have a database of over 200 tables. I want to delete data in about 100 tables. It's hard for me to delete because there are so many dependencies. I wind up tracing each table until I find the end and then deleting backwards. Is there a script I can run where I can get a textual view of which tables are related to which tables (foreign key constraints). I don't want to hit 'display dependencies' on each table. I know I can look at logical diagram but with a database of over 200 tables, it is hard to look at it.
Thanks,
Angel
Change-Format Text To Date
Jun 12, 2008I have a column of text 010208 that is suppose to be a date. How can I changeformat this column to read 01/02/2008?
Lisa Jefferson
Create Trigger To Format Text
Mar 4, 2015We are using an old version of Numara TrackIT for our helpdesk software, and it doesn't have much in the way of configurable options. There is no way to set validation or formatting on the text fields in the program.
There is a field, WO_TEXT1, Which I would like to be formatted as 6 characters, 3 integers + a period + 2 integers. The first the integers would be padded with zeros on the left, and the last 2 integers would be padded with zeros on the right.
IE, if someone enters 2, it would actually end up being 002.00
If someone enters 3.5 it would end up being 003.50
If someone enters 12.1 it would end up being 012.10
If someone enters 172.80 it would end up being 172.80
I was hoping to achieve this via an update trigger.
Below is the guts of the trigger I created, mostly as a proof of concept.
-- This update properly formats the Estimated Hours field
Update t SET WO_TEXT1 = (SELECT RIGHT('000000' + CONVERT(VARCHAR(6), WO_TEXT1), 6) FROM inserted)
FROM dbo.TASKS as t
Where (EXISTS (SELECT * FROM inserted WHERE WOID = 24773));
I expected that this update trigger would only affect the Work Order with a WOID of 24773. Unfortunately, it updated all 21000 work orders in our system, wiping out all of the actual estimated hours that had been inserted by technicians!
Luckily I had a report that I could quick dump the 300 or so active work order's estimated hours back into the DB from (all the other Work orders are closed, and no one really cares about their estimated hours).
My question is three fold,
1) Why did my trigger update every record in the tasks table instead of just WO 24773?
2) Is using a trigger the best way of accomplishing what I'm trying to do?
3) if a trigger is the best way of accomplishing this, what should my trigger look like?
Multi Format Text File
Apr 5, 2007Hello. I am in the process of migrating an old app to SQL Server. The old app reads hundreds of different flat file formats. One of the more complex ones is a multi-format delimited file. For example:
01^Bob Johnson^123 Main St^Anytown^St
02^Book1^$20
02^Book2^$30
03^Gift Cert^Happy Birthday^$100
This file is delimited with the ^ character. Note that the first 2 characters identify the row type. All 01 rows have data in the format: Name, Street Address, City, State. All 02 have data in the format: Book name, price. Etc.
Any clever ideas on how to parse this? I tried setting it up as a flat file source with the ^ delimiter. It doesn't work - in this example it wraps the third row to the end of the second row and keeps adding columns to fill out the row.
The only option that I can think of is to pull the entire row into one long column, and then use a script component to manually substring each column out.
Any help would be greatly appreciated.
Thanks,
Chris
Wired Text File Format
Jan 24, 2007Hi,
I have a text file contains data extracted from security logs from from different servers. The format of the data looks like this
Header row
Computer,LogFile,Type,Source,EventID,EventCode,User,Date,Time,Category,CategoryString,EventType,Description
Detail row
ServerName,Security,Audit Success,Security,540,540,DomainUserName,20061231,13:36,2,Logon/Logoff,4,Successful Network Logon:
User Name: UserName
Domain: Domain
Logon ID: (0x0,0xD4118FE)
Logon Type: 3
Logon Process: Kerberos
Authentication Package: Kerberos
Workstation Name:
Logon GUID: {4bcdc9b4-4caa-ce31-b202-459f1865a477}
Caller User Name: -
Caller Domain: -
Caller Logon ID: -
Caller Process ID: -
Transited Services: -
Source Network Address: XX.XX.XXX.XX
Source Port: 0
If I tries to extract data as Delimited the columns are not containing the same data for each row as there are some wired characters in the "Description" filed.
How can I extract this data as proper csv file? If not is there a way to extract only ther first line in detail row as shown below
Success,Security,540,540,DomainUserName,20061231,13:36,2,Logon/Logoff,4,Successful Network Logon:
If I tries to extract as Fixed Width, it still not work. Kindly suggest, How I can achive this.
How To Get Report In Text Format In SSRS?
Sep 3, 2007Hi,
Can aybody help me in the below requirement.
I have a report like below:
<<Name Of Compnay>>
<<Name Of Report>>
<<Todays Date>>
<<ReportPeriod>> From Date - To date
The above is header and then the data in the report should come as the <<Data>> and # to be used as separator for columns.
163 #GXXX #ABC Comp Ltd. #DTDC#PPPP ABC XYZ ETC #111
*End Of Report*
How can I get this exported as it is to the .txt file?
Currently I have acheived this with SSIS, where I have kept the header template, footer template in my folder. The SSIS generates the .txt file and then I use the execute process transformation to merge the files and data. I am getting the exact o/p as I want. But in this I am intorducing lot of IO in the package by every time creating a new header for the data to be replaced as todays date and the period to be replaced by the user selected Report Period.
How can this be achived using SSRS? Is any custome code required?
Thanks in advacne for your help.
Regards,
Virendra
Convert Text Data To Date Format
Nov 13, 2013I've got a field I'm trying to convert into a date format as it currently sits as a text field. The date setup is of the format DD/MM/YYYY. Some of the fields are NULL, however for the sake of filling in gaps I've just set the nulls to '01-01-1905'.
I've tried to use CAST to change the data into a readable format for SQL Server however I tend to get one of two messages:
Conversion failed when converting date and/or time from character string.
-Or-
error converting data type varchar to date
Encrypt And Decrypt Text Format Data
Oct 19, 2007Hi,
We have migrated a CRM Database from SQLServer 2000 to SQLServer 2005.
Database contains very sensitive data about customer in text format (Datatype varchar(20)) how can i encrypt the same without any change in the table design.
Regards
Sufian
Query String Field Based On Text Format
Mar 7, 2008I need to search a nvarchar field based on the format of the text. This field holds values in two formats: 000 000 000 000 and 000000. I only want to search through the records that are in the 000 000 000 000 format. Can anyone give me direction on how to go about doing this or give me some key words to search for on Google? Fixing this problem is not an option. This is a county tax DB from a poor county with almost a million records in it.
Thanks for the help!
Keeping Text Format After Stored In The Data Base.
Jan 14, 2004I want the users of my site to be able to write a couple of paragraphs, save it to SQL Server and then have it read back and look the same. I don't want the users to be able to add any html to the text they submit. I just want them to be able to seperate or indent their paragraphs.
At one point, I had a textbox that saved text to the server and read it back the same way it was originally inputed. However, I can't figure our what I had done to make it work.
Any ideas?
Thanks
SQL 2012 :: Date Format In Text Column With Many Other Inputs
Feb 10, 2014How to convert MM/YYYY to MM/DD/YYYY and also YYYY to 01/01/YYYY.
This column is text column and has many other inputs.
SQL 2012 :: Select And Format A Text In A Varchar Column
May 9, 2014One purchased app stored user's multiple lines input into a varchar column in which including char(13) and char(10).
My app need to select this value and format to multiple lines text in one text box.
How to code to output it?
BCP And Treatment Of NULLs When Export To Fixed Format Text File
Jul 23, 2005We are using the bcp utility (via APIs) to export data from a SQL tablein a fixed format text file. BCP is inserting spaces for a field ifthe field contains a NULL. This is fine with us except at the end ofthe line, there are no spaces for that field just the end-of-rowterminator prematurely, so it looks like that field is not present andmesses up another piece of software we pump the text file into downstream.Example -- The last row illustrates the problem.123-49-890 Mary Smith Raleigh NC
999-88-123 Henry Ax Boston MA
456-99-123 Sue Kite WA
789-88-126 Andy Yates Philadelphia
We have thought about using a SQL query to convert the NULL dataexplicitly to spaces, but were wondering is there a switch or somethingin our format file to get around this.Thanks.
How To Align The Text In Justify Format With SQL Server Reporting Services?
Jan 18, 2007
How to align the text in justify format In SQL server Reporting Services? Is there any code to do so?
Exporting Data To A Comma Delimited Text File, FORMAT Function
Jan 15, 2001Hi. Im new to SQL and I need to export a SQL table as a comma delimited text file which is straight forward. However two of the fields are integers and I need these to be right justified with zero's.
In Access I would use something like format(columnname, "00000000") to get it to work, but SQL Server doesn't like this.
How can I do this?
Integration Services :: Send 4 SSRS Reports In Text Format To Ftp Server
Jul 11, 2015i have 4 SSRS reports. each report must be scheduled to run at different time and then send the send the reports in txt format (tab delimited) to a ftp server .I am new to SSIS and SSRS.
View 3 Replies View RelatedSQL Server 2008 :: Change Text Format From Case Sensitive To Case Insensitive?
Aug 31, 2015How can I change my T-SQL text editor from text sensitive to text insensitive?
View 2 Replies View RelatedXML Format File For Bulk Insert Of Text File With Fixed Length Columns
Jan 2, 2008Hey All,
Similar to a previous post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=244646&SiteID=1), I am trying to import data into a SQL Table.
I am trying to program a small application that will import product data obtained through suppliers via CD-ROM. One supplier in particular uses Fixed width colums, and data looks like this:
Example of Data
0124015Apple Crate 32.12
0124016Bananna Box 12.56
0124017Mango Carton 15.98
0124018Seedless Watermelon 42.98
My Table would then have:
ProductID as int
Name as text
Cost as money
How would I go about extracting the data with an XML Format file? I am stumbling over how to tell it where to start picking up data for a specific column.
Is there any way that I could trim the Name column (i.e.: "Mango Carton " --> "Mango Carton")?
I don't know if it makes any difference, but I've been calling SQL from my code by doing this:
Code in C# Form
SqlConnection SqlConnection = new SqlConnection(global::SQLClients.Properties.Settings.Default.ClientPhonebookConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO PhonebookTable(Name, PhoneNumber) VALUES('" + txtName.Text.ToString() + "', '" + txtPhoneNumber.Text.ToString() + "')";
cmd.Connection = SqlConnection;
SqlConnection.Open();
cmd.ExecuteNonQuery();
SqlConnection.Close();
RefreshData();
I am running Visual Studio C# Express 2005 and SQL Server Express 2005.
Thanks for your time,
Hayden.
SQLServer JDBC Exceptions :Controlling Exceptions Text Format
Sep 28, 2006Hi,
Using RAISERROR from within a stored prcedure will result in a SQLException being thrown at the client side of a JDBC connection.
I am trying to provide a meaningfull error message to be shown to the end user. However, all exceptions thrown will inevitably start with : "[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]". The following questions then arise :
- Is there a way of configuring SQL Server not to display this header ?
alternatively,
- Is there another way of conveying error messages to the client through JDBC ?
Thank you for your answers,
Ben
Negative Values...
Jan 17, 2007calculating profit... how do I write an update query that will correct the gross profit calculated column for all negative qty transactions
Basically in the stored procedure that creates the table the query includes:
Profit = CASE Sale WHEN 0 THEN 0 ELSE (Sale - Cost) END,
which is wrong when sale and cost is negative the formula becomes
(-Sale + Cost).... I want it to be -(Sale-Cost) (where sale any cost ignores negative sign.....
but i dont know to write this....any ideas???
Negative Values
Jan 3, 2008
I have the following query, I would like to know if I can replace negative values with a zero and if a posiive value appears then leave the value alone?
SELECT TimeStamp, Tag1,Tag2,Tag3
FROM dbo.keller8
QA Returning Negative #s As Positive
Mar 30, 2004Hey guys,
I have a very peculiar issue going on. I have a table that contains a decimal(18,2) column called "Amount". Looking at this table through Enterprise Mgr, I can see that there are values in there that are negative. However, when I run a query in Query Analyzer, it displays all the negative values as positives.
The only workaround I've found right now is to change the column type to "real" and then change it back to decimal(18,2), and it starts showing the negatives as negative. However, without performing this absurd workaround, it doesn't work.
Is there a known bug in QA that would manifest itself as this? What is the cause for this?
Thanks in advance.
Inserting Negative Number?
Sep 30, 1999Hi
I'm a newbie when it comes to using SS7. When I try to insert a negative number (-1) into a integer column, the value is changed to 65535. How can overcome this problem?
TIA
JDJ
jdj@gvd.dk
Sp_spaceused With Negative Numbers?
Mar 1, 1999Hello:
I have gotten the following information from sp_spaceused:
----------------------------------------------------------------------
database_name database_size unallocated space
------------------------------ ------------------ ------------------
EP_tran2 700.00 MB 273.21 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
437034 KB 295106 KB 1447796 KB -1305868 KB
--------------------------------------------------------------------
I unfortunately since this is for a test database that I have put the data and log on the same device.
My questions are:
1) Why do I have negative numbers?
2) Should I be concerned about this?
3) for a live database can I drop the log and put the log on another device? What do I need to do?
4) has anybody seen this before?
5)what can I use to get accurate numbers?
THanks. Any information will be greatly appreciated.
DAvid Spaisman