How Can I Combine Three Fields Together Of Dif Datatypes
Oct 13, 2007
I want to combine three fields together as a description in a select statement. When I try using the & or + I'm told that the datatypes are incompatable. How can I join them?
(Item_Description is Nvarchar, Item_Cost is Money, Is_Active is bit)
Select Item_Description &' '& Item_Cost &' '& Is_Active As Description FROM tblItemList I tried Casting this but same incompatable message.
Select Item_Description &' '& CAST(Item_Cost AS NVARCHAR) &' '& CAST(Is_Active AS NVARCHAR) As Description FROM tblItemList
What I'm hoping to end up with is:
Brake Pedal, $36.00, True
View 3 Replies
ADVERTISEMENT
Jan 16, 2008
Hi All.
I apologise if i have not posted this in the correct Topic before i start. But was uncertain where to post this query.
This is my first project in ASP.NET, MS Visual Web Developer 2005 Express and SQL Server 2005 Express. I have relatively little experience, so please bare with me. I have the table below:-
Column Name Data Type
OrderID intOrderNo intInvoiceNo varchar(50)PurchaseDate smalldatetimeCost moneyInsure money
I wish to have a dropdownlist on an aspx page display the OrderNo and the Cost e.g. 12345 £3.50. I have tried the SQL Statement below but that just calculates the addition of both.
SELECT OrderNo + ' ' + Cost AS Expr1
FROM [Order]
Do i have to convert them both to string/varchar first? If so, how?
Thanks for your replies.
View 3 Replies
View Related
Oct 30, 2006
I have two fields that I would like to combine into 1 field is this possible.
Example: Document # 555, Doc Description ABCD in the 3 field I would like 555-ABCD.
Is that possible in SQL Server thanks
View 4 Replies
View Related
Nov 5, 2007
I have 3 fields: DOB_DAY (ex: 15), DOB_MO (ex: 8), and DOB_YEAR (ex: 1975). I have a blank field named BIRTH_DATE.
What would be the SQL to set the BIRTH_DATE field to be equal to the Day, Month, and Year field. I need it in "DateTime" format.
Thanks
View 11 Replies
View Related
Sep 25, 2007
Hi there!
I have a table with three columns: Id, Name and Departament. (ex: 23, "Mary", "Check-out")
I'd like to write a SQL Select clause so that the three columns are combined in just one column (ex: "23 - Mary - Check-out")
Is it possible? Many thanks for any kind of help.
View 4 Replies
View Related
Nov 5, 2007
Hello everyone -
This is my first post to the forum and I'm very new to SQL. I apologize if this is addressed elsewhere.
Here is an example of the results I am getting from my query
AdmissionID, sNurseInit, sSWInit
100, {NULL}, SAE
100, REG , {Null}
Is there a way to combine (merge, join? I don't know the right word) these records so that a single record for the admission is returned?
AdmissionID, sNurseInit, sSWInit
100, REG, SAE
Thanks in advance!
Amy
View 20 Replies
View Related
Jan 28, 2008
I want to do something like this.
Code SnippetSELECT * FROM [scholarship] WHERE ([schlrPrefix] + ' ' + [schlrName] AS ScholarshipName) LIKE 'Ann Buttler'
How do I something like this where it combines two fields and then use that to compare to a parameter?
View 6 Replies
View Related
Jan 24, 2007
Hello to All,
I needs help to combine these together but how would I eliminate necessarily zero in front of "PropertyHouseNumber".
Table: DirectHome
Column fields.......
PropertyHouseNumber, PropertyStreetDirection, PropertyStreetName, PropertyMODE
0000001091 , W , 000026TH , RD
Thank you
RV
View 5 Replies
View Related
Jan 1, 2007
Is it possible to combine fields and text in a select statement?
In a dropDownList I want to show a combination of two different fields, and have the value of the selected item come from a third field. So, I thought I could maybe do something like this:
SELECT DISTINCT GRP AS GroupName, "Year: " + YEAR + "Grade: " + GRD AS ShowMe
FROM GE_Data
WHERE (DIST = @DIST)
I hoped that would take the values in YEAR and GRD and concatenate them with the other text. Then my dropDownList could show the ShowMe value and have the GroupName as the value it passes on. However, when I test this in the VS Query Builder, it says that Year and Grade are unknown column names and changes the double-quotes to square brackets.
If this is possible, or there's a better way to do it, I'd love some more info.
Thanks!
-Mathminded
View 7 Replies
View Related
Dec 28, 2007
The following query gets all the data I need except for one new field that I need which combines multiple fields and some text. Here is the query:
SELECT [Make Mods-Additions HERE].StockNumber AS ProductID, [Make Mods-Additions HERE].[Long Description], [Make Mods-Additions HERE].[Short Description], [Make Mods-Additions HERE].NEWwholeEachCost AS [Wholesale Each], [Make Mods-Additions HERE].Units, [Make Mods-Additions HERE].[Sale/CameoPrice] AS [Case Price], [Make Mods-Additions HERE].MinQty, [Make Mods-Additions HERE].Multiples, [Make Mods-Additions HERE].UPC, [Make Mods-Additions HERE].MSRP, [Make Mods-Additions HERE].[Availability Date], [Make Mods-Additions HERE].[Item Description (Detailed)] AS [Full Item Description]
FROM [Make Mods-Additions HERE]
WHERE ((([Make Mods-Additions HERE].Active)="YES"));
I need one more field named 'Rep Order Description' that concatenates the following:
[Short Description], "-$", [Wholesale Each], " ea, MSRP $', [MSRP]
It is important that the [Wholesale Each] and [MSRP] values are in 0.00 format (they are currency)
Example of output:
Short Description-$0.00 ea, MSRP $0.00
View 1 Replies
View Related
Oct 19, 2007
I want to combine two of my fields somewhat like when you combine to char fields and concatenate them. But these two fields are int. How do i do that? Here's my query right now:
Code Block
SELECT AutoID,
CONVERT(Varchar(Mars_Calender_Year )+ CONVERT(Varchar(Mars_Calender_Period_Code) as MarsId
FROM NavisionReplication.dbo.Tbl_Mars_Calender
ORDER BY Mars_Calender_Year DESC
what am i doing wrong?
View 5 Replies
View Related
Dec 26, 2006
Good morning.I am importing an XLS file into one of my tables. The fields are:Date Id Time IO12/22/2006 2 12:48:45 PM 912/22/2006 16 5:40:55 AM 112/22/2006 16 12:03:59 PM 2When I do the import, I get the following:Date Id Time IO12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 212/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 112/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2Here are my doubts:1. Would it be better to combine the Date & Time fields into onecolumn? If so, how?2. What issues or problems might I have when I program SQL reports, ifI leave the fields as they are?Any comments or suggestions will be very much welcomed.Cheers mates.
View 2 Replies
View Related
Dec 26, 2006
Good morning.
I am importing an XLS file into one of my tables. The fields are:
Date Id Time IO
12/22/2006
2
12:48:45 PM
9
12/22/2006
16
5:40:55 AM
1
12/22/2006
16
12:03:59 PM
2
When I do the import, I get the following:
Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2
Here are my doubts:
1. Is it be better to combine the Date & Time fields into one column? Advantages/Disadvantages?
2. If I don't combine them, should I use varchar or datetime data type?
2. What issues or problems might I have when I program SQL reports, if I leave the fields as they are?
Any comments or suggestions will be very much welcomed.
Cheers mates.
View 3 Replies
View Related
Sep 13, 2000
I am designing my first SQL database and need some insight on datatypes.
What datatype is equivalent to autonumber like in MS Access.
When I convert an auto number field to to SQL it comes as int with a length of 4, Does this mean it will only auto increment to 9999?
I need the field to increment forever.
Also what datatypes should be used for phone number with area code and
zip code.
Thanks
View 1 Replies
View Related
Nov 20, 2000
Hi i'm using the FileSystemObject to loop through a file and read out all the data within it (it's an html page, so loads of tags and free text) and then put it in a field in a table. I am using the nText datatype but i don't think i use Full-Text Searching with this, is there any other i can use that produces the same result?
Cheers in advance...
View 3 Replies
View Related
Apr 10, 2002
Hello,
I'm 17 years old and I'm from Belgium, and I have to make a school project. I have to make a program in Visual Basic and my database is SQL Server 2000. I've already made my tables but I dont know excactly what all the datatypes in SQL Server are. They are a little bit different then Access datatypes and I dont know what to use, ntext, text, nvar, ... I can't find a list of the meaning of the datatypes on the net, maybe I'm searching with the wrong keywords(+"sql server" +datatypes in yahoo), but could please someone give me a link of a site where there is that kind of information?
thanks a lot :)
Dommie x
View 1 Replies
View Related
Aug 4, 2004
I'm new to SQLServer and I'm creating some tables. In Oracle I always used varchar as this was the default and also recommended. The default in SQLServer seems to be char. Am I right in thinking that varchar is still best to use, as the field can then be of variable length rather than fixed?
View 4 Replies
View Related
Aug 27, 2005
Could someone tell me the datatypes for the SQL EXPRESS 2005
I'm trying to use a boolean datatype and it says it isn't a datatype....have they changed it??? Please HELP!!!
Thanks
View 6 Replies
View Related
Apr 1, 2007
I have a SQL 2005 database that's created by a survey data collectionsystem. Users of this system are fairly non-technical and have littleto no conscious control over the datatypes. As a result, the data ismostly stored quite inefficiently as varchars. For example, there isdata that could be stored in a column of bits and it's stored as avarchar value of 0 or 1. (Yuck, I know.)I am building a reporting system using this raw data and have a newtable structure designed that is much more efficient (and better forreporting). Does anyone have any suggestions for getting this datainto my new structure? Specifically, how would you recommend checkingthat varchar field and determining it could be stored as a bit?
View 3 Replies
View Related
Sep 29, 2006
i hope this is the right board for this.
i want to have a table for members of a club/society and i wnt to be able to maintain the structure of their member id numbers, eg com212 for a committee member, or mem019 for ordinary members.
is there a way to automate this using some sort of auto-incriment function and use this as the primary key for the table?
thanks in advance,
mark
View 4 Replies
View Related
Apr 17, 2007
I have created a coldfusion webpage form that writes to a sql database. For comment boxes they can enter as much text as they need. Do I want to choose a varchar for my datatype and put a riduculously high number for characters or should I should use a text datatype and if I use text are there specific options I should set up?
Thanks!!!
View 4 Replies
View Related
Nov 18, 2006
I am currently creating a database tables in SQL Server 2005. I am looking for a link to a website that might explain to me the correct configuration for tabels in a sql server database such as the recommended datatypes for specific fields, eg datatype for short characters, or numbers etc.
View 3 Replies
View Related
Jun 3, 2007
Can some1 tell me the data types used in SQL Server for what like, i use Access so like i feel the data types are more intuitive, but for SQL Server in ASP, nchar, nvarchar, there are so many and i dunno what is what. any sites for beginners? cos i searched google and the terms they use to describe data types are like "variable length" "Unicode" i don't exactly know them. something like A-B, integers etc will be good.
View 2 Replies
View Related
Jun 9, 2000
Two datatype problems: (1) Datetime conversion (2) Foreign Currency format.
The first problem is that I am trying to convert source data from varchar to datetime. The source data is in CSV format and is displayed as follows - '1111999052349' to represent 1-Nov-1999 05:23:49. Have converted to a numeric value and then tried to convert to datetime but this just returns the following message 'Server: Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type datetime.' However you can convert to a timestamp but the value returned is not meaningful (e.g. 0x0E0000013DFA4EE8).
Problem 2 concerns the French currency. The source data is in CSV format and is displayed as follows - '00000001,9700' to represent Fr1.97. Need to convert this to a numeric field or alternatively get SQL 7 to recognise it as a money field.
Any suggestions would be musch appreciated.
Many Thanks.
View 2 Replies
View Related
Feb 19, 2001
Hi,
Is anybody can tell me why I can't have more than 255 characters in a field with text datatype?
The insert and the retreive is done directly using ISQL...
Thanks a lot for your assistance
Patrick
View 2 Replies
View Related
Jun 24, 2004
Hi all. Im tryin to export (DTS) my some SQL server tables, many of which contain 'bit' datatypes. However, when DTS/SQL Serv. moves these bit datatypes out, it changes bit values to True/False values - which makes sense - however these are all going to plugin to web frontends where the SQL specifies condtions like: "if column1 = 0 then" etc..
Is there anyway to get SQL server to export bit datatypes as just numeric values of 0/1?
View 11 Replies
View Related
Sep 28, 2007
New to developing. We are working on a project using asp.net and c# where data is pulled out of an sql database via multiple tables and displayed via a details view. The user can then update the information and submit it back to the database.
The problem we are encountering is:
Within the database there are multiple tables joined by a FK for example there is a Service Group ID column with the Records main table linked to the Service Group table which holds an ID column and a Service Group column so ID1 = Service Group A. Any records within the main table which has an ID of 1, is shows as Service Group A in the details view.
What we wold like to do is when a user updates the Service Group via a drop down, the ID goes into the main table rather than the Service Group name so if they select Service Group B, an ID of 2 goes into the Records main table. We aren't sure how to go about doing the conversion of Service Group B to an ID 2? We have been looking at converting datatypes but don't think it is right for what we are trying to do,
Any suggestions would be gratefully recieved Apologies if this is in the wrong forum.
thanks
View 1 Replies
View Related
Oct 10, 2007
Hi all,
i'm looking for an article on how to use DataTypes of SQLServer in T-Sql script writing !!!?
As you know the Books-Online has limited example and i have trouble undertanding for example how to use Binary,varbinary Decimal,Float,real...in T-sql script
Could anyone showm me an instructive article on the net?
Thanks in advance.
View 3 Replies
View Related
May 23, 2006
Hi,
Is there any equivalent in SQL Server for Oracle's datatype 'SDO_GEOMETRY'
Smitha
View 1 Replies
View Related
Apr 27, 2007
Dear Friends,
I have a problem with datatypes....
The error is "Cannot convert between unicode and non-unicode..."
The column in source is nvarchar(32) and in the destination is varchar(32)... ok...
But in the DataReader Source in the SQLCommand I've converted the column into varchar(32) with
convert(varchar(32), Equities.Equities_Name) as CL_FOLDER_ID
Do you have any tip??Thanks!
View 3 Replies
View Related
Oct 15, 2007
Does anyone have a good way to detect LOB datatypes for a table or index? I am doing index rebuilds with the ONLINE option and need to work around indexes with LOB datatypes in them...
Any suggestion?
View 3 Replies
View Related
Sep 25, 2006
Hi All,
how do you convert from a date to an int ? as well as converting from Varchar to and Int
in SQL server 2000 ?
I am retrieving the GetDate() which i store in column as Varchar, i then want to use it within my select statement to calulate data which i want to return i.e
DECLARE @DateValue AS VARCHAR(20)
SELECT @DateValue = ApplicationSettingValue FROM ApplicationSettings WHERE ApplicationSettingKey = 'ProcessDate'
print convert(int,@DateValue) - 5
print GetDate() - 35
SELECT ccy_code, NULL, xrate_date, sterling_xrate
FROM SylvanTrans.dbo.SIADHP_XRate_Hist
WHERE xrate_date >= (CONVERT(int, @DateValue) - 35 )
ORDER BY xrate_date
now my as you can see in my WHERE CLAUSE i want to calculate what is returned using the GETDATE() stored in my @DateValue Variable, but the conversion throws a syntax error:
Server: Msg 245, Level 16, State 1, Line 3
Syntax error converting the varchar value 'GetDate()' to a column of data type int.
what am idoing wrong? i know it is something simple, unless there is no conversion from varchar to int?, i also tried setting the datatype for my variable as datetime but i got the same sort of error with DATETIME replacing the VARCHAR in the error!!!
thanks
regards
View 7 Replies
View Related
Jan 14, 2005
Hello to ALL the ASP.Net, ASP, and SQL Gods!!!!
I have a quick question about SQL Server 2000 supported datatypes!!
I have been trying to search on the "dreaded" Microsoft KB site to try and find information and details about the different supported data types on SQL Serv 2000, and to no avail!!!
Could anyone help me out and give me a rundown on the different data types that are supported on SQL Serv 2000, and maybe a quick explanation for each datatype, so I can make a better database?!??! I would really much appreciate any advice that anyone could offer...
Thanx in advance!!!
CAOTK
View 3 Replies
View Related