|
writing query for your requirement will be bit tough. Why don't you use date fields rather than storing date like this. In date fields you can use > or < operators to get the dates in between two dates.
|
|
|
|
|
Thanks for ur reply navaneeth...,
s navaneeth, i amn't getting date format from my end user...,
Just i am getting month and year only...,
So cant say in dateformat in my table...,
But any way thanks for ur suggestion...,
See andy's first query is giving solution for this probs...,
Keep in touch,
Friendly,
Regards,
Magi
|
|
|
|
|
SELECT * FROM TblA
WHERE Convert(Datetime, '01/'+sMonth+'/'+sYear, 103) BETWEEN '01-Jun-2007' AND '30-Jun-2008'
Regards
KP
|
|
|
|
|
You are right, the only thing is not all months have last day as 30, it can be 30, 28, 31... but well this is the right way to do anyway, there is a store procedure which gets the last day of the month in a particular year, can always use that.
|
|
|
|
|
given Month & Year are constant (june, 2007, 2008)
Also the date which is derived is always 1st.
Regards
KP
|
|
|
|
|
Hi all
i have a case where i have 3 tables
table 1
blId blDesc
1 test1
----------
table 2
bl2Id bl2Desc blId
1 test2 1
-------------------
table 3
id name description
1 bilal test1.test2
the description of table3 (test1.test2) containing description from table1 and its child from table2
how can I query table3 and have instead of test1.test2, 1.1 (The Ids)
Please Help
|
|
|
|
|
For SQL-Server, you could try:
select table3.description,
convert(varchar, table1.blId) + '.' + convert(varchar, table2.bl2Id) as TheIds
from table3
inner join table1
on table3.description like table1.blDesc + '.%'
inner join table2
on table3.description like '%.' + table2.bl2Desc
order by table1.blId, table2.bl2IdRegards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Hi All,
I have a table with some columns. My project is when I upade any of the
column then I want a trigger to execute in a column named DATE_UPDATED. i
have the following trigger but when I update any column it doesn't allow me
to update the table manually. Where is the problem. e.g if I try to update
column name ADDRESS1 it gives the error message
ERROR MESSAGE = The transaction ended in the trigger. The batch has been
aborted.
Here is my trigger.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [trgUddated]
ON [dbo].[DELTA_CARRIER]
FOR UPDATE
AS
If UPDATE(CARRIER_NAME)
BEGIN
PRINT('AFTER Trigger [trgUddate] - Executed - Carrier Name has
been updated')
ROLLBACK TRANSACTION
END
else If UPDATE(ADDRESS1)
BEGIN
PRINT('AFTER Trigger [trgUddate] - Executed - Address1 has been
updated')
ROLLBACK TRANSACTION
END
else If UPDATE(ADDRESS2)
BEGIN
PRINT('AFTER Trigger [trgUddate] - Executed - Address2 Name has
been updated')
ROLLBACK TRANSACTION
END
else If UPDATE(CITY)
BEGIN
PRINT('AFTER Trigger [trgUddate] - Executed - City has been
updated')
ROLLBACK TRANSACTION
END
else If UPDATE(STATE)
BEGIN
PRINT('AFTER Trigger [trgUddate] - Executed - State Name has
been updated')
ROLLBACK TRANSACTION
END
else If UPDATE(ZIP_CODE)
BEGIN
PRINT('AFTER Trigger [trgUddate] - Executed - Zip code has been
updated')
ROLLBACK TRANSACTION
END
else If UPDATE(PHONE)
BEGIN
PRINT('AFTER Trigger [trgUddate] - Executed - Phone Name has
been updated')
ROLLBACK TRANSACTION
END
UPDATE DELTA_CARRIER
SET DATE_UPDATED = GETDATE()
WHERE CARRIER_ID = 7551
seema
|
|
|
|
|
If you want your manual updates to succeed then remove the "Rollback Transaction" statements from your trigger.
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
I have the following challenge:
I have a table "Table1" with two columns:
Column A int
Column B int
I am using the combination of A and B as a unique identifer.
I suspect there is bug in the stored procedure, therefore, I need to find where there is a duplicate row where coulmn A and B values in one row match that of another row.
What method should I use to do this?
Any help is greatly appreciated
Regards,
Hulicat
|
|
|
|
|
I got it thanks
Regards,
Hulicat
|
|
|
|
|
I use this connection string to connect to a DB on the same computer as the application.
Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;
Now I want to connect from my application to a remote database on remote server through local network. How should I modify the connection string?
|
|
|
|
|
http://www.connectionstrings.com[^] can help you out. You are going to have to point the connection string to the ip address of the remote server. Frankly, I have not done any Oracle work in the past year or so, the site I provided is a very useful site that is worth bookmarking 
"I've seen more information on a frickin' sticky note!" - Dave Kreskowiak
|
|
|
|
|
So I will have to do something like this?
Provider=OraOLEDB.Oracle;Host=xxx.xxx.xxx..xxx;Port=1521;Data Source=MyOracleDB;User Id=myUsernameassword=myPassword;
Do I still need the "Data Source" option?
|
|
|
|
|
hi,
i have .rdlc report. which is grouped on a field. Every thing is working fine except one that same group contents print on two different pages. My mean is that some contents of same group print at bottom part of page & remaining contents print on top part of next page.
i want to shift whole contents of group to top part of next page (if there r not enough space at bottom part of current page). is there r any option to Keep together group ?(as in crystal report)
i think u properly understand my problem.
Please guide me or provide me some link . any help will be greately appreciated.
thanks in advance
Rupesh Kumar Swami
Software Engineer,
Integrated Solution,
Bikaner (India)
|
|
|
|
|
Hi All
What is wrong in this Query I want only one record of a customer. If he have many records.
If I remove the othere tables from the queyr then it works fine.
SELECT DISTINCT cust_order.customer_id AS cust_id, cust_order.order_id AS order_id, customers.fname AS fname, customers.lname AS lname, cust_order.order_date AS order_date, cust_order.order_status AS order_status
FROM cust_order, customers, cust_debit
WHERE customers.customer_id=cust_order.customer_id and cust_order.order_id = cust_debit.order_id and cust_debit.amount >=2000;
pls help me.
Thanks in Advance.
|
|
|
|
|
The DISTINCT works across all the columns you have selected. If you only want one row per customer you are going to have to define which particular row you want to take the rest of the data from.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
|
The "DISTINCT" keyword tells SQL-Server to return unique rows. Your query is basically returning one row per order.
select latest_order.customer_id AS cust_id
latest_order.order_id AS order_id,
customers.fname AS fname,
customers.lname AS lname,
latest_order.order_date AS order_date,
latest_order.order_status AS order_status
from customers
inner join (
select lastest_order_id.customer_id, lastest_order_id.order_id,
lastest_order_id.order_date, latest_order_id.order_status
from (
select customer_id, max(order_id)
from cust_order
group by customerId
) as lastest_order_id
inner join cust_order
on cust_order.order_id = lastest_order_id.order_id
) as latest_order
on latest_order.customer_id = customers.customer_idNote that the above SQL does not refer to your "cust_debit" table because I cannot tell whether it has a one-to-one or a one-to-many relationship with your "cust_orders" table. You will need to modify the "lastest_order_id" inline-view in the above SQL to handle this restriction.
Hope that helps.
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Thank u very much its realy helpful.
I m realy very thankful to u .
|
|
|
|
|
You deserve FIVE(5) and you allready get it by me, even though the soulution wasn't for me.
I Love SQL
|
|
|
|
|
Hi,
i hava dataGrid of 100,000 record ,i want to update the Edited data in the
Oracle DataBase,but i dont want to use the Adapter.update() method ,i want to
send the whole data to my storedProcedur and that procedure will update the Record in the database.
best regards
nabeel
|
|
|
|
|
Is this[^] what you want?
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
hi
i have a problem here
i have a column in database the data type is DateTime
i insert into this column Time only LIKE " 09 : 00 AM "
but after i insert data it stores in table date with the time i entered
the problem is
i want to select the data from this column as Time Only
i try this select convert(smalldatetime ,ExamTime,108)
but i back by the date also
how can solve my problem
thnx 4 help
MD_NADA
|
|
|
|
|
did you try to convert in varchar type?
select convert(varchar,ExamTime,108)
I Love SQL
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
