MYSQL :: SQL For Finding Years In Dates?

Storing Dates In The Billions Of Years
Since the datetime format only allows dates between 1000-9999 I won't be able to use this directly. I am considering using perhaps an integer or some sort of a date hack.

Right now I am considering all options. One I came up with is storing an alternate number to be stored alongside which would designate the date's power. So '0' would mean the date is between 1000-9999, '1' would mean the date is between 8000BC-999... etc. I am wondering if this would be too much of a problem coming up with queries, however, so right now I am leaning towards a regular year integer and perhaps a secondary integer for the MMDD.

Finding Between Dates
I have a date in the following format : 08/06/2004 00:47:47 stored in a char field. What sql would I do to look between 2 dates. --- say I wanted to select all entries between 08/01/2004 and 08/10/2004.

Will between work if it is not a date field in the sql?

If not -- is there anyway to copy the time field to another time datetime field?

Finding Dates
I want to pull up all entries in the database with a date that is either today or before today:
Code:
SELECT *
FROM `invoices`
WHERE `date` <= '2005-08-01'

This doesn't work ... what am I doing wrong?

Finding Records Between Dates Problem
Here is my query.

SELECT * FROM callhistory
WHERE date_format(callhistory.TimeStamp, '%m/%d/%Y') between '09/15/2004' and '01/08/2006'

This returns nothing.

If I change it to

SELECT * FROM callhistory
WHERE date_format(callhistory.TimeStamp, '%m/%d/%Y') between '09/15/2004' and '12/08/2006'

It works fine.

Getting The Age In Years
i want to get the age of a person in years with sql query in mysql. i have stored the date of birth in the database. I have tried Datediff function of mysql but it returns in Days not in Years.

I would like to use some thing like this one.

select * from tablename Where "datefunction that get me date in Years like this one Datediff(CURDATE(),date_of_birth) " between agefrom AND ageto.

Do Years Begin With 1901???
I just created a new table that stores the names of Washington State governors, with separate fields for the years they began and ended office. When I filled it from my CSV file, I discovered that all the years prior to 1901 defaulted to 0000.

Is there a way to fix this, or should I just use INTEGERS instead of YEAR?

Adding Multiple Years
I have set up tables that increase input dates by either a month, quarterly, half yearly or annual using M,Q,H or Y in the default space, and ('M','Q','H','A') in the length/values space.However, I want to be able to add 5 years to one of the fields in the table.

Grouping Weeks, Months Or Years
I have a database full of log information that has a record for every hour of every day.

So I have 24 records all with the same 'dateCreated' field and then the 'timeCreated' field changes for each hour of that day.

So grouping the query results of the database into days is very simple, just a quick 'GROUP BY dateCreated' does the trick, however i now have the requirement to group that data into weeks, months and years.

What is the best way of achieving this? is it even possible?

Returning Decades From A List Of Years
I have a column containing a year and I want to return a list of the decades in that column. For example:

Year
--------
1987
1992
1914
1922
1923
1928
1984

would return:

Year
-------
1910
1920
1980
1990

Mysql Nt Service Stopped Starting After 2 Years
i have been running mysql on windows server 2003 for the last almost 2 years (its a really early version of mysql 4 anyway) but now all of a sudden mysql service stopped starting, i can go to services, press start and then refresh and its off again! i have a feeling that someone deleted the core mysql table (mysql) as it is now totally empty.

im not too good with my sql as i generally use mssql or whatever, infact mysql never really installed properly, the only user i could ever use was root but i didnt mind this so i just left it and it was fine for 2 years but now its just stopped all of a sudden and i cant get it back on.

i have enabled the mssql php exension as a backup system but i cant transfer any databases across without enabling mysql.. but it wont turn on! i tried several exes in e:mysqlin but none got it on (well one of them made the little winmysqladmin light go on for half a second)

Converting MS Access Dates To MySQL Dates During LOAD DATA INPUT ?
My insert code is below. The MySQL server is on my local machine and is version 4.1.22. I am exporting from an access table with 6 fields to a mysql table with those 6 plus 5 more fields. The main problem I am having SO FAR, is converting dates. The data file dates are formated like:
2/2/2006 0:00:00, 12/20/2006 9:22:05

Any ideas how to format those into mysql friendly dates?

