MYSQL :: Select Fields Between 2 Dates

Select Dates After A Specified Interval
The code i have come up with thus far is as follows:

SET @StartDate='2005-11-22', @StopDate1='2007-11-22', @date1= '2006-01-31'; #Note: do not set StopTime greater than 23:59:59

SELECT
Table1.StopDate As Date

FROM dbase1.Table1
where StopDate= @date1
@date1 = DATE_ADD('2006-01-31', Interval 2 DAY)
WHERE ((dbase1.Table1.StopDate)>= @StartDate) AND ((dbase1.Table1.StopDate)<= @StopDate1)
GROUP BY Table1.StopDate;

date1 is the first date in the series.

Can someone point out where i am going wrong or if there is an easier way to do this?

Select Range Of Dates
How can I select a continuous range of dates? For example If i want all of the dates between Jan 01 2007 and Apr 02 2007 to be listed as:

jan-01-2007
Jan-02-2007
jan-03-2007
..
Apr-01-2007
Apr-02-2007

What Select statement could I use? The date format is not important.

Retrieving Dates In SELECT
Im trying to select some results from a query and use a WHERE clause to extract ones that were only created greater than July 1st 2007.

In the table, there is a 'created' column which stores a unix timestamp. The sql im using is

"SELECT post,created FROM table1 WHERE created >= 1183348800"

It seems that the WHERE clause is just comparing the acutal number and not realizing that it is a unix date.Is there a function that I am able to use?

Who Do I Write Select With Dates And Times
i have to cols, date and time. who can i do, SELECT between (date 1,time 1) to (date 2, time 2)?

for example i want to get the recordes between 2007-01-01 23:00:00 to 2007-01-03 03:00:00 ?

SELECT Date Records Between Two Given Dates.
Let's say i have a job posting site and a user wants to see the jobs posted between dates.... how would i do that?

here's how a table would look like(sample):

table: Jobs
fields:
JobID
JobStatus
DatePosted
DateToDeactivate

and when i build the query i would make it something like this:

"Select * from Jobs Where JobID = '123' and ( DatePosted BETWEEN 2003-05-08 12:14:37 AND 2004-06-08 12:14:37)"

i know that this does not work! how can i do this.

Select All Dates Since First Of Current Month
I'd like to write a query to select all records entered since the first of the current month (also, a query for all records entered during the previous month). A "date_in" field is being populated using the Now() function.

Select WHERE Between Two Fields
I am trying to see if something like this is possible... I want to find all orders that have not been paid for.

SELECT SUM(payments.paid) AS paid, orders.amount AS ordered FROM orders AS orders LEFT JOIN payments AS payments ON orders.ID=payments.orderID WHERE ordered>paid.

Select Fields
Just want to make sure I understand this. If I need all the fields in a record I could use SELECT * ... but if I do that I can't use any of the MySQL functions on the data, right?I mean, I have some DATETIME fields I needs to format, so I assume the only way to do this is to use SELECT col-1, col-2...DATE_FORMAT col-n FROM table WHERE id=whatever. (I understand there is more to DATE_FORMAT.)

Is there any other way to use the MySQL functions (I am using MySQL with PHP.)

Select All Fields
I have two tables, "literature" and "publishers". The "literature" table has a field for "publisher_name", the "publishers" table has a field for "name".

Now, I want to produce an output of all "publisher_name" fields in the "literature" table that don't have a matching entry in the table "publishers". In other words, to give our editors info of missing entries.

Is there a "clean" way to accomplish this? That is, anything else except producing a humungously long "OR OR OR OR OR OR OR" query? That wouldn't be pretty at all...

Select Fields
just curious how something like this would work. I've got a last modified field which uses unix timestamps. do I just figure out what the integer would be, subtract it from NOW, then select fields with integers above that? or is there some shorthand?

Select Fields
i have:table:
id, data

sample data:
1, bob
1, daniel
2, john
2, david
3, samuel
3, nathaniel

i was wondering if i could get output like:
bob, daniel
john, david
samuel, nathaniel

