MS SQL Server :: Combine And Convert Int Fields To Create One Field, Should Be Simple

Combine Column Fields Together Into One Field

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

Combine Separate Date && Time Fields Into One Datetime Field?

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

Combine Separate Date && Time Fields Into One Datetime Field?

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

Very Simple Question: Cannot Create Table With Identity Fields Using SQL Sentences

Jan 26, 2007

Hi, I'm trying to build a several tables using sql ce 3.1
I refer to the manual on Create Table:
ms-help://MS.SSCE.v31.EN/ssmprog3/html/143cad25-5c1d-4c96-bd8b-6a95dbfb1b00.htm

The sample:
CREATE TABLE MyCustomers (CustID int IDENTITY (100,1) PRIMARY KEY,
CompanyName nvarchar (50))

just simply doesn't work displaying this error:

SQL Execution Error
Executed statement: CREATE TABLE MyCustomers (CustID int IDENTITY (100,1) PRIMARY KEY,
CompanyName nvarchar (50))

Error Source: SQL Server Compact Edition ADO.Net Data Provider
Error Message: There was an error parsing the query. [Token line number =1,
Token line offset 40, Token in error=IDENTITY]

I tested against a SQL Desktop, everything is OK.
What's wrong with the sentence?
Please some advise or workaround will be appreciated
Thanks
MSCD Fernando Zorrilla de San Martín

View 4 Replies View Related

Very Simple Question Regarding A DATETIME Field; Select Fields Matching Month/day/year

Jan 12, 2006

Hello All,I've got a DATETIME field, and it includes hour:minutes:second data. I want to do selects where I can simply match on the month, day and year. For instance, something like this:SELECT * FROM QuizAttempts WHERE DateTimeTaken = '1/12/2006'And have it match anything that was taken that day, regardless of *when* it was taken. Any suggestions?Thanks! -Josh

View 2 Replies View Related

Join Several Fields To Create New Field

Oct 4, 2006

What would be the recommendation/approach in creating a seperate field in which joins several differate fields together.

I have a table with field name a, b, and c. I want the information in those fields to be populated in a seperate field, d.

So instead of:

a

122

b

joe

c

st

I would have:

d

122 joe st

Thanks!

View 12 Replies View Related

Combine Multiple Field On The Basis Of Employee ID Field

Sep 30, 2015

I need formulating a view through which I can create a an output like below image -

Monthly Table

NUM STATUS ACTIVITYCODE

HAX603 Completed 0x45845a
HAX317 Completed 0x112z44
HAX465 Completed 0x1155x4
HAX523 Completed 0x124c69

Season Table

NUM STARTMONTH STARTDAY ENDMONTH EMDDAY

HAX603 JULY 1 OCTOBER 31
HAX317 DECEMBER 1 DECEMBER 31
HAX317 MARCH 1 MARCH 31
HAX317 July 1 July 28

[Code] ...

Final Output

NUM STATUS ACTIVITYCODE <SEASONS>

HAX603 Completed 0x45845a 1 JULY - 31 OCTOBER, 1 DECEMBER - 31 DECEMBER
HAX317 Completed 0x112z44 1 DECEMBER - 31 DECEMBER, 1 MARCH - 31 MARCH, 1 July - 30 July
HAX465 Completed 0x1155x4 1 MARCH - 31 MARCH, 1 July - 28 July, 1 August - 30 August
HAX523 Completed 0x124c69 1 November - 30 November

I have written a query to join the values of multiple field, but lacking in as how will I formulate a view which will check for the duplicate values of Num fields and merge there values in a single field like season.

select num, (CAST(startday AS VARCHAR(3)) + ' ' + startmonth + ' - ' + CAST(endday AS VARCHAR(3)) + ' ' + endmonth)AS Season from seasons;

View 10 Replies View Related

Grouping By One Field And Combine Data Of Another Field In SSRS

Apr 2, 2008

Hi all experters,

Please suggest how to build the report in below case:

Raw data:

ID
Member
Functions

1
Alan
A

1
Alan
B

2
Tom
A

2
Tom
B

2
Tom
C

3
Mary
D

3
Mary
E

Report Shows:

ID
Member
Functions

1
Alan
A,B

2
Tom
A,B,C

3
Mary
D,E

I group the data by the column ID, but would like to show the functions data by join all functions' values by the same ID.

Any good suggestion?

Thanks in advance,
Steve Wang
2008/4/2

View 6 Replies View Related

Combine Fields

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

Combine 3 Fields Into 1

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

Combine Fields

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

SIMPLE Command To Convert String To Number? Not CAST Or CONVERT.