Also, how do I convert currency fields in the load data process? I know you use SET by what kind of formula?

===========================================

LOAD DATA INFILE 'C:Documents and Settingspath_to_filedata.txt'
INTO TABLE auto
(field2, field3, field4, field5, currency_field6, field7, date_field8, date_field9)
SET id = MD5(UUID())
FIELDS TERMINATED BY ','
LINES TERMINATED BY '
'

How To List All Missing Dates Between 2 Dates
I have a database which stored financial data daily exclude weekend, however sometimes i tend to forget to upload data into the database. How do i write a sql query that would detect the missing dates between from last updated date until the current date.

Stroring Dates Perior To 1 AD, BC Dates
I need to store some dates that can be BC, However I received a problem indicating Incorrect Datetime.

I have read the documentation, and it says that Date&DateTime types can store from 1000-9999 AD, and Timestamp stores less year ranges. What other options do I have to store dates before 1000 AD !

List All Dates Between 2 Diff Dates
I have a database which stored financial data daily exclude weekend, however sometimes i tend to forget to upload data into the database. How do i write a sql query that would detect the missing dates between from last updated date until the current date.

Dates, Dates, Dates - Syntax Help Please!
OK, I have a MySQL table which contains, among others, a field called date which stores dates in the format YYYY-MM-DD HH:MM:SS.

What I'd like to do is select a list of all the months present (without repetition), preferably in the format YYYY-mm

Models Where The Years Overlap (was "Query Help Needed")
I'm trying to formulate a query for the following example:

Table: model_by_year
model start_year end_year
model_1 2001 2004
model_1 2003 2004
model_2 2000 2005
model_2 1998 1999
model_3 1998 2003
model_3 2002 2006

I want a query that will return all like models where the years overlap. In other words, I'd want to return model_1 and model_3, but not model_2.

Naming Years As "seasons"
In the football records database many of you have been so kind to push me along on, I have a dropdown menu that pulls each year into the list.

The problem now is adapting it for sports that play one season in two calendar years (like this year is the 2006-2007 basketball season). Is there a simple way to do this? The easiest I've come up with so far is making another table of "seasons" and use a "season_id" in the 'games' table. I've tried looking into "fiscal year" solutions, since that also spans two calendar years, but the naming conventions are different (i.e., "Fiscal Year 2006")

Finding Which Row Has Max
I am trying to find which row has the result produced by MAX(). I've tried quite a few different approaches, but I'm not getting what I want. Here is a simplified example:

Table: prod_ver
id   category  version
1    5         3
22   52        3
88   5         4
116  52        5
117  3         2
118  41        1
120  52        2

SELECT p.id, p.category, MAX(version)
FROM prod_ver AS p
WHERE p.cat IN (5,52)
GROUP BY p.category;

Finding &
I've obviously got some problems in my php forms. Going through some of the entries, I find anywhere from 1 to 4 backslashes infront of some single quotes, ie: \' or '. Other entries with single quotes are fine. I've just published my member profiles on the site and, it really looks bad seeing these backslashes out in the open.I'm working on redoing the functions that format the data but, my bigger challenge is going through 5,000 records and finding/fixing all the records with backslashes.

Is there an easy way to do a find and replace on all records in the table? I've been using phpMyadmin to search individual fields but, I have so many fields and records that need ammending, it's taking forever.

Finding The Parent Id
I am new to these boards and to SQL. My question is I am trying to find the parent id of a child and have its output

What I have so far:

Database:
id#name#subcatof
0#Home#0
1#About Me#0
4#Sub Category#1

PHP
<p><? echo l('subcategory'); ?>:
<?
$query = "SELECT * FROM " .s('prefix'). "categories WHERE subcatof = id";
$result = $db->query($query);
while ($r = $result->fetch_array()) {
echo $r['name'];
} ?>

It outputs:
Sub Category of: Home
It is not selecting About Me, how can I make it select ABout Me?

Finding Only Childs.....
---------------------------
1)category

category_id
category_name
---------------------------

2) category_parent

category_id
parent_id
---------------------------

Using this structure one can go to infine depth of creating sub-categories.

I've used this structure in my project,
now i want to find categories which are the lowest are not parent of any other

Finding MAX Among DISTINCT
I'm pretty new to mysql, and I'm looking for some help.