i'd like to be able to ORDER BY either of those fields

Select Distinct From 2 Fields?
I have a table with 2 similar fields containing state names (e.g. primary_state and secondary_state). How can I find distinct states from both fields?

Using CASE To Select Different Fields
I have a table with 5 fields:

a
b
c
d
e

Based on the value in A, I want a SELECT statement to return 2 fields

If a = 0, return b, c
If a = 1, return c, d
If a = 2, return d, e

It works with a UNION...

SELECT a, b, c FROM abcde WHERE a=0
UNION
SELECT a, c, d FROM abcde WHERE a=1
UNION
SELECT a, d, e FROM abcde WHERE a=2
;

I thought of using a CASE statement, but I can't figure out how to make it work.

This case statement works...

select a, case a when 0 then b
when 1 then c
when 2 then d
end from abcde;

This one doesn't...

select a, case a when 0 then b, c
when 1 then c, d
when 2 then d, e
end from abcde;

Any ideas what I should be doing?

Here's what I used to create the table -
CREATE TABLE abcde (a int, b char(2), c char(2), d char(2), e char(2));

INSERT INTO abcde (a, b, c, d, e) VALUES
(0,'b1','c1','d1','e1'), (0,'b2','c2','d2','e2'), (0,'b3','c3','d3','e3'),
(1,'b4','c4','d4','e4'), (1,'b5','c5','d5','e5'), (1,'b6','c6','d6','e6'),
(2,'b7','c7','d7','e7'), (2,'b8','c8','d8','e8'), (2,'b9','c9','d9','e9');

Select Range Between Two Fields
Is there an SQL way of selecting all the values between and including two integer fields that are in a single given record? For example, if one is 1957 and the other is 1965, I need all years between and including those two. I know I can do it using PHP or other programming but I am trying to come up with an SQL way, if one exists. Code:

Select Two Fields With Distinct
Here is my query:
CODESELECT DISTINCT(LOCATION) AS LOC FROM tblNetDesc
LEFT JOIN tblNetClass ON tblNetClass.NetClassID=tblNetDesc.NetClassID
LEFT JOIN tblLocation ON tblLocation.LocationID=tblNetClass.LocationID;

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 '
'

Select Only Specific Data In The Fields How?
Hi MySQL gurus

I have a table called cclp_players
now that table has 3 fields for instance and has data see below,
-------------------------------
| player_id |team_id|division_id |
-------------------------------
| 001 | hp | golem |
| 002 | ibm | cestino |
| 003 | sun | mac os |
| 004 | del | bim os |
| 005 | acer | ms os |

Now i want to SELECT the table either by team_id or by division_id.
And let say i wanted to SELECT team_id by sun.

What is the right expression or combination of SELECT command for this?

SELECT DISTINCT With Multiple Fields
I'm kind of stumped on a query I'm trying to build. basically I have a forum table like this:

Post_ID | Thread_ID | Post_EntryDate | etc.....
90 | 22 | ....
89 | 21 | ....
...
...
...
85 | 1 | ....
84 | 1 | ....
83 | 10 |....

and so on. I would like to get the last 20 threads with most recent posts filtering for Thread_ID duplicates of course. so far I have this:

SQLtemp = "SELECT DISTINCT Thread_ID FROM Posts ORDER BY Post_ID DESC LIMIT 0,20"

this succesfully returs a list of unique Thread_IDs of the last 20 threads with most recent posts. However, I need to get the Post_IDs and other fields but when I throw Post_ID in the SELECT statement all I get is duplicate values for Thread_ID

Select Only Specific Data In The Fields How?
I have a table called cclp_players
now that table has 3 fields for instance and has data see below,
-------------------------------
| player_id |team_id|division_id |
-------------------------------
| 001 | hp | golem |
| 002 | ibm | cestino |
| 003 | sun | mac os |
| 004 | del | bim os |
| 005 | acer | ms os |

Now i want to SELECT the table either by team_id or by division_id.
And let say i wanted to SELECT team_id by sun.

