|
Hi Fritzables,
It is very difficult to say on which track you are, without knowing the error message. But I expect the problem is the date format. You can try two things:
SET DATEFROMAT DMY
Or you can format the string to:
'2007-05-20 5:30PM'
I hope this helps.
Wout Louwers
|
|
|
|
|
G'Day Wout,
If I was to enter the sting at: 30/05/2007 5:30PM then the error message goes something like: syntax error near 5.
Ok, so if I was to use the SET DATEFORMAT DMY, at what point would I define this - sorry for the stupid question.
Regards
Fritzables.
|
|
|
|
|
Ah, the errormessage explains something. You should try:
INSERT INTO MyTable(Date) VALUES('30/5/2007 5:30PM')
I think you forgot the quotation marks.
If that does not work you can try:
SET DATEFORMAT dmy;INSERT INTO MyTable(Date) VALUES('30/5/2007 5:30PM')
Wout Louwers
|
|
|
|
|
Wout - you're an absolute legend !!!
Yea, it was the missing quotes that put all things back in order.
Thanks a million.
Fritzables.
|
|
|
|
|
Hi,
I want to develop an application for pocket pc and desktop with VB.NET. For this application I want to use only one database. This will be sql server 2005 and not sql server 2005 CE. I really don’t know how to do this. I have search the internet on this subject, but found nothing, except RDA which is using sql server 2005 CE.
I will appreciate any suggestion/tips from you on this subject.
GR,
|
|
|
|
|
Hi i have one employee month record in my table...,
My table look like:
Id , SDate , M1 , M2
100 4/1/2007 78 45
100 4/2/2007 89 65
.
.
.
100 4/30/2007 87 67
Like this my table is there...,
Now i want to show sunday records separately and other weekdays records separately...,
Plz help me...,
Magi
|
|
|
|
|
You can use DATEPART to filter by day of the week.
|
|
|
|
|
True as far as it goes. However, the problem with applying any function to the data in the table is that the query optimiser cannot use an index on that column, if there is one. If this is the only column in the WHERE clause, or the only column in the GROUP BY or ORDER BY clauses, the query optimiser may have to resort to a table scan, i.e. reading all the rows in the table from beginning to end (it will instead show as a Clustered Index Scan in the query plan if there's a clustered index on the table).
If having an index on this information would be helpful, consider adding an extra column which contains the weekday data, and indexing that.
Be aware that the first day of the week is different in different locales and the return value of DATEPART is therefore affected by the connection settings - you should check the value of @@DATEFIRST in any calculations. For that reason I believe the DATEPART function is considered non-deterministic and it may not be possible to add a computed column and index that - it may need to be a real column. You could use a trigger to update it as required.
|
|
|
|
|
hi friends
how to delete two tables using single delete query in sqlserver2000
regards
saravanan
|
|
|
|
|
saravanan05 wrote: how to delete two tables using single delete query in sqlserver2000
Why do you want to use a single delete query? What is wrong with two delete statements inside a transaction?
|
|
|
|
|
Hi
You can drop more than 1 table but you can delete two tables
drop t1,t2 (two tables will be dropped from database)
but it is not possible to delete records as in delete from t1,t2
Which one you are asking ??
Harini
|
|
|
|
|
Hi Friends
I have table containing prd_id,version_no
Now I want to get product,and their latest version_no
how go about
kindly help me
regards
chandru
|
|
|
|
|
dear chandru,
i cannot understand ur question fully.if the version is an integer number u can draw the maximum value using max() function.can u pls describe ur question in detail
shally
|
|
|
|
|
Hi shally
thank you for replying
take for example i have these details in my table
prd_id prD_name ver_no
01 abc 1.5
01 abc 2.4
02 xyz 1.0
02 xyz 1.2
02 xyz 2.0
03 xxx 1.0
04 yyy 2.0
i need latest version of each product like
01 abc 2.4
02 xyz 2.0
03 xxx 1.0
04 yyy 2.0
hope u understood
thank you
regards
chandru
|
|
|
|
|
hello chandru,
pls use the following query
if u find any errors pls reply me
select prd_id,prD_name,max(ver_no) from products
group by prd_id,prD_name 
shally
|
|
|
|
|
dear shally
yes, it is working
thank you very much
chandru
|
|
|
|
|
I have the following stored prodecure and I need to add the average time of the "Total closed for range" was opened for.
(
@startdate datetime,
@enddate datetime
)
as
select count(*) as 'Total closed for range', Priority_type_name 'Priority', location_name 'Cient', email 'Engineer'
from job_ticket j
inner join priority_type p on p.priority_type_id = j.priority_type_id
inner join tech t on t.client_id = j.assigned_tech_id
inner join location l on l.location_id = j.location_id
where (last_status_update_time between @startdate and @enddate) and status_type_id ='3'
group by l.location_name, t.email, p.Priority_type_name
The where clause is specifying when the last update = 3 which is closed.
I suspect I need a sub query for a field named report_date and my where clause (last_status_update_time between @startdate and @enddate) and status_type_id ='3'?
here is what the table looks like:
ASSET_ID int Checked
ASSIGNED_TECH_ID int Checked
BILLING_RATE_ID int Checked
BILLING_TERM_ID int Checked
CLIENT_ID int Checked
CLOSE_DATE datetime Checked
DELETED int Checked
DEPARTMENT_ID int Checked
DISCOUNT money Checked
DUE_DATE_OVERRIDE datetime Checked
DUE_HOURS_MANUAL int Checked
FIRST_RESPONSE_DATE datetime Checked
IS_HOT int Checked
JOB_COST money Checked
JOB_TICKET_ID int Unchecked
JOB_TIME int Checked
LABOR_TAX_RATE money Checked
LAST_REMINDER_DATE datetime Checked
LAST_STATUS_UPDATE_TIME datetime Checked
LAST_UPDATED datetime Checked
LOCATION_ID int Checked
LOGGED_BY_ID int Checked
MAIL_CC_ADDRESS varchar(255) Checked
MODEL_ID int Checked
PARENT_ID int Checked
PHONE varchar(40) Checked
PO_NUMBER varchar(75) Checked
PRIORITY_TYPE_ID int Checked
PROBLEM_TYPE_ID int Checked
QUESTION_TEXT varchar(4000) Checked
REPORT_DATE datetime Unchecked
ROOM varchar(80) Checked
SEND_CARBON_COPY int Checked
SEND_CLIENT_EMAIL int Checked
SEND_TECH_EMAIL int Checked
SHIPPING money Checked
SHOW_DUE_DATE_ON_CALENDAR int Checked
STATUS_RED_NOTIFICATION_DT datetime Checked
STATUS_TYPE_ID int Checked
STATUS_YELLOW_NOTIFICATION_DT datetime Checked
SUBJECT varchar(255) Checked
SUBSCRIBER_ID int Checked
TASK_ELEMENT_ID int Checked
TECH_GROUP_ID int Checked
TICKET_TIME_AS_OF_LAST_UPDATE int Checked
TRAVEL_COST money Checked
TRAVEL_RATE_ID int Checked
TRAVEL_TIME int Checked
UPDATED_FLAG int Checked
USE_DUE_DATE_OVERRIDE_INTEGER int Checked
WORK_END_DATE datetime Checked
WORK_START_DATE datetime Checked
CC_ADDRESS_FOR_CLIENT varchar(255) Checked
INITIALIZED_CUSTOM_FIELDS int Checked
IS_PRIVATE int Checked
IS_TAX_FREE int Checked
LAST_CLIENT_REMINDER_DATE datetime Checked
LAST_CLIENT_UPDATE datetime Checked
SEND_TO_CLIENT_CC_LIST int Checked
SERVICE_TIME_ENABLED int Checked
SURVEY_RESPONSE_ID int Checked
TASK_ELEMENT_COMPLETE int Checked
UPDATED_BY_TECH_FLAG int Checked
ESCALATION_LEVEL int Checked
Unchecked
Results currently look like:
Total closed for range....... Priority........ Cient......... Engineer ........
24 ............................... Sev1.............. CA ............. swilliams........................
Desired Results:
Total closed for range....... Priority........ Cient......... Engineer ........ Avaerage time open
24 ............................... Sev1.............. CA ............. swilliams........................ value
I can not wrap my head around this for the life of me @ all.....any help or direction would be greatly appreciated.
Regards,
Hulicat
|
|
|
|
|
Got it...Thanks Srinivas!
select count(*) as 'Total closed for range', Priority_type_name
'Priority', location_name 'Cient', email 'Engineer' ,
AVG(datediff(d,report_date,last_status_update_time )) AS [Avaerage
time open]
from job_ticket j
inner join priority_type p on p.priority_type_id = j.priority_type_id
inner join tech t on t.client_id = j.assigned_tech_id
inner join location l on l.location_id = j.location_id
where (last_status_update_time between @startdate and @enddate) and
status_type_id ='3'
group by l.location_name, t.email, p.Priority_type_name
Regards,
Hulicat
|
|
|
|
|
Hi,
I'm trying to get running sql servers in my machine and I'm using SqlDataSourceEnumerator class. The code below does not return any result.
SqlDataSourceEnumerator servers = SqlDataSourceEnumerator.Instance;
DataTable table = servers.GetDataSources();
table is null.
Why can't I get these servers?
Thanks..
Savas Cilve
|
|
|
|
|
Hi there,
Is there a way to include a value from the affected record into a AFTER UPDATE trigger? And if there is a way, can you give me an exapmle, please?
Thanks, Werries
A programmer's life is good... or is it?? Ek dink nie so nie!
|
|
|
|
|
Use the inserted or deleted pseudo-tables depending on whether you want the values after or before the update occurred. Remember that more than one record can be affected by a statement, but that the trigger only fires once, regardless of the number of records affected.
|
|
|
|
|
Thanks for your reply Chris, I appreciate it.
Regards, Werries
A programmer's life is good... or is it?? Ek dink nie so nie!
|
|
|
|
|
Werries wrote: Chris
Who's Chris?
|
|
|
|
|
Hi everybody.....searched all over the place and couldn't find any info: I'm building an ASP.NET maintenance form to query/upsert records into an Oracle table. I haven't worked with PL/SQL in a while, and I can't find any straight forward help on syntax and my 9i Ref manual is at home :oP . ...Anyway, I'm trying to put my stored proc in a package and it won't let me specify the scale & precision of a number. When I desc the table, I see that the field is a
NUMBER(6,5)
So, I did this:
(Inside Package Spec)
Procedure Upsert_xxx(p_startDate IN DATE,
p_stopDate IN DATE,
p_scaleFactor IN Number(6,5)
);
and matched up the Package Body...I keep getting the following msg (SELECT * from USER_ERRORS)
PLS-00103: Encountered the symbol "(" when expecting one of the following:
:= . ) , @ % default character
The symbol ":=" was substituted for "(" to continue.
on the specification of p_scaleFactor inside the Package Spec. wtf???
Welcome my son...Welcome..to the Machine
|
|
|
|
|
Unlike other databases you don't specify the size of the parameter just the type
Create proc xxx_Update(
p_Name IN nvarchar2
,p_age IN number
)
Or even better is:
create proc xxx_Update(
p_Name IN TableName.ColumnName%TYPE
,p_Age IN TableName.ColumnName%TYPE
)
For some concise examples of proc/procedures try http://www.techonthenet.com/oracle/index.php [^]
hth
Al
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
