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
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.
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
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
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.
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.
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
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?
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?
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?
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?
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?
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?
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.
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.
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.
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?
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.
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.
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...
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'
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!!!
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...