What is the right expression or combination of SELECT command for this?

How Does One Add Or Subtract Two Fields In A Select Statement
I would like to subtract tow fields in table:

for example Table1 has fields A, B, C

I wrote the statement as follows

Select A, B, (A-B) as diff from Table1.

MySQL is complaining that there is a syntax error near '-'

Difference In SELECT WHERE IN With Datetime Fields
Anyone can explain the following?

Table definition:

CREATE TABLE testdate
(
SalesDate DATETIME,
SalesTotal DOUBLE,
PRIMARY KEY (SalesDate)
)

If I use the following SQL statement I get results:

SELECT * FROM testdate
WHERE SalesDate = '2003-08-27' OR SalesDate='2003-07-27';

But I get an empty set when I use this:

SELECT * FROM testdate
WHERE SalesDate IN ('2003-08-27', '2003-07-27');

This happens on both 4.0.15 and 3.23.57.
But the above statements work the same under Microsoft SQL Server.

Why is MySQL interpreting WHERE IN and WHERE = OR = differently?

How Can I Join Two Fields In A SELECT Statement
How can I join two fileds in a SELECT statement, such as...

SELECT FirstName & ' ' & LastName AS Name FROM....

I know I was able to do that in Access, but how does it work in mySQL?

Select Only Rows Of 2 Fields That Does Not Repeat
I would like select only rows where registerNo AND entryid that does not repeat.
Example, from the table only one row of 'registerNo=1 and entryid=sqbiiphiu1' and the last 5 rows will be selected.

I'm using php to build this. i'm not sure if distinct can help. this is wat i thought of
$query = "SELECT DISTINCT * FROM fn_vote WHERE registerNo = '".$registerNo."' AND entryid='".$entryid."'";

How To Select All The Fields In Aparticular Row That IS NULL
I want to select fields which has null values in a particular row.

if there is one field i can give as

Select a,b,c from tablename where a IS NULL;

But my problem is

I want to get results from a,b and c not only from a.

Difference In SELECT WHERE IN With Datetime Fields
Table definition:

CREATE TABLE testdate
(
SalesDate DATETIME,
SalesTotal DOUBLE,
PRIMARY KEY (SalesDate)
)

If I use the following SQL statement I get results:

SELECT * FROM testdate
WHERE SalesDate = '2003-08-27' OR SalesDate='2003-07-27';

But I get an empty set when I use this:

SELECT * FROM testdate
WHERE SalesDate IN ('2003-08-27', '2003-07-27');

This happens on both 4.0.15 and 3.23.57.
But the above statements work the same under Microsoft SQL Server.

Why is MySQL interpreting WHERE IN and WHERE = OR = differently?

Append 2 Or More Fields In A Select Statement
How can I append 2 or more fields in a select statement.

ex:
CODESELECT *, `RESTPHONE1`+`RESTPHONE2`+`RESTPHONE3` AS NUMBER FROM RESTAURANTS

SELECT DISTINCT Then Additional Fields
I am trying to do the equivalent of :

Code:

SELECT DISTINCT email, * FROM student_bookings

I know this will never work but think it explains best what I am looking for - I want to select additional fields from each DISTINCT email.

I know I could use a subquery but are there any other ways of doing this? (unfortunatly I can't use subqueries as we don't have the correct version of mySQL for that ).

Select Fields Accros Multiple Dbs
I am trying to use the SELECT DISTINCT statement to create one list of email address I have which are in two different databases, I have tried the below syntax, but does not seem to work...can anyone please tell me where I am going wrong!

SELECT DISTINCT m2e_db.email, events_bookings.email FROM m2e_db, events_bookings ----Ment2Excel (M2E)----
--LEARN BY EXAMPLE--

How To Select Fields That Have Reserverd Names In MySQL
I have a stored procedure that does this select:

SELECT Blob, BytesCount, FileName
FROM ProblemReportAttachments
WHERE AttachmentID = a_AttachmentID;