I have a database for photos -- the columns are id, id_num, date, and photo. The id_num ties certain records to specific clubs in a different database.

The problem I'm having is I want to select DISTINCT(id_num) and then from within those results the MAX(date) available then ORDER BY date DESC.

It seems nomader how I tweak my query I either will get mulitple results of the same id_num or cannont get them to list by date DESC.

Finding The Min Of The Maxes
I am not as proficient with SQL as I used to be, so I am having trouble writing a query. Here is the table structure:

ID (auto-incrementing)
PrimaryProductKey
RelativeProductKey
LastIndexed (datetime)

A sample dataset would be:

1, 2, 1, 2008-02-06 10:27:18
2, 2, 2, 2008-02-05 10:27:18
3, 2, 1, 2008-02-04 10:27:18
4, 5, 1, 2008-02-06 9:00:00

I would like to find a) the MAX datetime stamp per PrimaryProductKey key, and then b) find the MIN of that result. So for this dataset, that would return a) 2008-02-06 10:27:18, 2008-02-06 9:00:00 and then b) 2008-02-06 9:00:00 . I don't really need the results from a, I just need the final datetime stamp returned from b.

Finding What Your Root Is.
How do I find out what my root is? I don't mean my root password, I just mean my root in general. Like the root name.

Finding The Ratio
Suppose i have two tables called tableA and tableB. TableA have 3 fields called area, employers, year and tableB also have 3 fields state, area and area_name.

tableA looks like this

area employers year
233 250 2005
654 500 2005
221 2060 2005
754 786 2005

tableB looks like this

area state area_name
233 FL anything
654 NY anything
221 FL anything
754 CA anything

How can i find the ratio of the people employed in florida versus new york?.

I have tried many things but nothing works, including this query that works but give me wrong a wrong answer.

CODESELECT sum(L.employers) / sum(L1.employers) FROM tableA L, tableB LL, tableA L1, tableB LLL WHERE L.area = LL.area AND LL.state = 'FL' and L1.area = LLL.area AND LLL.state = 'NY;

Finding Duplicate
select o.operanum from opera_num o
left outer join (
  select p.operanum, count(p.operanum) as c
  from opera_num p
  where to_days(expiry)>to_days(now())
  GROUP by p.operanum
  having count(p.operanum)>1
)as secondUse
on o.operanum = secondUse.operanum
where to_days(o.expiry)>to_days(now());

Finding The Overhead
How can I find out the overhead of my mysql database's tables like phpmyadmin does?

Finding My SQL Table
I have database driven site that is written in Perl (not by me!) on a unix server and all the data is uploaded or edited via an on-line form. I previously used to hold this data on a local access database and this information was uploaded to the server at intervals by the administratior, not very efficient.

Since I have been given this form to upload and update the information directly on-line I do not have a copy of the data locally which is a problem because I need it to produce various reports etc. What I would like to know is where is the data stored and how can I download a copy of it in a format that I could use in a database type programme.

Finding Duplicates
I have a table with 3 columns 'sid', 'aid', and 'call'.

What i want to do is to create a query that shows me all records where
sid matches and aid matches but call differs.

For example, it would give me this result:

sid aid call
abc def gh
abc def ij

and not:

sid aid call
abc def gh
abc def gh

Currently i have a query that gives me all matches of sid's and aid's
but gives me call's that match and don't match.

Finding Duplicates
I have a database that lists words with their part of speech. The textfile that I uploaded it from was a combined list, so naturally it had duplicates (ex: 'moo' is featured twice as a noun).

My question is, which queries would select only those rows, and then subsequently delete one of them? I'm new to MySQL and I can't seem to figure out one that works.

Finding Orphans
If I have table_a.id and table_b that has table_a_id, how can I find all entries from table_b that have a non-existant table_a_id?

Finding Database
I have experience using SQL script though from using MS Acess. Anyways, I have created a database(or schema), containing 1 table, using MySQL Administrator. Now I want to find that database and move it to my server to setup a forum. Where do I find this database?

Finding The Primary Key
Is there a way to find the primary key(column name) in a table via a SQL statement? Show Primary From Table.

Finding The Maximum Value
I tried to find the maximum value by using count and group by statement
but the result is negative. how should i go about.

Let say the data are

empno | name
1 John
2 John
3 Jess