Aug 15, 2006

Dear Experts,Ok, I hate to ask such a seemingly dumb question, but I'vealready spent far too much time on this. More that Iwould care to admit.In Sql server, how do I simply change a character into a number??????In Oracle, it is:select to_number(20.55)from dualTO_NUMBER(20.55)----------------20.55And we are on with our lives.In sql server, using the Northwinds database:SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2)) as a_number,cast ( STR(r.regionid) as int ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2) ) as a_number,cast (STR(r.regionid,7,2) as numeric ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044Str converts from number to string in one motion.Isn't there a simple function in Sql Server to convertfrom string to number?What is the secret?Thanks

View 4 Replies View Related

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 View Related

Combine Records On Two Fields

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

Combine Two Fields Using AS For Comparing To A Value

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

Simple Cursor To Combine Records

Sep 28, 2007

I need to return one record with concatenated string fields from a table that may contain several records. I think a cursor will be able to do what I want, but I'm not very experienced at writing them.

My data

HDR DMCD
107 TEX
107 AIR
107 LG
108 TEX
108 CAR
109 SM

I want the result of my query to find adn return each header and return the 1 or more DMCD field values concatenated. i.e.

107 TEX AIR LG
108 TEX CAR
109 SM

This is my attempt at the cursor so far

SET NOCOUNT ON

DECLARE @AACODE varchar(50),@hdr varchar(20),@dmcd varchar(20)

DECLARE AAROW_cursor CURSOR FOR

SELECT aaglhdrid,aatrxdimid

FROM aag30003

OPEN AAROW_cursor

FETCH NEXT FROM AAROW_cursor

INTo @hdr, @dmcd

WHILE @@FETCH_STATUS = 0

BEGIN

set @aacode=@aacode+@dmcd

select @hdr,@dim,@aacode

FETCH NEXT FROM aarow_cursor

INTO @hdr, @dmcd

END

CLOSE AAROW_cursor

DEALLOCATE aarow_cursor

View 5 Replies View Related

Combine And Convert A Date?

Dec 5, 2007

how do I convert DATEPART(year, GETDATE()) - 1 into a string so I can combine with
'10/1/' to form '10/1/2006'

select '10/1/' + DATEPART(year, GETDATE()) - 1 AS StartDate

View 1 Replies View Related

Combine Fields And Text In Select Statement

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

Union Query Or Some Other Way To Combine Fields And Text

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

How To Combine Date And Time Into One Field

Jan 27, 2012

We have a piece of software and database for student registers.

One of the biggest problems is the database has been difficult to work with, as I have had to work out the date using dateadd functions and combine fields from different tables.

I've now got the date correctly, but I want to add the time to the time part of a datetime field.

The time field is already stored in a datetime field, but the date in this field is always 1899-12-30.

This is the function I am using to get the date:

Code:
DATEADD(dd, e.day_of_week, DATEADD(ww, sm.week, '01/08/2011'))

And this is the function I use to get the time (from a datetime field):

Code:
SUBSTRING(CAST(e.start_time AS varchar(20)), 13, 5)

With the DATEADD function it appears you can only add individual elements each time hh, mi, ss.

Would I have to add a DATEADD function for each time element in order to add the time to the date?

View 2 Replies View Related

Combine Text Field Based On Value

Feb 27, 2014

I have one table with text column(varchar50) and 2 value columns(INT)

it looks like this:
c1, c2, c3
1, null, text1
2, null, text2
3, null, text3
4, 3, text4

What i want to do is to combine c3 on row 3 and 4 so the output would look like this:
c1, c2, c3
1, null, text1
2, null, text2
3, null, text3
4, 3, text3 ext4

I have tried to use CASE with no luck.

edit:
output like this is also fine
c1, c2, c3
1, null, text1
2, null, text2
3, null, text3
4, 3, text4
5, null, text3 ext4

View 6 Replies View Related

How To Combine Fromdate And Todate Values In One Field

Aug 16, 2007

Hi All,
I have to fetch FromDate and Todate values from the table like this.Suppose Fromdate value is 02-Feb-2007 and Todate Value is 04-Feb-2007,then my need is to get the date value like this......Feb 2-4,2007or 2-4 Feb,2007.Can anybody know the syntax or code?.I am using sql Server and fromdate and todate values are stored in two different feilds in table.
Thanks and Regards

View 1 Replies View Related

Combine City Field With Prov/State

Jun 4, 2007

Hello I am new to SQL Server 2005 and am designing my first database.

In the AdventureWorks sample database the city field is included with the street address where it repeats for each row.