But the column called 'Blob' causes a MySQL syntax error because Blob is a reserved word in the MySQL syntax (for the BLOB datatype). How to get around this? Please, no replies saying to rename the database table column to something else since this is not an option.

Select All Fields Of Specific Row In Multirow Result
I'm making a query that returns multiple rows, each with multiple fields. I need to be able to select individual rows (including all of their fields) without using a while loop. so, my query:

$fullquery = "SELECT claimid, title, claim, dateSubmitted, totalVotes FROM claims ORDER BY overallrating DESC LIMIT 6";
$result = mysql_query($fullquery, $dbc);

I need to select row 1, use its data in a specific way, then select row 2, etc. I know how to do this in a while loop, and how to remove particular fields, but not how to access all fields for just 1 row.

Query To Select All Fields Using AUTO_INCREMENT, And Then Delete All Data With A Value Of 0?
Got a database with lots of tables using AUTO_INCREMENT quite a bit. Around a week ago a restore was done on this database from a backup made with compatibility mode turned on which removed the AUTO_INCREMENT value from the SQL. Problem I have now is that for all data being submitted to tables with fields using the AUTO_INCREMENT function are being submitted with a value of 0.

I have now fixed my database structure so all the fields that need AUTO_INCREMENT are set to use it, but now I have the problem that most of these fields have data with a value of 0 which is invalid. What I need help with is to write a query that finds all the fields in all tables in the database using AUTO_INCREMENT, and then delete any values of 0. Does anyone know how I can do this, and if so could you show me?

Select Fields On Selection Box(html) By Database Values
I have a form that I have put together to update, and add new fields. I have no problem with connecting to the database, updating fields, and adding new fields. My form autofills with current database information if the user wants to update certain fields.

What is the easiest way to make these fields selected, if the database has the entries already? Thereâ€™s about 30 fields, so I would like to do it dynamically. The fields in the database(in one single variable) are comma delimited...

"value1,value2,value3"

Dates In The Past 3 Weeks (was "SELECT Query Help")
I'm trying to select data from a table that has been submitted in the past 3 weeks. I am not having any success.

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

INSERT Into Table2 SELECT Fields From Table2 Giving Inconsistent Data
I have two tables ia_raw_mo and ia_intermediate_mo.The ia_raw_mo table has extensive data , so I use the select group by on this table and insert the data into the ia_intermediate_mo table using "insert into select table" clause

The query is :

insert into ia_intermediate_mo SELECT days, hours, bannerid, zoneid, ifnull(channel,'') as channel,ifnull(os,'') as os, ifnull(browser,'') as browser, ifnull(countryid,'') as countryid, ifnull(cityid,'') as cityid , ifnull(bandwidth,'') as bandwidth,count(*) as mouseovers

FROM ia_raw_mo where days='2007-12-14' and hours >= 8 and hours < 9 group by bannerid,zoneid,channel,os,browser,countryid,cityid,bandwidth,days,hours;

the ia_raw_intermediate has 9825 records and after correct execution of select and group by on this table, I should get 45 records in ia_intermediate_mo table.

But the above command is giving me 9825 records sometimes(around once in 100 times).So in that one particular case,there is no error message thrown but the group by is not properly executed and the query returns exact numbers of records , as if no group by was there

What I am thinking is,that there may be some problem in query plan which mysql creates, because the same query is also returning corrent result most of the time.But I am not sure about this or is there any possible flaw in the above query.

Even the mysql query log is also showing the same query been executed and returning 9825 records.

Select Where Four Of The Ten Fields Are Same (was "query")
I have a table with ten fields. Now I want to select * from table where four of the ten fields are same. How can I do the query?

SELECT DISTINCT, (and Display Other Fields Not Distinct.)
I am using SELECT DISTINCT to select 1 of a duplicted field. So far I have;

SELECT DISTINCT `field1`, `field2`, `field3` FROM table1

This returns what I need. There is also another field (field4) which I also want to select, but not distinctly.

