Simple Way To Convert '1900-01-01 17:12:00.000' To 5:12pm?

Oct 25, 2006

I have a table called class_time. and the column name is start_time.
datatype is datetime.

id start_time
21900-01-01 15:00:00.000
41900-01-01 17:12:00.000

i want to the convert the start_time column into a format that only
has the hour and minutes plus AM/PM ( for example, 3:00 pm, 5:12pm)

id start_time new format
21900-01-01 15:00:00.000 3:00 pm

is there any simple one step way to do that?

(the only way i know is to use the following query, then do a substring
where the space is. but that's too complicated)
select CONVERT(CHAR(20), start_time, 22) from class_time

Thank you in advance for your time.

View 2 Replies

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

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

Combine And Convert Int Fields To Create One Field, Should Be Simple

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

1/1/1900 Date

Oct 2, 2001

I insert an empty field from a form to a SQL 7.0 table which has a collumn with datetime type and allowing nulls, but I always get a default date 01/01/1900. I don't want this date, how do I get rid of it? Thanks

View 1 Replies View Related

1900-01-01 In Open Xml

Sep 16, 2006

we have a flat text file that has blanks in some of the date columns.
when we use open xml to import data to a table, the blank date values get loaded as 1900-01-01. why does this happen? Is there any way to get these blanks loaded as NULLs to the table.

View 1 Replies View Related

Birth Date 1900-01-01 00:00:00.000

Apr 12, 2005

Hi,all,

How to check the default value set up for datetime field? The reason I am asking is my database

field "birthday" is datetime(8), the default value I set to '' already. Everytime my asp program
have empty birthday or invalid birthdate for this field, the system automaticaly set it to

1900-01-01 00:00:00.000

Do you know what happened?

Thanks!
Betty

View 3 Replies View Related

Dates In Years Prior To 1900?

Jan 13, 2000

I've set up a SQL7 database with MSAccess97 as a front end. I'm trying to enter a person with a birthdate prior to 1900, get an ODBC call error, "Datetime field overflow". How to enter dates prior to year 1900?
Thanks.

View 1 Replies View Related

Null Datetime Field Contains 1-Jan-1900

Feb 10, 1999

I have defined fields in a table as datetime fields and allow nulls. SQL 6.5 is placing 1-Jan-1900 in all of these fields causing me to have to filter out these dates when retrieving a recordset.
How do I get rid of this?

thanks!

View 1 Replies View Related

T-SQL (SS2K8) :: Date Column With 1900-01-01 Value

Dec 23, 2014

I have a date column with 1900-01-01 value, I am trying to get the min(date) without 1900-01-01.My problem here is: there are some records with both 1900-01-01 and real date. How do I get the MIN of the real date without getting 1900-01-01. Below is my query sample.

CASE WHEN MIN(CAST(PROJECTEDSTARTDATE AS DATE)) = '1900-01-01' THEN NULL ELSE MIN(CAST(PROJECTEDSTARTDATE AS DATE)) END as 'ProjectStartDate'

View 3 Replies View Related

SQL Server Automatically Changing Date 1900-01-01 To 2001-01-01

May 16, 2008

I have a SSIS data flow task that downloads data from an Oracle source which has some real dates and some date values of 0001-01-01 which I am trying to convert to '1900-01-01' using a decode(Date1,'0001-01-01','1900-01-01',Date1). The problem I am having is that when I run the package in Business Intelligence Development Studio on my local machine the date value is stored correctly in the SQL table in a datetime field as 1/1/1900 12:00:00 AM, however when I run the package from the server the field gets saved as 1/1/2001 12:00:00 AM.

I have tried a bunch of ways to work around this issue and the only solution I have found that works it to download into a temp table and then load into the live table. Does anyone have any idea of what might be causing this issue when I run the package from the server?

View 1 Replies View Related

Date Selection Giving Default '1/1/1900' For NULL Values

Feb 9, 2005

'XXX_DTE' is character type which wont take NULL.

SELECT CONVERT(Datetime,XXX_DTE) FROM XXXX

I get result as : 1/1/1900

Why is it so.....

What I expect is '0000-00-00 00:00:00.000'

View 1 Replies View Related

How To Find Year From 1900 To 2000 Using Store Procedure In Sql Server

Nov 21, 2006

hi friends
i want to know how to calculate year ranging from 1900 to 2000 using store procedure in sql server

View 8 Replies View Related

Matrix Report, Date: 1900-01-01 Wrong!! Suppose To Be Empty, How?

Aug 10, 2007

Hi
I am making a report in Visual Studio. I��m making a matrix report. I have made a UNION of 2 tables. One of them has a field called Date and the other one does not. But to make a UNION of these 2 tables so that the results are printed in a Matrix I have to have the same fields�� aliases at least. So what I did is that in the second table is:
SELECT �� AS ��Date��
Right?

Now, in the report the first table gives me the dates in a format:
=Format(Fields!Estimated_Close_Date.Value, "yyyy-MM-dd")

But, the second table is it doesn��t have a date in that field when I run the report it gives me:
1900-01-01

Something I don��t want.

So, how do I make it understand that the second tables date field is suppose to be empty?

View 8 Replies View Related

Why Date Columns Default To 1900 After Inserting Dates In SQL Server Everywhere.?

Sep 19, 2006

I have created a sample Database for the school project,

After executing the query below, the Date column is supposed to have the dates I have entered before,

However the dates shown are 1900.

Any idea why is this happening?

I appreciate your help.

Thank you.
Query:

Drop table AccountReceivable
GO
--BEGIN TRANSACTION
Create table AccountReceivable
(
AccountRecID int identity (1,1) not null,
PatientID int not null,
PresentCharges int default 0 not null,
PaymentMade money default 0 not null,
PreviousBalance money default 0 not null,
BalanceDue money default 0 not null,
LastPaymentDate datetime not null,
PresentDate datetime default GetDate() not null
)
GO
ALTER TABLE AccountReceivable ADD CONSTRAINT
PK_AccountRecID Primary Key (AccountRecID)
GO

ALTER TABLE AccountReceivable ADD CONSTRAINT
FK_PatientID_PatientID FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID)
GO
--COMMIT
--query to find delinquent accounts
--DATEDIFF (d, LastPaymentDate, PresentDate)