Why would you not put it in a separate table with state/province or a separate table all togeather?

Thanks for your help.

View 5 Replies View Related

Separating One Field Into Two Fields Based On A Character In The Field

Jul 20, 2005

I know there has to be a way to do this, but I've gone brain dead. Thescenario..a varchar field in a table contains a date range (i.e. June 1,2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). Theusers have decided thats a bad way to do this (!) so they want to split thatfield into two new fields. Everything before the space/dash ( -) goes intoa 'FromDate' field, everything after the dash/space goes into the 'ToDate'field. I've played around with STRING commands, but haven't stumbled on ityet. Any help at all would be appreciated! DTS?

View 1 Replies View Related

Pass In Null/blank Value In The Date Field Or Declare The Field As String And Convert

Dec 30, 2003

I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.

I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable

mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))

option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.

With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End

Please help

View 6 Replies View Related

How To Convert Datetime Field To A Date Field So Excel Recognize It As Data Type

May 17, 2015

I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .

How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?

I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)

**all of the above returned text objectes in excel and not date objects.

View 3 Replies View Related

Convert Field From VarChar To Int With Speical Characters In Field

Aug 29, 2007

Hello,

I have a table with a column that is currently a varchar(50), but I want to convert it into an int. When I try to just change the type in design mode I get an error that conversion cannot proceed. When I look at the field it appears some of the entries have special characters appended at the end, I see a box after the value.

How can I remove all speical characters and then convert that field to an int?

Also I tried the following query which did not work as well, same error about conversion.

UPDATE myTable SET field = CAST(field AS int)

View 2 Replies View Related

SQL Server 2014 :: Combine Records That Are Identical And SUM Values In Price Field

Sep 30, 2015

I have a robust query that returns a dataset and the data is good, however some of the records contain the exact same data with the exception of the 'Price' field. I want to combine the records that are identical and SUM the values in the 'Price' field. My query and example return dataset is below.

Query:
--------
select distinct
'On-Demand' as 'Business Line',
o.OrderID as 'Order #',
isnull(d.DisplayCode,'UNK') as Hub,
isnull(rz.RouteID,'UNK') as 'Default Route',
'On-Demand' as 'Assigned Route',

[Code] ....

View 4 Replies View Related

Convert Var Char Field To Date Field

Sep 27, 2005

Hi,

I need to convert a var char field to date field (DD/MM/YYYY)

Current convertion format:
CAST(Report_Date as DATETIME)

How can i convert to Date field with date format of DD/MM/YYYY?

Thanks.

Onn Onn

View 2 Replies View Related

Simple Convert Question!

May 18, 2006

Hello all!

Never did a convert, I been looking for samples but I don't quite get it. I get this error message in my vb.net app, Taking a number value from a textbox, adding it to a table, with a money field.

"Implicit conversion from data type money to nvarchar is not allowed. Use the convert function to run this query"

Here is the SP it's pulling,

CREATE PROCEDURE dbo.AddWg (@UserID NvarChar(50),
@Credits Money)
AS
IF EXISTS (SELECT 1 FROM Bankroll WHERE UserID=@UserID)
begin
Update BankRoll
Set Credits = Credits + CAST(@Credits as money)
where userID = @userID
return -1
end
ELSE
begin
Insert Bankroll (Credits, UserID) Values (@Credits, @UserID)
RETURN @@IDENTITY

end
GO

The store procedure worked when Credits was nvarchar, but then I switched it to money, and now this. How would I use the CONVERT to fix this?

TIA!!!

Rudy

View 3 Replies View Related

Query To Search All Fields In Simple Table

Mar 1, 2006

I am trying to write a simple search page that will search all the fields in a database to find all records that match a user input string. The string could happen anywhere in any of the fields. I have a dataset and can write a query but am unsure what the format is for this simple task. I figured it would look like this:

SELECT Table.*
FROM Table
WHERE * = @USERINPUT
But thats not working. Can someone help.? Thanks..

View 1 Replies View Related

How To Combine Multiple Rows Data Into Single Record Or String Based On A Common Field.

Nov 18, 2007

Hellow Folks.
Here is the Original Data in my single SQL 2005 Table:
Department:                                            Sells:
1                                                              Meat
1                                                              Rice
1                                                              Orange
2                                                              Orange
2                                                              Apple
3                                                             Pears
The Data I would like read separated by Semi-colon:
Department:                                            Sells:
1                                                             Meat;Rice;Orange
2                                                             Orange;Apple
3                                                             Pears
I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..

View 2 Replies View Related