|
I have a problem substracting dates in Oracle. I tested the following:
Create a new table with a date field
Insert a register using sysdate
Then a select (sysdate - dateField) doesn't return 0 as expected. In the SQL Developer it returns an empty field.
How can I solve that?
Sometimes, maybe depending on the time difference, it works and returns a value (really, I need (sysdate - datefield) * 24*60*60
Regards,
Diego F.
|
|
|
|
|
To break the diff between 2 dates into days, hours, minutes, sec -- you can use the following:
select to_char( created, 'dd-mon-yyyy hh24:mi:ss' ),
trunc( sysdate-created ) "Dy",
trunc( mod( (sysdate-created)*24, 24 ) ) "Hr",
trunc( mod( (sysdate-created)*24*60, 60 ) ) "Mi",
trunc( mod( (sysdate-created)*24*60*60, 60 ) ) "Sec",
to_char( sysdate, 'dd-mon-yyyy hh24:mi:ss' ),
sysdate-created "Tdy",
(sysdate-created)*24 "Thr",
(sysdate-created)*24*60 "Tmi",
(sysdate-created)*24*60*60 "Tsec"
from all_users
where rownum < 50
/
Regards,
Satips. ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
Could you try that basic sample so I see what is the result?
First create that table:
CREATE TABLE TB_TEST
(
ID NUMBER(2, 0) NOT NULL,
DATEFIELD DATE
, CONSTRAINT TB_TEST_PK PRIMARY KEY
(
ID
)
)
Now, I insert a register:
insert into tb_test values (1, sysdate)
commit;
And then, the select query:
select (sysdate-datefield) from tb_test where id=1
What is the result?
Regards,
Diego F.
|
|
|
|
|
I have 2 tables storing documents as IMAGE Type and version of document
what i want is when i open and save a document if the document is changed then i want to save the version in detail table. Is there any method to compare IMAGE data types in SQL SERVER..?
|
|
|
|
|
value() Method (xml Data Type)
Performs an XQuery against the XML and returns a value of SQL type. This method returns a scalar value.
You typically use this method to extract a value from an XML instance stored in an xml type column, parameter, or variable. In this way, you can specify SELECT queries that combine or compare XML data with data in non-XML columns.
Regards,
Satips. ![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
|
|
|
|
|
static string conn="provider=microsoft.jet.oledb.4.0;data Source="+ Server.MapPath("/data/db1.mdb");
OleDbConnection cn=new OleDbConnection(conn);
the above statement giving the below error:
Error showing:- 'System.Web.UI.Page.Server' denotes a 'property' where a 'class' was expected
please suggest me how to change the code
www.mywebsite.com/data/db1.mdb
please write the connection string for the above website to locate db1.mdb
i will be thank full to you if you reply.
thank u
multimedia9
|
|
|
|
|
Look at the various connection strings at http://www.connectionstrings.com[^], and I'd bookmark the mentioned site for future reference 
"That's no moon, it's a space station." - Obi-wan Kenobi
|
|
|
|
|
Can someone help me understand the meaning of this query I found in a stored procedure in a SQL Server 2005 database?
SELECT @varMID = master_id
FROM tbl_link
WHERE email_address=(SELECT @@identity)
The part I don't understand is the WHERE clause:
1. What does it mean when an identifier is prefixed by TWO '@' chars instead of just one? ( There is no variable declaration for 'identity', BTW. )
2. Is it even a valid SQL statement that appears in the parenthesis, and if so, is it querying from tbl_link ?
Thank you very much for any help!
--------------------------------
"All that is necessary for the forces of evil to win in the world is for enough good men to do nothing" -- Edmund Burke
|
|
|
|
|
Hi
1. @@Identity is a valid sql statement - which returns the last-inserted identity value.
2. This is used after an insert statement if you insert a row to a table where a column is having an identity type.
For example:
Assume the table EmpDetails contains three columns EmpID, EmpName and EmpAge.
EmpID should be auto generated serial no.
So if you run the foll. statement:
INSERT INTO EmpDetails (EmpName,EmpAge)<br />
VALUES ('XYZ',55)<br />
<br />
SELECT @@IDENTITY AS 'Identity'
Displays identity value used in the new row.
Hope you got it.
Harini
|
|
|
|
|
Thanks, now it makes sense! ![Jig | [Dance]](https://www.codeproject.com/script/Forums/Images/jig.gif)
--------------------------------
"All that is necessary for the forces of evil to win in the world is for enough good men to do nothing" -- Edmund Burke
|
|
|
|
|
after insert new record , you can get the new unique ID by this.
|
|
|
|
|
Thanks!
--------------------------------
"All that is necessary for the forces of evil to win in the world is for enough good men to do nothing" -- Edmund Burke
|
|
|
|
|
I used to work in SQL Server 2000 and it's been a while. Now getting into a project with SQL Server 2005. I used to be able to create an auto-filling sequential record ID (effectively a long unique ID) assigned to every record so I could use that as a relational field among related tables. This seems to have been replaced by the uniqueidentifier type in SQL 2005? The problem is that a GUID is effectively a formatted string and a lot of characters, where the long is preferrable for me. How can I get a long int id that is sequential, and therefore unique for each record?
Thanks!
|
|
|
|
|
CREATE TABLE example
(
[ID] bigint IDENTITY (1, 1)
)Works in both SQL Server 2000 and 2005.
|
|
|
|
|
Thanks. I guess working in SQL Server Studio Express isn't the full product and isn't giving me all the options. I'll get the SQL Server access in the next few days and try it on there directly.
|
|
|
|
|
I am using VC++ 6 to access a database using ADO. I have created a record set which contains a variable of type DBTIMESTAMP. I have trouble when I try to update, or insert a record with a field which is a time/date stamp.
I know how to do this with an SQL statement using the Execute method of the connection. What I want to do is use the AddNew or Update method of the recordset to alter, or create a record.
It seems that all values specified must be represented as strings. The string that I use for the time/date stamp however must be the wrong format as I get the type mismatch error.
Any idea what could be wromg?
Mark Kunkel
|
|
|
|
|
You should be OK. I've had no problems using adDBTimeStamp in a recordset in VB.
Be aware that the SQL Server datatype timestamp does not correspond to a date and time. It is a synonym for rowversion, which is simply a unique identifier for the row information and is modified every time the row is updated. You cannot supply data for this column type.
The database type for a date and time is datetime, or if you don't need that wide a range or precision and want to pack a few more rows to a page (can help in reducing I/O when scanning a table), smalldatetime.
|
|
|
|
|
I am trying to a create a stored procedure that accepts 4 input parameters. These parameter are used in the where clause of a select query to return a record from a sql server 2005 database. The record is the result of a three table join. I am getting an error "Must declare the scalar variable '@date'" If I re-arrange the order of the parameter-catching variables, the error will always list the first of the four. I googled the error message and couldn't find anything that helped. I wrote this based on examples in a couple books. And I don't think what I am trying to do is that uncommon.
Can someone please point me in the right direction. I am fairly knew at this.
-----------
use oee
go
create procedure dbo.sp_GetDowntime
@date smalldatetime
@dept nvarchar(3)
@equip nvarchar(25)
@shift nchar(1)
as
SELECT h.oee_date, h.dept, h.equip, h.oee_shift, d.dt_reason, r.name, d.min
FROM userdata_header h
INNER JOIN userdata_downtime d ON h.dept = d.dept AND h.oee_date = d.oee_date AND
userdata_header.equip = userdata_downtime.equip AND h.oee_shift = d.oee_shift
INNER JOIN refer_dt r ON d.dt_reason = r.dt_reasonID
WHERE h.oee_date = @date, h.dept = @dept and h.equip = @equip and h.shift = @shift
|
|
|
|
|
Try:
CREATE PROCEDURE dbo.sp_GetDowntime
@date SMALLDATETIME,
@dept NVARCHAR(3),
@equip NVARCHAR(25),
@shift NCHAR(1)
AS BEGIN
SET NOCOUNT ON
SELECT h.oee_date, h.dept, h.equip, h.oee_shift, d.dt_reason,
r.name, d.min
FROM userdata_header h
INNER JOIN userdata_downtime d
ON h.dept = d.dept
AND h.oee_date = d.oee_date
AND h.equip = d.equip
AND h.oee_shift = d.oee_shift
INNER JOIN refer_dt r
ON d.dt_reason = r.dt_reasonID
WHERE h.oee_date = @date
AND h.dept = @dept
AND h.equip = @equip
AND h.shift = @shift
RETURN (0)
ENDI found the following problems:- The parameters needed commas between them.
- There was a comma in the where-clause.
- The "equip" link between the two tables specified the table-names instead of the table aliases.
- I added "Set Nocount On" because many front-end programming languages by SQL-Server results otherwise.
Regards
Andy
|
|
|
|
|
Thanks, Andy. That was very helpful. Your version works perfectly, of course. I can't believe I missed the commas...where they were needed and where they weren't. I guess the error message was a misnomer. Thanks again.
|
|
|
|
|
Once I have a GB created I can create new controls as children of it, but I can't find a way to drag existing ones inside. I can place them overtop of it, but they're not bound parent-child and stay in place when the GB itself is moved on the form.
--
You have to explain to them [VB coders] what you mean by "typed". their first response is likely to be something like, "Of course my code is typed. Do you think i magically project it onto the screen with the power of my mind?" --- John Simmons / outlaw programmer
|
|
|
|
|
If you have groupbox1 then I guess this piece of code will add new controls as parent-child:
private TextBox textBox1 = new TextBox();<br />
groupbox1.Controls.Add(textbox1);
By the way, you have posted in an incorrect forum. This forum for database stuff
|
|
|
|
|
I'm not asking about a form in C#, c++, etc. The access DB has a built in system to make (ugly) forms. IT uses a vb dialect for events but doesn't expose any of what would be constructor type logic in a real language to the developer.
--
You have to explain to them [VB coders] what you mean by "typed". their first response is likely to be something like, "Of course my code is typed. Do you think i magically project it onto the screen with the power of my mind?" --- John Simmons / outlaw programmer
|
|
|
|
|
The same was true in the VB6 designer. You can cut the control you're trying to move into the group box, then select the group box, and select Paste, IIRC.
|
|
|
|
|
hi,
in the sql we have user_tab_coloumns to know the datatype of a field in a table by properly querying but my problem is i am using an oledbconnection in C# to connect to an access database and i want to know the datatype of a field of a table in that perticular database how to do that?
pls help me.
thanx in advance.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
