|
Hello,
I am trying to read a DateTime value from an Oracle database (Oracle Version 11.2.0.1.0 running on a German Windows XP). The column is defined as "DATE", and the value is correctly shown as "2010-05-04 16:17:10.0" via the web browser when viewing all the data of the respective table.
But when I retrieve the data with the OracleClient (via OracleCommand and OracleDataReader) in a C# application (.NET 2), I receive a string (!) value of
"04.05.10 " , i.e. reader["LASTLOGIN"] returns a string with that value which I then convert to a DateTime using the Convert.ToDateTime() function.
By the way, a
int lastLoginCol = reader.GetOrdinal("LASTLOGIN");
LastLogin = reader.GetDateTime(lastLoginCol);
yields a "Specific cast is not valid" error message.
Please note the 6 blanks after the date part. Where has the time part gone to?
How can I retrieve the full date time value? Is the answer: "Not at all, it's Oracle"?
And what about a situation where the server is running with a different locale than the client? Is it really necessary to change to CHAR 14 (i.e. YYYYMMDDhhmmss - that does work with some tricks)?
Thanks a lot for your ideas!
Bernhard
|
|
|
|
|
The Oracle DATE type for the column does not have a corresponding equivalent within most languages you will use. As a result it must be cast to some other intrinsic type. Usually what will happen is that Oracle will convert it to a string using a session specific date format specification. Look up on NLS_DATE_FORMAT and if you don't know what that format is, the following might help you
SELECT value FROM v$nls_parameters WHERE parameter ='NLS_DATE_FORMAT';
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
In .NET all you have to do is open an OracleDataReader from an OracleCommand and then call GetDateTime. It works for me. .NET should handle all the type conversion issues: the OracleDataReader "knows" how to convert SQL types to .NET types so that I don't have to. It's called encapsulation. Or maybe abstraction, I can never remember the difference between those two.
|
|
|
|
|
Thanks a lot for your input. The problem has dissolved somehow - I use to shutdown the server and the client in the evening and start them again next morning, and now the problem cannot be reproduced. Maybe there was some inconsistent state in the Oracle database, during development things can go wrong and Oracle likes to punish you horribly for the smallest misdeeds....
|
|
|
|
|
Hi All,
I am struggeling finding an effectient way to find a distance between a Point that intersects a polygon and the border of that polygon. I was able to use the STDistance comparing the point to every point that made up the polygon but that is taking a lot of time. Using SPatial indexe wasn't much helpful because the STDistance is not part of any constraint and even when I did put the constraint, the index didnt help much.
I appreciate any feedback.
Thanks
|
|
|
|
|
Looks like you might have to write some code. For the basics, google "distance point line". Once you have that, you just need to find the minimum of the distances to the edges of the polygon. Calculation should be O(n) for an n-gon.
Software rusts. Simon Stephenson, ca 1994.
|
|
|
|
|
Thats what I am doing but its freakin slow. After a lot of research I found an excelent blog describing an efficient way to do this:
Nearest neighbors[^]
|
|
|
|
|
hi iam using sql server 2000
my table is branch
BranchName nvarchar(50),
ContractNo nvarchar(50),
Email nvarchar(50),
StartDate datetime,
EndDate datetime,
ContractReminder int,(5days,10days,15days)
remindertype int(1month,3month,6month,annual)
i have to send the email to the user before enddate and check the ContractReminder if it is for 5 days then send before 5days ,if it is for 10days then before 10 days and also check the remindertype if it is for one then before one month before contractreminder ie 5 days or 10 days i have to send the mail like this for three months, 6 months , and for annually
how to write this querry can you give an idea which helps me
|
|
|
|
|
It looks like you need a job to run every day that by using two datediff criteria using an OR between them.
This could then get the list that is sent to.
Sorry I cannot be more specific as I am installing SQL 2008 R2 on my system, so I cannot try an example.
See BOL for Datediff and you will use days I would suggest using days for the month also as that would prevent the letter from being sent every day of the month.
SELECT * FROM branch
WHERE DATEDIFF('dd', GETDATE(), EndDate) = ContractReminder
OR DATEDIFF ('dd', GETDATE(), EndDate) = ReminderType * 30 NOT TESTED
Good Luck,
djj
|
|
|
|
|
hi iam using this querry
SELECT BranchName,ContractNo, Email,StartDate,EndDate, ContractReminder,
DATEADD(mm,ReminderType,StartDate)-ContractReminder as newd
it works fine for the first time only for 1 month, 3months,or 6months,
suppose the starttime is 2010-01-01 and enddate is 2010-12-31
the querry will works fine for only first time for 1st month, or 3rd month, or 6th month
but i want the querry to check upto enddate repeatedly ie for a year i want the querry to be executed 12 times, if it is for 3 months the querry should be executed for 4 times in a year
how can i get this querry please give suggesstion
|
|
|
|
|
A suggestion would be
SELECT BranchName, ContractNo, Email, StartDate, EndDate,
CASE
-- notice that the 101 is my favorite see BOL Convert for list of others
WHEN CONVERT(VARCHAR, DATEADD(day, ContractReminder, GETDATE()), 101) > EndDate
THEN 'Less than ' + CAST(ContractReminder as VARCHAR) + ' days left'
WHEN CONVERT(VARCHAR, DATEADD(month, remindertype, GETDATE()), 101) > EndDate
THEN 'Less than ' + remindertype + ' months left'
END AS MyFlag
INTO #RList
WHERE CONVERT(VARCHAR, DATEADD(day, ContractReminder, GETDATE()), 101) > EndDate
OR CONVERT(VARCHAR, DATEADD(month, remindertype, GETDATE()), 101) > EndDate
|
|
|
|
|
try something like this.
*assumption that all contract end dates are in the future
select branchname, contractno, email, startdate, enddate, contractreminder, remindertype
from branch
where convert(varchar(20), enddate,112) = convert(varchar920), dateadd(dd, contractreminder, current_timestamp),112)
-- this will then removes the issues of the times in the datetime column
Marc Clifton wrote: That has nothing to do with VB. - Oh crap. I just defended VB!
|
|
|
|
|
I have to search keyword "searching"
Out of this keyword i have to find any 8 characters in the following string
"string to search" .
Can u suggest any approach ?
|
|
|
|
|
look at the patindex function.
Marc Clifton wrote: That has nothing to do with VB. - Oh crap. I just defended VB!
|
|
|
|
|
Hi..
please solve my prob i try this query
select PRODUCTID,u_price, max(qc_dt) dt
from qc where PRODUCTID=1011
group by PRODUCTID,u_price
order by PRODUCTID
result of this query is like
productid.,u_price.,dt
1011......6.00.....2010-01-27 00:00:00.000
1011......5.50.....2010-04-27 00:00:00.000
but i want to show the max date record like
productid.,u_price.,dt
1011......5.50.....2010-04-27 00:00:00.000
please tell me the query
hope you understand
i am waiting ur reply.
|
|
|
|
|
Try this
select top 1 PRODUCTID,u_price, max(qc_dt) dt
from qc where PRODUCTID=1011
group by PRODUCTID,u_price
order by qc_dt
|
|
|
|
|
thanks this query is working for me
|
|
|
|
|
 when i change the productid query show old record but i want to show latest u_price of product.
