|
if your date and datetime information were stored as such (and not as a string), then you could simply use SQL stuff such as "ORDER BY" and "TOP N" since databases that support date types also know how to sort them chronologically.
If you insist on storing datetime information as strings, you will:
- be subject to problems due to different regional settings (which day/month/year is 02/03/04 in each country on Earth?)
- need conversion functions everywhere in your SQL statements.
You might want to read this little article[^].
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that. [The QA section does it automatically now, I hope we soon get it on regular forums as well]
|
|
|
|
|
As Luc has said you need to change your data type to datetime, leaving it as varchar is a disaster. Think about this, to get the top 7 records you could do the following
SELECT TOP 2<br />
Dt<br />
FROM (SELECT DISTINCT CONVERT(DATETIME,PeriodDate) Dt<br />
FROM Period ) X<br />
ORDER BY Dt desc
That will have to convert EVERY datestring in your table, creating an index is a waste of time and all the regional problem Luc mentioned will be visited upon you.
FIX YOUR DATA NOW!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: to get the top 7 records you could do the following
SELECT TOP 2
Dt
FROM (SELECT DISTINCT CONVERT(DATETIME,PeriodDate) Dt
FROM Period ) X
ORDER BY Dt desc
Call me picky, but won't this give the top 2 records? Or am I missing something here?
|
|
|
|
|
I only had 3 records in the test table I wrote this against  . If the OP can't work out the difference then I probably can't help him.
And yep I call you picky 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: And yep I call you picky
Every forum needs at least one anal-retentive pedant to keep things in line. I do my best.
|
|
|
|
|
David I doubt you are the only one - AR is a required trait for some aspects of development so you are welcome.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
What do you mean by "select 7 days record from the table"? Do you mean that you want all the records from the table for the last 7 days? Or do you mean that you want the top 7 records from the table irrespective of date? Or do you mean that you want all the records for the most recent 7 days which are actually in the database (which chronologically may range over more than 7 days if for example you have no entries at the weekend)?
Each one of these will require a different query. None of which are particularly straightforward given the way in which the dates are stored in the table.
|
|
|
|
|
Hi
I am planning to implement a custom version control system.
Is there anyway to represent physical directory structure in database?
Or should I represent them using 'tree' data structure?
If using tree data structure, how can I save the contents of tree to an external file??
thanks
fadi
|
|
|
|
|
which version of SQL are you using?
If SQL Server 2008, you may want to look at the FileStream Datatype[^]
I guess you can create a self referencing table [ heirarchical ] and either use the datatype above or file path and have some code to export it out. I dont know exactly what you want to do but it seems dangerous
|
|
|
|
|
Along with the filestream storage you can use the hierarchicyid you define your tree structure but you are going to have a coordination problem between the physical structure and the hierarchy in the table.
The filestream will not allow you to move the files via explorer so that would be one benefit.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hallo,
Did anyone solve the problem with a Boolean Column in a datatable for .net / c#?
I am new in Oracle development and want to convert my database to oracle. But there is no Boolean datatype with SQL and I want to display a gatagridview column as a checkbox. With Postgres or MSSQL there were no problems with that. But with Oracle I search a elegant solution.
Any ideas?
Thank you.
camau
|
|
|
|
|
Boolean datatype is not defined by ANSI.
So either you'll have to use a number or a char, for example: flag char(1) check (flag in ( 'Y', 'N' ))
|
|
|
|
|
I have installed Oracle 9i I want to install now ( Oracle forms 6i ) ...On inserting the installation CD...the "Oracle installer" screen appears ..then "Oracle installation settings" form appears ..Here we have to specify
Company Name:xyz
Oracle home details
Name: OraHome92
Location: C:\oracle\ora92
Language: English
With theses entries when I click OK button, the following error message with the Title "General Information" appears:
The location specified "C:\oracle\ora92", is already used as an Oracle Home for 'Oracle 82 Beta'. It can not be used as an Oracle home for 'nt'.
The OS am using is Windows XP Professional with
Pentium 4 ...Please help me out...Thanking you in advance.
|
|
|
|
|
Try to rename c:\oracle\ora92
|
|
|
|
|
You probably need to check on some registry entries. Search for HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE. There may be an entry about an Oracle Home that is no longer valid and can be deleted.
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]
|
|
|
|
|
If you search the Oracle forums you will find several threads discussing this issue. I haven't read them in detail but from what I can gather, the installer doesn't like installing Forms 6 and Oracle 9.2 to the same home location. There are a number of suggestions to work around it in this thread:
http://forums.oracle.com/forums/thread.jspa?threadID=340452[^]
|
|
|
|
|
Here's my quandry: I am creating a new table that's holding records of message sent within the system. One of the fields is what type of message is being sent which currently falls into only 3 options.
Currently, it's likely that this is not going to change that much but I'm trying to work out if it's better to create a new table to hold these and thus enforce referential integrity or simply not bother and rely on the enumeration on the code end.
Queries against the data is likely to want grouping by message type but with only 3, it just seems a bit overkill.
Any suggestions?
|
|
|
|
|
From a purist standpoint, it would be better to create a parent table like:
MessageType
ID int,
Description Varchar
and create a constraint against your Message Table to ensure that only valid message types are created in your table. You won't pay much (if any) performance penalty because your parent table is so small.
That's my vote. 
|
|
|
|
|
I want to update a column of one table A with column of another table B.
for one entry of A, B contains 2 or more entry. Say A is super table of B.
I wrote a query like this
UPDATE SendSet SET oDate = (Select oOrder.oDate from oOrder where oOrder.oID=SendSet.oID)
WHERE Exists (Select oOrder.oDate from oOrder where oOrder.oID=SendSet.oID);
DB is MS-Access.
But it gives error :
Operation must use an updatable query !!
You attempted to run a query that tried to update a field that cannot be updated. For example, you may have created the query in such a way that you tried to update a field on the one side of a one-to-many relationship.
any help ?
|
|
|
|
|
You can use a join in your update query. Please check out how to do a join.
|
|
|
|
|
Without knowing the relationship between table A and table B, I can only suggest that you use something like this:
update SendSet inner join oOrder on SendSet.oID=oOrder.oID set SendSet.oDate=oOrder.oDate;
You need to work out what exactly you need to do this update.
Happy programming!
|
|
|
|
|
Thanks.
it works.
regards
|
|
|
|
|
I have one table "Farmer" in which i store farmer name,village id,gut id
and when display this information in grid,i want display farmer name,village name (this name is retrieve from village table in which[village ID,Village name],but some farmer have no village id ,but i want show information in grid his name and after vilage name is blank,which query i use?
|
|
|
|
|
What have you tried so far?
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
i think for this try left outer join so that it get all the farmer even the village is not present for that
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