select count(name) from employment
group by empno

The above SQL statement will give

count(name)
2
1

But I want the value 2 only.

Finding NULLS
I am querying with the following hoping NULL and not NULL:

SELECT *
FROM pet
WHERE sex='NULL';

returns empty set, but there should be one record.

SELECT *
FROM pet
WHERE death<>'NULL';

also returns empty set when there should be one record.

Finding Mysql.exe
I have installed the EasyPHP in WindowXP with working perfectly. But one thing I just wonder which directory that I can open the mysql.exe file to be able to type my mysql command line in this case. Since I cann't find the mysql.exe anywhere after installed the EasyPHP in WindowXP, does anyone know about it? Thanks a bunch!

Finding Users
ive got 2 tables, one of users and one of orders created by those users. The orders keep track of the user_id. I am looking to get a list of users who have not placed an order, or in other words, users who do not have their user_id in the order table.

Finding The Record ID
I am using MS Access as a front end to a MySQL server on a windows platform.
The problem is that when I add a new record to a list (continuous form) of
records as soon as the cursor moves to another record the just entered data
appears as #DELETED until I requery the form. The problem is that the record
does not appear to get an ID until it is requeried so I cannot find the ID
and then return to the record after it has been requeried. I need to do this
because after creating a new record I want to open another form and insert
the new ID into it.
I have a timestamp field on the form.

Finding Primary Key
I have a mysql db with with autoincrementing primary keys in each table. I
want to allow a user to add a record using a php webpage. Once the page is
added how can I re SELECT that record (for the user to verify) if I don't
know what the primary key was when it was inserted?
Is the only way to not use autoincrement and choose the primary key value
myself?

Finding File
how I would go about checking a database field (that isn't the primary field) to see if it matches another string

Finding Rankings With MySQL
I'm working with php. I have a auction site, more or less. I want to create all-time rankings. The idea is to display where a seller ranks (all time) in the number of sales.

So for example, I'd display
John Doe All Time Sales Ranking: #138

I'm not exactly sure how to go about this.

$query = "SELECT count(*) as counter, SellerName FROM sales GROUP BY Sellername ORDER BY counter DESC";

This query would give me the data to list all of the Sellers in desc order by the number of sales.

In php, I could probably count until the Sellername was equal to $Sellername (already defined in their profile page), but I was hoping there would be a way to do this entirely in MySQL.

Finding Around An Specific Number?
I have a page in php and mySql and in my query I look for a number that's been input from a dropdown in a form.

Let's say I pick number 5.

So I put WHERE number LIKE $number to get what I picked.
But now I wanna get the number picked and also the 3 lower numbers as well.
So if I pick 5 I would get 2, 3, 4 and 5.
If I pick 20 I would get 17, 18, 19 and 20.

Is there a simple way to do this in the query?
Is there something I could use for this, just as I can use = or < and >.

Finding Unique Entries
I was just wondering if there was a mysql query or php code that could help me do what I want to do.
I will use the example of a lyrics website for the sake of argument. Let's say I have a huge database of all the lyrics from all the artists on my site, and I want to be able to print a list of all the artists, only onces. Could anybody point me in the direction of the solution?

Finding Out Table Updates Since...
I want to synchronize 2 sets of tables and need to be able to tell wich updates the offline set has had since the last time it sychronized the online table. Then i want to run these queries against the online table so their both in sync again.

Is there a MySQL way to do this or will i have to store the SQL queries ran agains the offline table manualy?

Finding The Last Column In Table
Is there any way I can find the last element of a table in a certain column besides using a while loop to go through till I reach the last element.

If its the while loop, i presume its this:

Query: Finding Lastest 6, But Then The 6 Before That?
It is easy to find the latest 6 entries I think, something with limit I presume and order DESC?

But how do I find the 6 before the 6 latest?
and then back until to first?

Sorry, but I am still very much a beginner at this.

(I have an auto-increment integer(=ID) and a timestamp field)

Finding A Way To Omit Certain Letters.
I have a table with a field called CELL under this field I have rows with the following.

1X3V
1Y3V
1Z3V
.
.
.
.
.
up to
.
.
.
30X3V
30Y3V
30Z3v

I would like to find a function to omit the (X,Y,Z)3V and only output 1 - 30.

Can this be done?