--Populate the Accounts Table
DELETE AccountReceivable
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )
                    VALUES (913235,451.34,50,0,401.34,4/7/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (918035,109,109,0,0,3/6/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                            VALUES (914235,279,89,0,190,5/9/2005,5/9/2005)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                            VALUES (914235,0,90,190,100,5/9/2005,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (912224,67.90,67.90,0,0,2/2/2006,DEFAULT)
GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (900814,678.32,78.32,0,600,4/6/2006,4/6/2006)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (900814,0,500,600,100,4/6/2006,4/16/2006)

GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (900814,0,100,100,0,4/16/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (913010,203,0,100,303,2/6/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (913010,0,80,303,223,8/3/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (913230,1030.89,1030.89,0,0,4/16/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (918035,78,60,0,18,7/1/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (941235,902,502,0,400,8/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (941235,0,200,400,200,8/15/2006,DEFAULT)

GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (952235,134,24,0,110,4/18/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (952235,0,20,110,90,4/18/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (921635,257.87,57.87,0,200,5/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (921635,0,20,200,180,6/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (915235,1204,200,0,1004,3/15/2006,DEFAULT)

GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (915235,0,100,1004,904,4/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (900035,578,178,0,400,7/10/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (900035,0,100,400,300,7/19/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (913241,157,0,0,157,5/12/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (913241,0,57,157,100,5/16/2006,DEFAULT)

GO

--sample query

select PatientID,PresentCharges,LastPAymentDate,PresentDate from AccountReceivable

GO

--result

PatientID PresentCharges LastPaymentDate PresentDate

----------- -------------- ----------------------- -----------------------

913235 451 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

918035 109 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

914235 279 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

914235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

912224 67 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

900814 678 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 203 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913230 1030 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

918035 78 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

941235 902 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

941235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 134 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 257 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 1204 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

900035 578 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

900035 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 157 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

(24 row(s) affected)

View 6 Replies View Related

Convert Composite Primary Key Into Simple Primary Key

Jan 11, 2007

Uma writes "Hi Dear,
I have A Table , Which Primary key consists of 6 columns.
total Number of Columns in the table are 16. Now i Want to Convert my Composite Primary key into simple primary key.there are already 2200 records in the table and no referential integrity (foriegn key ) exist.

may i convert Composite Primary key into simple primary key in thr table like this.

Thanks,
Uma"

View 1 Replies View Related

Updating Database Date Field Results In Date Value Of 01/01/1900

Jun 18, 2007

Brand new to this, so please bear with me.I'm using the following code fragment to update a datetime field on a SQL Server 2005 database table:cmd.CommandText = "Update Projects Set EntryDate = " & Convert.ToDateTime(txtEntryDate.Text)cmd.ExecuteNonQuery()The result of the update operation is the the database field contains the value "1900-01-01 00:00:00:000". This probably means that I passed nulls to SQL; however, I see a valid date in the txtEntryDate field on my web form (i.e., "06/18/2007"). I also did a "Response.write" to display the txtEntryDate and it looks Okay.Can someone tell me what I'm doing wrong?Thanks!Using Visual Web Developer 2005 Express.

View 1 Replies View Related

Simple Simple Linking Tables & Perform Calculation

Mar 22, 2007

found it

View 3 Replies View Related

Simple Question (Hope Simple Answer Too)

May 26, 2004

Hey,

I have MS SQL database.
I have procedure:

code:--------------------------------------------------------------------------------
CREATE PROCEDURE dbo.Reg_DropTable
@ModuleId varchar(10)
AS
declare @TableName varchar, @kiek numeric
set @TableName = 'reg_'+@ModuleId

begin
DROP TABLE @TableName <- HERE I GOT ERROR
end
GO
--------------------------------------------------------------------------------

I got error when using variable with tables names.
How to do this?

Ps. Number is send to this function and it must drop table with name Reg_[That number]

View 1 Replies View Related

Simple Join Not So Simple

Feb 21, 2007

I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.

result set

colA colB colC colD colE colF colG colG (from yesterday)
1 1 a b c d e m
1 1 a b c d m e

So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.

Any help is appreciated.

View 7 Replies View Related

Simple Join Not So Simple

Aug 19, 2006

Hi,

I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)

I have:

Master table Hotel

table AddressCrossRef (with: RefID = Hotel.ID, RefType = 'Hotel', AddrID)
joins
table Address (key = AddrID)

table MediaCrossRef (with RefID = Hotel.ID, RefType= 'Hotel', MediaID)
joins
table Media (with MediaID,mediaType = 'thumbnail')

foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)

however not all hotels have thumbnail image

hence i have hotel inner join AddressXReff inner join Address ..... however i must have
left outer join mediaXref left outer join media

the problem is that if there is no entry in Media or mediaXref, I don't get any results

i tried to get over it by using
where (media.mediaTyple like 'thumbnail' or media.mediaType is null)
but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned

any clue?

thanks

View 5 Replies View Related

Select Convert(varchar(16), Getdate(), 101)+LEFT(REPLACE(convert(varchar, Getdate(), 108), ':', ''),4)

Sep 26, 2007

select convert(varchar(16), getdate(), 101)+LEFT(REPLACE(convert(varchar, getdate(), 108), ':', ''),4)

From above query I get

mmddyyyyhhmm

but it' s yyyy and hour can not be separated

04/12/200702:05

How can I separated the year and hour ?

Thanks
Daniel

View 2 Replies View Related

This All Seemed Simple Enough...

Mar 24, 2007

...when I started this endeavor.
I have a previously developed Lotus Notes App. The idea was simple; as
I am sefl taught on Lotus Script, I figured I'd be able to stumble my
way through VB.Well,
it started OK. I used VB Express to get familiar with the stuff, but
decided to go with a full version of VS 2005 and try and get this thing
properly developed as a web app. I purchased several reference books
etc., and have become relatively familiar with the forums here.First
issue I have is that I simply want to use code to update or add records
to an SQL DB. I know about datagridview etc., but I want to update the
DB using forms, not the tabular view those controls provide. I thought
it would be relatively straight forward, but found my ignorance runs
deeper than I thought. When I tried to do so I am finding I am not
really clear on where to make declarations etc. in the web app. If anyone could point me in the right direction that would be great. The
issue with searching these forums is most posts deal with datagridview
or something similar.I have spent a ton of time trying to find relevant
posts or articles, but have had no luck yet.Again, all Ireally
need is a nudge in the right direction. I am more than willing to plod
through reference materials or articles/posts to find what I need to
know, I just can't find where to even start on the info I need. Regards, Joe

View 3 Replies View Related

Its Got To Be Simple....

Feb 24, 2008

Hi
I have a problem with my sql WHERE query, if i manually type ([Area] = 'The First Area') then it is okay but if i try and pass the variable 'The First Area' using the

([Area] = @Area) it doesnt work.
ALTER PROCEDURE dbo.StoredProcedure1(@oby nvarchar,@Area char,@Startrow INT,@Maxrow INT, @Minp INT,@Maxp INT,@Bed INT)ASSELECT * FROM(SELECT row_number() OVER (ORDER BY @oby DESC) AS rownum,Ref,Price,Area,Town,BedFROM [Houses] WHERE ([Price] >= @Minp) AND ([Price] <= @Maxp) AND ([Bed] >= @Bed) AND ([Area] = @Area)) AS AWHERE A.rownum BETWEEN (@Startrow) AND (@Startrow + @Maxrow) Please Help I know it must be something simple as the sql works but not when i pass the variable.... Thanks In Advance

View 2 Replies View Related

Simple SQL

Dec 30, 2004

How can i get an output like this from this sql??

HEM_PATIENT_ID is primary key...

Output:
First row: initial values of the fields
Second row: average of the same fields

Please help me...

select * from (
select HEM_LOKOSIT, HEM_NNS
from LPMS.HEMOGRAMS
where HEM_PATIENT_ID = 33
union
select AVG(HEM_LOKOSIT), AVG(HEM_NNS)
from LPMS.HEMOGRAMS
where HEM_PATIENT_ID = 33)
order by HEM_LOKOSIT desc nulls last;

View 1 Replies View Related

Simple Help

May 20, 2001

Am new to SQL Server , can any1 tell me is there anyway to display date in this format month and year like Apr 2000 ,excluding the day.

eg: 01/28/2000 should b displayed like Jan 2000

View 5 Replies View Related

Should Be Simple !!!!

Jan 25, 2001

Hi,

I'm relatively new to SQL7 but I did use 6.5 a fair bit.
I'm trying to test the restore of the Transaction log backup and having a bit of difficulty. The idea is that I make a complete database backup at 1am backup the transaction log every 30 minutes between 7am and 7pm. I need to be able to restore the database to a known state between 7am and 7pm with a max data loss of 30 minutes.

What I am trying to achieve is (as a test):

1)Create a small test database with a test table
2)Add some data to the test table
3)Back up the transaction log
4)Restore the transaction log to 'undo' the data added in step 2

Should be simple I think !!! The problem I am encountering is that in step 4 it won't let me restore only the transaction log (a tick automatically appears in the database backup as well). Bah !

Can someone please tell me what simple steps are required to get this to work. I need specifically on what options to chose during the backup and restore processes.

Thankyou,

Tim

View 4 Replies View Related

Simple SQL

Nov 8, 1999

Given one table with one column and two rows, one containing the string 'Bill', one containing the string 'Gates'.

Write a select statement which gives you the result 'Bill Gates'.

???+

View 1 Replies View Related

Simple SQL

Apr 2, 2003

Hi All,

I have a table with 2 columns which looks like the following.

IDText
-------------
1AAA
1BBB
1CCC
2DDD
2EEE
2FFF
3GGG
3HHH
3III

Each ID can have multiple texts associated with it. I want to write a query that gives me the following output.

IDText
-------------
1AAA; BBB; CCC
2DDD; EEE; FFF
3GGG; HHH; III

I appreciate your help

Thanks

View 1 Replies View Related

Simple SQL

Apr 2, 2003

View 1 Replies View Related

Looks Simple But ...

Jan 11, 2007

Hi,
I have a table with two columns. I need to find distinct value of col1 and the correspondin repeated value of col2 for that col1 value with comma seperated list. Is there any function
for this in MS SQL?
I need somethgn like
a 1,2,3
b 4,5
c 7
d 5,55,5

I can do that with creating 2 cursors but looking for some easy way around.

Any suggestion and help highly appretiate.

Thanks

View 2 Replies View Related

Simple Sql ?

Jan 20, 2006

incorrect syntax near #
how do i fix this and did i make any other errors?

SELECT Master.CheckNum, Master.CheckDate, Master.ExpenseType, Deal.Alias, Detail.InvoiceDate, Detail.InvoiceAmount, Detail.Person, Detail.Deal, Detail.InvoiceNum, Detail.Reference, Detail.idDetial
FROM Master INNER JOIN (Deal INNER JOIN Detail ON Deal.Deal = Detail.Deal) ON Master.CheckNum = Detail.CheckNum
WHERE (((Master.CheckDate)>#12/5/2005#) AND ((Deal.Alias)="aic"));

View 3 Replies View Related

Using And In Simple Sql

Apr 15, 2008

Originally i had:

DELETE FROM #RptDetails WHERE StructureType <> @StructureType
AND #RptDetails WHERE #RptDetails.TraderId <> @TraderId OR #RptDetails.TraderId is null

But it didnt delete the structure types i changed to :
DELETE FROM #RptDetails WHERE StructureType <> @StructureType --AND
DELETE FROM #RptDetails WHERE #RptDetails.TraderId <> @TraderId OR #RptDetails.TraderId is null

and it did, how do i format the 2nd sql into 1 statement and what was i doing wrong?

View 1 Replies View Related