Can anyone tell me how to format a field to spec?
Example:
Select num_field from table = 12345
I want
Select Format(num_field,"0000000000") from table = 0000012345
or some equivalent. I know in Access you could do this using the Format function, but I cannot seem to find anything like this in SQL.
Thanks in advance,
Michael
I have a db1.tPersonJobHist where I have two columns
[WorkflowCoordinatorFlag] [dbo].[shrsFlag] NOT NULL,
[HRBusinessPartnerCode] [dbo].[shrsCode] NULL,
Our developer mistakenly made HRbusinesspartnercode field as nullable. He now wants to change it to Not Nullable.
So there were 4 rows where the values were not null(not sure how). We edited those rows and changed those values to NUll. Now we have no Nulls in that column.
So we brought up the table designer and made the change but got the following error
'tPersonJobHist' table
- Unable to modify table.
Cannot insert the value NULL into column 'HRBusinessPartnerCode', table 'BD6578.dbo.Tmp_tPersonJobHist'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Question is why wouldn't it let me alter the table design. and what is tmp_tpersonjobhist.?
How can I make this column Not-nullable.
Hi
Is there a way to format a field in a table with a datetime data type to show just the date?
ie. 24/04/2008 17:25:00 would just become 24/04/2008.
Even showing 24/04/2008 00:00:00 would be helpful.
Thanks
I am selecting a datetime value from a table but I want it to be
in this format: mm/dd/yyyy
This query is as close as I have come:
select char(month(nancyDateSent))+ '/'+ datename(day,nancyDateSent) + '/' + datename(yy,nancyDateSent) from tbloutgoingobms_hold
but is not correct - I get dates that look like this: /11/2002
Please help - there must be an easier way !!!
Thanks in advance,
Nancy
Hi guys,
Presently I have a column which represents the amount of money someone spent and for some reason, the fields are being outputted like this 29.8600.
This field is infact suppose to 29.86. Now initially this file was an excel spread sheet and I imported it into sql server 2000 and the datatype is money. What would i use to get the desired field.
Hi guys,
How do you convert a date varchar field which looks like 20080525 to 2008/05/25?
Hello again,
I have a huge question haha. I just simply want to make a number display differently before I insert it into the table. for instance in Access, you can simply say FORMAT(c,format) ex: FORMAT(@CHARMINS, 00000) and it should work that way.
How would I accomplish this in SQL server? Right now I have the number 30 displaying as 30.00 in the variable it is set to.... I want it to be shown as 00030.... and any other number that is like 30.00, I want it shown the same way. Like 100.00 would be 00100. Thank you.
Is it possible to format just part of a field? I've got a report that return a collection of users based on some search criteria. Bascially, you enter a string and the report returns all users with a user name or display name simialr to your search string. I wanted to highlight the part of the name that matches the search string.
For instance, if you search for 'nny'
You might see a result of 'Johnny User'
Thanks.
J
Hello, I'm new to SQL Server, working for a non-profit computerizing alot of its data.I imported a table of people's names, birth dates, etc. into SS2005from Access, and the birth_date was imported as an Access date/timefield, giving it the datetime datatype in SQL.The column values look like:10/14/1964 12:00:00 AMWhere and how do I learn to specify that all fields like this should bein ISO format of yyyy-mm-dd??Do I have to create a new column and put all the dates into it??Should I just convert the data in queries/views??Use a constraint to format the data??I can redo the Access table if necessary, it is only 300-some rows.I tried BOL but it was not helpful...The end users will likely enter mm/dd/yy or mm/dd/yyyy and it will haveto be stored properly in the database table as column/fieldbirth_date...Thank you, Tom
View 11 Replies View RelatedI have a field where the data is stored with RTF. I know SQL Reporting Services cannot display it properly. Is there way to remove the RTF formatting codes through a function to just pull out the text and displya it in the text box?
View 4 Replies View RelatedCan I build an expression that allows me to change the field size of a column or row in SSRS2005?
View 5 Replies View RelatedHelloWe are upgrading our DB server to a new machine and there is a heateddebate about what spec machine to use.We have on this server a 15 GB DB that handled around 2-3 milliontransactions / day, 50% updates and 50& reads roughly, the main goalsince it is used by an interactive end user application is speed forupdates and reads, it does not need to do any heavy calculations atall during daytime, at nighttime it does a lot of batch jobs, goingthrough the data and inserting results into different tables.the main questions I have is:Single or Dual CPUAMD 64 or Intel CPU2 or 4 GB of RAMDIsk Setup for max I/O, we are thinking, mirror for the OS, mirror forthe DB logs and RAID 5 for the DB data, using 2 different RAIDcontrollers to try and maximize disk I/O, keeping the logs and OS onone controller and DB data on the second controller.is there anything I need to watch out for? or something to pay moreattention to than other things?ANy advice would be greatly appreciatedrgdsMatt
View 2 Replies View RelatedHi,
I have a SQL server 2000 database running in Windows Server 2000. The database only consists of a handful of tables and is taking multiple inserts from @40 client PCs.
The server exists on two servers with half the clients talking to each. The databases use transactional replication to stay synchronised.
Each PC generates about 100 SQL transactions per busy hour, each being an insert.
The customer is expanding the number of client PCs from 40, through 300 to, ultimately, about 3000. They are asking me what hardware spec is required and want to be told it in a “for each additional 50 client applications you need XYZ” type of format to allow for gradual growth.
I have to say I am unsure how to go about scaling and configuring the servers!
So, is there such a thing as an automatic ‘configurator’ which automatically produces a required spec for a given number of transactions and clients? Are there other criteria they/I need to take into account such as number of logins etc? For archiving I am guessing that a CD/RW drive is the way to go?
Also, in terms of design between the two servers they have. With clustering, mirroring, replication etc I am unsure as to which is most appropriate.
The thing I do know is that they are willing to spend serious $$$ for the right design and need 99.9% up time.
They also pull significant amount of reports which I think logically equates to a third server to keep this traffic away from the live database. So, is replication the best way to go? Say replication every 15 minutes to a reports server?
I am also of the opinion that they need to move from Windows 2000 Server to Advanced Server for added scalability but will pursue that elsewhere.
Any input MUCH appreciated.
Out of my depth! :confused:
Paul
I would like some clarification regarding the specifications of SQL Server Compact Edition 2005 :
1)Is it required that .NET be installed in teh clinet for redistribution?
2)Will the database work even with the client not having any server (with IIS) to interact with ?
3)Does the database runs on 64 bit systems with the same binaries provided for 32 bit?
Thanks in advance
so i want to create a cmoputed column specification based on an 'if' condition. this is what I want to happen:
if [WID] < 10, I want the value in the computed column to be "W0" + [WID]
if [WID] >= 10, I want the value in the computed column to be "W" + [WID]
[WID] is of type tinyint.
What is my computed column specification formula? Thanks.
A date field in a report returns the date value as:
2015-07-01 13:30:27.000
Is there any way I can script this to appear as:
01 July 2015 (or 01-07-2015 or 01/07/2015)Â - basically to cut out the hours, minutes and seconds?
The best I have managed is: CONVERT (varchar(17),DATE,113) AS Date1 but this still leaves me with:
01 July 2015 13:30
First, I hope this is in the correct spot.
I'm trying to build a SS 2005 db from a Sqlbase database and I have coded a program to bring over the data and insert. The problem I'm having is that on some of the tables, I'm getting an "Invalid character value for cast specification" error on an insert. I've found the offending row and discovered that it's an integer that is null in the source table.
The weirdness is, that in SqlServer, it is defined as an "int" column that can be null. I can insert the row through both Management console (connected natively) and through a sql utility from Sqlbase/Gupta (called sqltalk) connected thru ODBC. The problem is when I try the insert through OLEDB within the application, that's when I get the error. I changed the value from null to zero in the source table then it works. I think there must be a "set **** on" on the connection that I'm missing if I can enter the SAME data using Management console but I'm not sure what that is. I don't want to have to find ALL occurences of null integers in my entire database just to insert it into SqlServer.
Has anyone else had this problem??
Hi Guys!
I want to buy a laptop, so that I can install and play with the trial SS 2005 Enterprise Edition and hopefully get familiar with SSIS. At work I have only used SS 2000. I want a laptop which will run this with a reasonable speed, but don't want to pay for more juice than is actually required. Does anyone have any advice on what kind of spec would be good enough for SS 2005 Enterprise? If I go for the basics suggested by the system requirements, will it run like a tortoise?
Thanks!!
Clare
Hello all-
I have a specification table that has some attributes defined.
SpecId - Id of the specification
Attribute - Attribute of the spec. (Like Color, HP etc)
Value - Is the value of the attribute
Then I have a car table that actually has information about the cars. Intention is to take each specification and match the cars that match the specification. If the car has more attributes than the spec, we ignore the extra attributes for the match. But if the car has less attributes, we don't even consider the car as a match (even if the attributes present, match). To summarize, the car's attributes should be >= spec's attributes.
The code I have below is bad because I am joining the same tables twice. In addition, it fails in the condition "the car's attributes should be >= spec's attributes"
Any help is greatly appreciated.
DECLARE @Specification TABLE
(SpecId VARCHAR(10),
AttributeVARCHAR(100),
ValueVARCHAR(100))
DECLARE @Car TABLE
(CarName VARCHAR(10),
AttributeVARCHAR(100),
ValueVARCHAR(100))
INSERT INTO @Specification VALUES ('S1', 'Type', 'Sedan')
INSERT INTO @Specification VALUES ('S1', 'Transmission', 'Auto')
INSERT INTO @Specification VALUES ('S1', 'HP', '220')
INSERT INTO @Specification VALUES ('S2', 'Type', 'SUV')
INSERT INTO @Specification VALUES ('S2', 'Transmission', 'Manual')
INSERT INTO @Specification VALUES ('S2', 'HP', '300')
INSERT INTO @Car VALUES ('Accord', 'Type', 'Sedan')
INSERT INTO @Car VALUES ('Accord', 'Transmission', 'Auto')
INSERT INTO @Car VALUES ('Accord', 'HP', '220')
INSERT INTO @Car VALUES ('Accord', 'Color', 'Black')
INSERT INTO @Car VALUES ('Escape', 'Type', 'SUV')
INSERT INTO @Car VALUES ('Escape', 'Transmission', 'Manual')
INSERT INTO @Car VALUES ('Escape', 'HP', '300')
INSERT INTO @Car VALUES ('Explorer', 'Type', 'SUV')
INSERT INTO @Car VALUES ('Explorer', 'Transmission', 'Manual')
SELECT DISTINCT Spec.SpecId, Car.CarName
FROM @Specification Spec
INNER JOIN @Car Car
ON Spec.Attribute = Car.Attribute
AND Spec.Value = Car.Value
WHERE Spec.SpecId NOT IN (SELECT Spec.SpecId
FROM @Specification Spec
LEFT OUTER JOIN @Car Car
ON Spec.Attribute = Car.Attribute
AND Spec.Value = Car.Value
WHERE Car.CarName IS NULL)
Hi,
Please help share with me if you know the version compatibility matrix of Ms SQL Server, ODBC driver (sqlsrv32.dll), Driver Manager (odbc32.dll) and ODBC API spec. For instance, how can I know Ms SQL Server 2000 can work with which version of sqlsrv32.dll, a particular version of sqlsrv32.dll can work with which version of odbc32.dll and a certain version of sqlsrv32.dll/odbc32.dll conforms to which version of ODBC API spec (e.g. 3.5).
Any help will be appreciated.
Thanks,
vtluu.
I'm new to SQL and I'm trying to write a statement to satisfy the following:
If [Field1] contains text from [Field2] then return [Field3] as [Field4].
I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.
Good morning...
I begin with SQL, I would like to add a field that will be date like 21/01/2000.
Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.
How to do for having date and time in two different field.
Sorry for my english....
Cordially
A newbie
Hello all,I have a strange problem that i need some advice on. I have the following field called FILENO. It is a SQL 2000 field with the Data Type set to Char (7). The following sql statement works perfectly:SELECT TOP 1 RTRIM(FILENO) AS TEST, RIGHT(DATEPART(Yy, FILENOYEAR), 2) AS YEAR FROM tblRecordsWHERE RIGHT(DATEPART(Yy, FILENOYEAR), 2) = '05'ORDER BY FILENO DESCIt returns the correct data, 0050. Now, what i'm trying to do is add 1 to the value so i can get the next available number which is 0051. But, when i run the following sql statement, i get 51 instead of 0051.SELECT TOP 1 RTRIM(FILENO+1) AS TEST, RIGHT(DATEPART(Yy, FILENOYEAR), 2) AS YEAR FROM tblRecordsWHERE RIGHT(DATEPART(Yy, FILENOYEAR), 2) = '05'ORDER BY FILENO DESCDoes anyone have an idea how to solve this? Thanks.Richard M.
View 1 Replies View RelatedI'm working as a consultant for a project and I have been having constant issues with the in house "database guru" about stored procedure formating.
He insists I use a format similar to this:
create procedure getUser @UserId int = NULL
as
select
UserName
, UserPassword
, UserRole
from
Users
join
UserRole
on
User.ID = UserRole.ID
where
User.ID = @UserID
and
User.Active = 1
where as I prefer this format:
create procedure getUser
(
@UserId int = NULL
)
as
select UserName,
UserPassword,
UserRole
from Users
join UserRole on User.ID = UserRole.ID
where User.ID = @UserID
and User.Active = 1
Now normally I don't argue about style issues but I find his format confusing and it bothers me that it senselesly wastes lines make the stored procedure longer than necessary.
I'd love to get some comments on this issue or reference to any discussions on issues such as this.
Thanks
Hello -
Does anyone know why SQL Server Reporting Services formatts SQL weird? -- If I type a SQL statement in notepad or notepad++ and then paste it in Reporting Services it will change the whole structure and "expand" it out to three or four times its size.
Thanks
Adam
Hello All,
Is it possible to format an integer to a float value with 2 decimals in an sql query.
If yes, Please help?
I need to show my SUM of the 2 columns added in the query below formatted as currency. Is this possible?
SELECT SUM(QVSTDN + QVNONC) AS Total FROM INVOICE_TBL WHERE QVORDN = @QVORDN AND QVINV = @QVINV
I tried:
SELECT CONVERT(varchar(12), SUM(QVSTDN + QVNONC) , 1) AS Total FROM INVOICE_TBL WHERE QVORDN = @QVORDN AND QVINV = @QVINV
But this does not format it as currency. Any input would be helpful.
I imported data from a text file, where dates are currently in the format: 050729 to represent July 29, 2005.
How could I write a sql statment to put make this show up as 07/29/05?
Thanks
I am trying to output data from SQL Server to an email body message with a little formatting (just to make it legible)
Using this SQL statement:
select top 1 'Name', app_first_name+' '+app_last_name [name] ,'Submission Date', submission_date ,'Address', app_address+', '+app_city+', '+app_state+' '+app_zip from vw_bpa_export where app_email = dave@email.com order by submission_date desc
the resulting output displays this in the email body
"Name","Brian Vogler","Submission Date","2008-01-30 07:45:00","Address","6285 Harrow Trace, Norcross, GA 30092 "
but what I want is the text to look more like this:
Name Brian VoglerSubmission Date 2008-01-30 07:45:00Address 6285 Harrow Trace, Norcross, GA 30092
Is there a way to do this in the SQL statement (without HTML tags)?
In a SQLDataSource, the following line throws an error as an undefined function. FilterExpression='left$(lname,1) = "D"'
Isn't this a standard VB function and why does it produce an error. How do I fix?
Thanks
Hi,
In MS Access I can do the following:
Format([SW_Dttm],"Short Date")
What is the equivalent in SQL Server?
Thanks,
Bill
I have values in my database that I'd like to display as $xxx,xxx.xx. Some of these values use the money datatype and others use float.
I'm building a string in a stored procedure that is eventually passed to Visual Basic. I want to format these values in the above format in the string that is passed. I've searched all through MSN and I couldn't find anything related to VB's FORMAT function in SQL Server.
Does Transact-SQL have any functions that could format these values for me?
Does anyone familiar with a tool or a quick way to put apostrophes and commas around some values, so they could be used in a query ? Example, if I have a bunch of zip codes such as:
10001
10003
10009
54321
89043
etc
Let's say I have a couple of hundreds of them. I need to be able to see them as -
'10001','10003','10009','54321','89043' etc
Appreciate very much your time.
Ivan