Something like: SELECT DISTINCT `field1`, `field2`, `field3` NOT_DISTINCT `field4` FROM `table1`

The field that is not being selected distinctly contains a '1' or a '0'. My table is ordered by field4 (0 first) does this mean the select distinct will select those with '0' before those with '1' (I want '0' to have priority when select distinct)

I will only be using this SQL query once to remove duplicates from a database, I am not concerned about performance issues which someone has mentioned to me.

How can I display this not-wanted-distinct field in a distinct query?

Fields In One Table Overwrite Fields In Another Via JOIN
I'm writing a simple web game which uses mysql to store data. The prototype works ok so far, but now I'm refactoring it to support multiple players. This means coming up with a way to store the player's current world state.

I'm thinking of doing this by having a table that defines the initial world state, and then a table containing the 'state' of anything different.

Example:
The 'objects' table looks like this:
object_id, name, room
1, Hammer, 1
2, Mirror, 1
3, Spade, 2

So, at the start, room 1 contains a hammer and a mirror, and room 2 contains a spade.
Now, if player 12 picks up the hammer then drops it in room 2, I store this override in the 'state' table

player_id, object_id, room
12, 1, 2

So. I'm trying to see if there's any kind of join syntax that would return all the objects in a room for a given player's 'session'. Obviously it could be done with temporary tables, or just comparison outside of SQL, but I keep thinking this might be possible *somehow*

One Table With Many Fields Or Many Tables With Few Fields?
I need to build a database, but I'm torn between these 2 choices:

Is it better to have one table which has many fields
or
many tables which each has few fields?

Is it true that the latter is worse because it will require many join operations?
What is the limitation of the first option (one table with many fields)?

Delimited Fields Or Unused Fields
I have a table which has, for instance, 5 fields and maybe people only need the first one to be filled in often.  So 4 might well be empty.  Is it better to join them into 1 delimited field or leave them as their own fields?
And - if I have some fields that are only relevant to some types of rows, is it better to have 1 field for them delimited, or have them as separate fields?  (the same issue there really)

The other side of it is - is it better to split such delimited fields in the mysql call, or split them afterwards in php?
I might know which is easier though I can do it either way now I've researched, but I don't know which is better.  I also don't know how big this db will get or how often these fields won't be used so it comes down more to the overall design issue of which is better.  I'm thinking efficiency, speed, db load, and so on.

Dates
I have a 'date' field, i.e. YYYY-MM-DD. I have a dynamically generated array, which I convert to a string of the form 1, 2, 3, 5, 7, 12 where the numbers represent months.

If necessary, I can make the string have leading zeros for one-digit months (so 1 -> 01, 2 -> 02) etc using PHP.

What I want to do is select all rows from a table where MM from the date field is in the array.

Is the following correct? (I can't try this right now as I'm not able to access the code/DB from here)

SELECT *, DATE_FORMAT(dateField, '%m') AS dt FROM aTable WHERE dt IN(01, 02, 03, 04, 07, 12)

Dates In SQL??
I'm having some trouble with dates in MySQL.

I'm developing a site that has an area for news. I've created a table that's called NEWS and it has 3 columns: id, date, news_text.
For the date I've given these properties: datatype:timestamp, default:current_timestamp.

The problem arises when I retrieve the news. I want the date to be printed as well in the form of YYYY-MM-DD, but I guess due to the current_timestamp, I get YYYY-MM-DD HH:MM:SS.

1) Is there a way to ommit the time??
2) How can I change the format and make it DD-MM-YYYY??

BETWEEN X AND X For Dates
I'm trying to find all records that fall between two dates. BETWEEN only seems to be for integers so I was wondering there was a similar construct for dates.

When Should I Use Int For Dates?
I'm thinking about storing dates in my databases as unix timestamps in int fields. Since I use PHP I'm thinking it would be convenient to have timestamps rather than datetime fields.

But someone told me I can't store dates earlier than 1970 as timestamps. So do I have to decide from application to application if I expect to be using dates earlier than 1970, or is there a way around this rule?