|
|
|
|
|
try
select top 1 productid, u_price, qc_dt
from (select productid, u_price, qc_dt
from qc
where productid = 1011
order by qc_dt desc) z
Marc Clifton wrote: That has nothing to do with VB. - Oh crap. I just defended VB!
|
|
|
|
|
I have a table that has a PK index on a VARCHAR(10) field. I insert 'A'. Next I try to insert 'A '. The insert isn't allowed because of the index. How can I make it so that 'A ' is allowed to be inserted?
Thank you in advance.
EDIT: The first is just A. The second was A[space].
modified on Monday, May 3, 2010 10:35 AM
|
|
|
|
|
yeah, primary keys are a nuissance; they have a habit of wanting to be unique. Remove the old 'A' before you insert a new 'A'.
|
|
|
|
|
The first is just A. The second was A[space].
|
|
|
|
|
If you genuinely need to add a second 'A' (rather than just trying to find out what happens if you do), then the chances are that the design of your database is less than optimal.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
yes, there is an issue with trailing spaces; here[^] is one discussion of that topic. I would avoid such situations by trimming all text before it goes into the database, otherwise you'll get trouble sooner or later, as you can hardly see the spaces at all and they may cause your selections to behave inconsistently.
|
|
|
|
|
Too slow Pattyn! 
I still think it's in the wrong place. Shouldn't it be a reply to the OPs' second post?
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
