MYSQL :: Converting MS Access Dates To MySQL Dates During LOAD DATA INPUT ?

Porting & Converting Dates From One DB To Another Problem
I am a bit of a newbee into the mySQL world and I have a question regarding
switching from one DB to another. I have for a couple of years used a very
simple CMS-system created by myself but now I want to move to textpattern
(http://www.textpattern.com/). Using a mySQL tool called NaviCat, I can
easily create queries regarding porting ID, text data etc. from my old DB
into the textpattern DB (using INNER JOINS it seems). But I have one
problem:

The dates I have in my DB is in the format given by PHP by using the
PHP-command time() (Unix-format?), which I have stored as varchar(10)
(Example: 1047756304). But the format Textpattern uses is the DATETIME
format. My question is: How do I port these dates from my DB into the
Textpattern DB?

Additionally:
- Is it possible in the DATETIME format to store time zone information,
since all my dates are in GMT+1?
- And can it all be done in one swoop in either PHP or as a MySQL query?

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

LOAD DATA Gives Problem When Last Column In Input Is Empty!!
I had created a table with 20 fields, setting most of them to DEFAULT to NULL.
(partially shown bellow).

I populated the table with LOAD DATA. Last column in most of my input rows are empty, yet separated by a tab from the previous column. So most of the rows typically ends with a tab (and a newline, ofcourse).

Load data works fine with out any warning. But, when query the database, i do NOT see NULL value in last column(lab here). This column just looks empty. Where as I expected NULL to be there.

Is there something i miss here.
NOTE: If a column does not have the data in the middle of the row (say for 4th field), then I get the NULL value set to that filed. Which is perfect. Code:

How To Get Data Between The Two Dates?
i'm using the between operator to list all the transaction between the dates and it's working fine.

But i want to list all the transaction from a table including the startdate and the enddate.

Selecting Data Between Two Dates
I'm trying to return data for each day in a date range. If the day does not have a total I would like to use the last previous rows total for a new row. I have no idea how to do this. This sql returns.

select
ag.agcy_name, date(a.acct_signup_date), count(m.mem_member_id)
from transactions t
left join txn_type txt on t.txn_type_id = txt.txn_type_id
left join travel_type trt on trt.travel_type_id = t.travel_type_id
left join segment s on t.segment_id = s.segment_id
left join member m on m.mem_member_id = t.member_id
left join account a on a.acct_account_id = m.mem_account_id
left join agency ag on ag.agcy_agency_id = a.acct_agency_id
left join group_member_relation g on g.group_mem_rel_member_id = t.member_id
where a.acct_status_id ='1'
and a.acct_signup_date between ? and ?
group by ag.agcy_name, date(a.acct_signup_date) .

LOAD DATA INFILE To Import Data Exported From A MS Access Xp
I am trying to use LOAD DATA INFILE to import data exported from a MS Access Xp table. But all i get in the date field is 00:00:00, as Access exports DATE and TIME and MySQL seems to use just the TIME portion of this.

Is there any way to ahve MySQL import just the DATE portion or is there any way to have Access export only the DATE portion?

Using Dates In PHP + MySQL
I have never used dates before but am currently trying to specify a date and have it insert into a database and ofcourse select that date for displaying later.

I try to insert a date using a form (dates are specified from three drop down menus for day, date and month) but it keeps appearing in the database as the default '0000-00-00'. When I select it later I need it to simply display 9 Jan or 10 Apr, etc.

Mysql Dates
I've got a list of Parent items and child items. I would like to use cron to setup a script to run everynight doing this:

Update parent status to old if the dateCreated of the youngest child item is greater than 14 days.

How can I setup an sql statement to achieve this?

Mysql And Dates
I have a table like this:

user   |    date    |    value  |
-------|------------|-----------|
mark   | 13/04/2007 |    1
peter  | 11/04/2007 |    2
peter  | 10/04/2007 |    1
mark   | 11/04/2007 |    3
peter  | 01/04/2007 |    10
mark   | 01/04/2007 |    1
mark   | 04/03/2007 |    34
peter  | 01/03/2007 |    3

i need make a select who give me value where the date its the more near to NOW() for any user.

Mysql Dates
to start off with I don't think this problem is solvable in mysql, although if I'm wrong please feel free to move this post!
Anyway, I have two tables in a database. One stores details on fanarts, another stores details on reviews. Now I want to get the 5 most recent additions to the database. Now for each indivudal table this is an easy thing in MySQL. However to get a result table showing records from both tables, sorted by date doesn't seem to be possible, well not if u alsoi want to know which tables each record came out of.
Therefore I see the only way is to do two seperate querys and then combine the results in php when I output. However for that I would need to compare two dates. Which I can't figure out how to do. I've read around but I couldn' find anything which seemed entirely relevant, although that may be due to lack of understanding.

Mysql Dates
I've got a list of Parent items and child items. I would like to use cron to setup a script to run everynight doing this:
Update parent status to old if the dateCreated of the youngest child item is greater than 14 days.
How can I setup an sql statement to achieve this?

Dates In MySQL
I've got a database in which i'd like to update a datetime from PHP, what is the easiest way to do this if the updated date should always be today?

Access Denied With LOAD DATA INFILE
I'm trying to use the LOAD DATA INFILE statement to load data into a new database. I created the database using the ROOT id, then used...

CREATE DATABASE SWAP;
GRANT ALL PRIVILEGES ON swap.* to 'SWAPUSR'@'localhost' IDENTIFIED BY 'password'

I then quit out of the console and restart it using the new userid - SWAPUSR (mysql -D SWAP -u SWAPUSR -p

After logging in I create the first table...

CREATE TABLE Comics;

So far everything is working fine. However, then I try to load the data from a text file using...

LOAD DATA INFILE 'c:wwwrootcomics.txt' INTO TABLE swap.comics LINES TERMINATED BY '
';

That's when I get the error...
ERROR 1045 (28000): Access denied for user 'SWAPUSR'@'localhost' (using password
: YES)

If I try this using the ROOT ID everything works fine. However, as this needs to work with the non-root id when I load it to my host its a problem.

Access Denied With LOAD DATA INFILE
I am a relatively new user to MySQL running version 4.0.17 on MacOS X
10.3.3 (this is the "current version" installed by the Fink package
manager).

I login to mysql as root

mysql -u root -p

and issue the following commands:

CREATE DATABASE MAPS;
GRANT ALL ON maps.* TO creator@localhost;
FLUSH PRIVILEGES;
use MAPS;
CREATE TABLE Pproto [I have "clipped" the full command here]

Then I login to mysql as user creator (which has no password)

mysql -h localhost -u creator maps

and type the following:

CREATE TABLE P001 AS SELECT * FROM Pproto;
ALTER TABLE P001 ADD PRIMARY KEY (starnumO);
LOAD DATA INFILE '/path_to/P001.in' INTO TABLE P001 FIELDS TERMINATED BY
' ' LINES TERMINATED BY '
';"

At which point the space delimited file at /path_to/P001.in (which is
readable by all readers) fails to be read in with the following error:

ERROR 1045 at line 1: Access denied for user: 'creator@localhost' (Using
password: NO)

Why am I getting a usage error. User 'creator@localhost' should have
all privileges on the maps.* tables, and I can create and alter them,
but "FILES" privilege doesn't appear to have been granted to 'creator'.

Storing Dates In Mysql
Is there a way I can change the way dates are stored in the mysql database from YYYY-DD-MM to DD-MM-YYYY?

MySQL Dates - A Discussion
I'm trying to convert an old db scripted by someone else to my new code
In my new database, I store dates using a datetime field, while the old script used a number like this "1128615400" or "1129126773" (I assume its a timestamp/unix timestamp?)

I have a couple of questions:

1- Which is the best data type to use when storing a date+time that will be used in PHP? (performance wise)

2- How do I convert that number (1129126773) to a datetime field? I tried adding it directly hoping mysql would convert it on its own but I only got zeros

Querying Dates In Mysql
If I want to extract data with todays date then it's 'where blab blah =
current_date()'

For yesterday it's 'where blah blah = current_date()-1'

But can anyone tell me how to extract all data from the start of the
current week? Is there any way in Mysql to work out the start of the
current week?

The current month?

Year?

Displaying MySQL Dates In PHP
I have several fields in my database that are of type 'date'. I want to display this in a browser, but the display seems to ignore the data in each field and uses the following dates only.

Jan 19 2038
jan 1 1970

I have been using PHP's date() function to display the data.

How 2 Insert Dates In Mysql Using Php
i wanna know how to enter the date to the mysql using php........
i have made a sign up form using php n mysql...
i want to add date n time for the registration form.....
hOw's that possible..

Importing Dates Into MYSQL
I have a bunch of data to import into MySQl. One column is birthdate, stored (in Excel) as 10/11/1981 (example). When I import it into MySQl, it shows up as 0000-00-00 (hundreds of rows of this!)

Even in OpenOffice, which lets me format the date as 1981-10-11, it still doesn't import to MySQL (still shows up as all zeros). Is it like others, do I need to "encapsulate" the date (like around # signs)?

IF I set the MySQL field date to text, they import wonderfully, but the second I set that field back to date, they all go to zeros!

Converting Access Db To MySQL Db
I'm doing some PHP work for a friend, and he's got a bunch of Access databases he wants to integrate into his site. Is there a way i can convert those access databases to mysql databases?

Extracting Dates From MySQL For A Given Weekday
Hi. This is kind of strange, but I'm working on a program (using PHP/MySQL) that maintains a work schedule for employees of a mid-sized company. Everything works great, but I want to be able to create a drop-down menu that lets them jump to, for example, the "Week of Monday, July 23". I'm focusing on a solution using MySQL because I only want the drop-down to contain relevant weeks based on what's in the database (no need for an option for some week in 2008 if they only have data through September 2007, for example).

My first thought was to simply do something like...

"SELECT DISTINCT Date FROM Schedule WHERE DATE_FORMAT(Date,'%W%')='Monday' ORDER BY Date"

Well, that would work great if I could count on there being an entry for each Monday. But if there would be a holiday on a Monday and thus no database entries for that day, that week wouldn't appear in the drop-down menu.

So, in effect, whatever the earliest date is in the database, I want to be able to have the Monday of the week for that date appear in the drop-down, and then whatever the latest date is in the database, same thing, and all Mondays in between.

I know I can come up with some clumsy solution, but I'm hoping someone can enlighten me as to a "clean" solution.

Functions For Inserting Dates Into Mysql
Just wondering, besides now(), what are some other functions in mysql for inserting today's date into a database? For example, one of the field's I have is of type "date" that will hold today's date (but not time).

Converting A Mysql Database To MS Access
I have to convert a mysql database to Access with all of its tables and data inside them.

Please suggest me some utility or some methode. I'm a ASP developer and I don't know anything in php so please guide me..

Converting Access Database To Mysql
Have for some years developed applications in Microsoft Access.
One of my clients now require the database to be usable nationwide. Cannot use replication as data changes must be immediately accessible to all users.
I gather, to use Access on the net is either difficult or expensive (using Terminal Server, which is I understand one way of doing it, requires the purchase of multiple user licences and also needs a professional engineer to set it up, etc).
Have no experience of mySQL.

How difficult is it to create/convert the sort of forms one uses in Access to mySQL?
Can one convert the data tables easily to mySQL?
How about queries, reports, etc?

More Questions About The Mysteries Of Time And Dates In Mysql
part of a query I am running goes as follows:
floor(sum(unix_timestamp(dtp.endtime)-unix_timestamp(dtp.starttime))/60) as total.....group by dtp.activityno

dtp.endtime and dtp.starttime are both datetime fields.

I am saving the result into a temporary table and what it gives me is the total number of minutes spent on the selected activitynos...

My question is: if a result from this query is for example 123 (and 123 minutes is the correct number of minutes-2 hrs and 3 minutes) how can I show this result as a date field in MySQL? that is as 02:03:00?
I tried setting the field type to date when creating the temporary table but that just made a mess of the actual number of minutes.
Anybody have any ideas?

Converting MYSQL Data
ive got a loads of entries from an old mysql db ... i want to convert the data in them to txt files.. so it puts the data from a certian field into a text file each.

Mysql Data Input Tool
i was wondering if there were any free data input tools for mysql instead of using the command line client?

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?

Dates Between Dates
Is it possible to query dates between two dates straight from mysql database? Example: I have two dates in my database that function as start of an event and end of an event.

I can get results when I seek an event that starts 2006-07-30 and ends at 2006-08-06 with following sql-query:

select eventid from database where (startdate between '2006-07-30' and '2006-08-06') or (enddate between '2006-07-30' and '2006-08-06');

Or by using any date values that go "over" the specified dates in database. Problem is that I would like to get the event also when it's active between these dates. Like from 2006-07-31 to 2006-08-02, but no result will be returned with this example query:

select eventid from database where (startdate between '2006-07-31' and '2006-08-02') or (enddate between '2006-07-31' and '2006-08-02');

BETWEEN Two Dates
I am making a calendar function in php and i need to know if an appointment already exists between two dates. I am trying to use some like below but with no avail, any other ideas?

SELECT id FROM appoit WHERE UNIX_TIMESTAMP(day) BETWEEN '$date' AND '$until' AND room='$room'

Note:
`day` is actaully stored as a datetime value
$date is the wished start date and time in the format of php's mktime
$until - as above but when the appointment will end

Dates Between
I want to write a query that gets all the dates between 2 specific days (not the difference between them).

Dates
I'm migrating my site form mssql & asp to mysql & php and I'm having a problem with this sql sintax.
What I do is select a value between 2 dates and show the results but I cannot figure how to do it in mysql.
My mssql sintax is:

$sqlstm="SELECT * FROM vidas_pasadas where fecha1<=#".$fecha."# and fecha2>=#".$fecha."#";

How should the mysql sintax look like?

Dates Comparison??
I'm trying to find people from my db based on a given min_date and max_date using:

SELECT . . . WHERE birthdate BETWEEN '".$dd_min_age."' AND '".$dd_max_age."'
but if a range is specified there are no results returned.

If I choose though the same date for min and max then I get the corresponding results.

Recurring Dates
I'm trying to make a calendar with events that can recur daily, weekly, or monthly. I think I can do it like so... any recommendations or improvements?

SELECT *
FROM drinks_e
WHERE
(drink_e_date = CURDATE())
AND
(recurring = 'once')
OR (
(DATE(drink_e_date) <= CURDATE())
AND
(WEEKDAY(drink_e_date) = WEEKDAY(CURDATE())
AND
(recurring = 'weekly')
)
OR (
(drink_e_date <= CURDATE())
AND
(DAY(drink_e_date) = DAY(CURDATE())
AND
(recurring = 'monthly')
)
OR (
(drink_e_date<= CURDATE())
AND
(DAYOFYEAR(drink_e_date) = DAYOFYEAR(CURDATE())
AND
(recurring = 'yearly')
)

Interpreting Dates
I have dates stored in the database in a field called start_date, in the format yyyy-mm-dd.
What sort of query conditions can I use to select only dates:Falling during the current month (i.e. July) orFalling during any given month and year (i.e. September 2007) orFalling over the next x months?Is it possible to select these only using SQL queries?

Count Between Dates?
I want to run a SQL query whereby I count how many records are in a database table ie:
PHP

<?php
          $query = mysql_query("SELECT * FROM dbtable");
          $result = mysql_num_rows($query);
          echo "$result
";
?>

But how could this be modified to count ALL stories from a weekly period, but not specify specific dates - ie find out the total entries for:

- Every time it is Sunday and the time is 00:00
till
- Every time it is Saturday ending at 23:59

So this week I would be counting all dates between

Sunday 25th February at 00:00am
to
Saturday 3rd March at 23:59pm

So when it turns:

Sunday 4th March at 00:00

a new count will begin without specifying the dates?

Handling Dates
Hi I have a table that Iâ€™m going to use for assigning equipment to a task since its going to be used for a simulating a company I need to filter out Sundays and non workable hours. I calculate the time the equipment will be used by multiplying the number of units to be produced by the time it takes mo make one, but I also need to add Sundays and the time that the factory isnâ€™t producing, work hours are from 8am to 5pm. Iâ€™m really lost and donâ€™t know to this, any help will be greatly appreciated.

#----------------------------
# Table structure for orden_produccion
#----------------------------
CREATE TABLE `orden_produccion` (
`Id` int(12) NOT NULL default Ɔ',
`ProductID` int(12) NOT NULL default Ɔ',
`TransaccionID` int(12) NOT NULL default Ɔ',
`EquipmentID` int(12) NOT NULL default Ɔ',
`StartDate` date NOT NULL default &#55612;&#57200;-00-00',
`EndDate` date NOT NULL default &#55612;&#57200;-00-00',
`Quantity` int(8) NOT NULL default Ɔ',
PRIMARY KEY (`Id`,`IdProducto`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The end date is what i want to calculate

How To Get Records Between Two Dates
In database I have stored dates in &#55612;&#57200;-00-00 00:00:00' format.
And from user I am getting dates in 'mm/dd/yyyy' format.

1- How can I get records from database between user entered dates.

Search On Dates?
I have 2,000 stories saved in a database (Not my DB) and I want to perform a search on the section of story and the date it appeared on the website.

The trouble is the DB is dodgy and dates are saved in a format like:
2007-1-8 //Today
Ie
YYYY-M-D

Now what I want to do is perform a MySQL statement whereby I perform a search on the date in this format:

2007-1-8 //Today
Ie
YYYY-M-D

IE: "Y-n-j"

And then my SQL finds the date I asked for, but then

- Checks itâ€™s not greater than todayâ€™s date (NOW) by using unix_timestamp on both the appreared field and published field.

And then output all the results to the screen.

Now I thought this may work:

PHP

$sql = "select id,headline,appeared from news_stories
where section = '$searchstring' AND
date_format('$appeared','%Y-%m-%d') =
date_format(appeared,'%Y-%m-%d') AND unix_timestamp(published) <=
unix_timestamp(now())";

Grouping Dates Together Then Sum()
Can anyone show me how to add the amounts together but by month. So all the month's of 12's added to gether and all the 11's (months).

mysql> select * from date;
+----+------------+---------+
| id | date | amount |
+----+------------+---------+
| 1 | 2006-12-25 | 100.00 |
| 2 | 2006-12-20 | 1456.34 |
| 3 | 2006-11-04 | 400.00 |
+----+------------+---------+
3 rows in set (0.00 sec)

Working With Dates
After taking a break from this project for a bit I find myself confused about how to pull specific date periods from a database. The timestamps are stored in UNIX format. I'm creating a report builder to pull details about issues that my techs have entered (ISP support center). I can pull reports from calls entered today or yesterday, but getting them from say, mon-fri of last week is throwing me for a loop. Can anyone point me in the right direction? This is what I have thus far:

//echo "DateRange: ".$daterange."<br>";
// begin building our query
$query = "SELECT * FROM issues WHERE ";

// the value passed in date range is how we modify the date lookup in our SQL query
if ( $daterange == "today" ) {
$timestamp = strtotime("today");
$query .= "DATE_FORMAT(CURDATE(), '%Y%d%m') = FROM_UNIXTIME(issue_submitted_date, '%Y%d%m');";
}
else if ( $daterange == "yesterday" ) {
$query .= "DATE_FORMAT(CURDATE() -1, '%Y%d%m') = FROM_UNIXTIME(issue_submitted_date, '%Y%d%m');";
}
else if ( $daterange == "thisweek" ) {
$query .= "DATE_FORMAT(CURDATE() -7, '%Y%d%m') = FROM_UNIXTIME(issue_submitted_date, '%Y%d%m');";
}
else if ( $daterange == "lastweek" ) {
$query .= "DATE_FORMAT(CURDATE() -14, '%Y%d%m') = FROM_UNIXTIME(issue_submitted_date, '%Y%d%m');";
}
else if ( $daterange == "thismonth" ) {
$query .= "DATE_SUB(CURDATE(),INTERVAL 1 MONTH) >= issue_submitted_date;";
}
else if ( $daterange == "lastmonth" ) {
$query .= "DATE_SUB(CURDATE(),INTERVAL 2 MONTH) >= issue_submitted_date;";
}
else if ( $daterange == "thisyear" ) {
$query .= "DATE_SUB(CURDATE(),INTERVAL 1 YEAR) >= issue_submitted_date;";
}
else if ( $daterange == "lastyear" ) {
$query .= "DATE_SUB(CURDATE(),INTERVAL 2 YEAR) >= issue_submitted_date;";
}