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 '
'
View Complete Forum Thread with Replies
Related Forum Messages:
Load Data Infile (empty Dates Fail)
I had a quick question concerning load data infile. I have a tab-delimited data file (unfortunately I cannot provide a copy to it because it contains senstive data. Whenever it comes to empty date fields, it fails. The table is set to allow for null dates though. Here's my query. LOAD DATA LOCAL INFILE 'Z:/Websites/Workspace/Agency Works Data Export Files/decrypted/2006/August/8-14-2006/349/PLAN.txt' REPLACE INTO TABLE agency_works.PLAN FIELDS TERMINATED BY ' ' LINES TERMINATED BY '' IGNORE 1 LINES;
View Replies !
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?
View Replies !
Update String Dates Into Mysql Dates
I've got a table that someone created with varchar fields for month, day, and year. I've added a DATE field to the table, and now I want to combine all three of these strings into one DATE and stick it into the DATE field. Once I've got this done, I can delete those three varchar fields and just have the nice DATE field. Here's the command I've tried in several variations but no luck--syntax errors every time: UPDATE SET OCR_Entry_Date = date_format(str_to_date(concat_ws('-', OCR_MM, OCR_DD , OCR_YYYY) ,"%b-%d-%Y"), "%m-%d-%Y") WHERE OCR_YYYY = '1974' OCR_Entry_Date is a DATE field and the others are the varchar fields. My logic is basically: 1. concatenate the separate field values into a single string 2. convert the string into a date 3. format the date in the way I'd like it in the table
View Replies !
MSSQL Dates -> MySQL Dates Automatically?
We're using data feeds that were originally meant for MS SQL and the dates in the data feed (tab delimited text files) are formated like: Oct 21 2007 In MySQL, the date fields are formatted as datetime fields and when we do an import the dates all come through as 0000-00-00 00:00:00. We're importing using the MySQL "LOAD DATA LOCAL INFILE" command and just dumping the text files right into freshly truncated tables. Is there a way when loading the files to find and replace the dates maybe? Can MySQL convert the dates? I have a shell script downloading an archive and uncompressing, then it runs a PHP script that loops through the files runningt he LOAD DATA command. Any advice would be appreciated... I'm just looking for the easiest (and least server-intensive) way to get the date issue fixed. We've already tried begging the vendor but they're not yet ready to start supporting other date formats (even though their MS SQL db can output a date format friendly to MySQL). Thanks!
View Replies !
Storing Dates :: From Access To MySQL
I'm currently in the process of migrating from MS Access to MySQL. In my MS Access database the dates are stored in the format MM/DD/YYYY. However on MySQL it seems to want the format YYYY/MM/DD. Therefore the dates aren't being stored properly when I'm importing data into my MySQL database, they are just being displayed as 0000-00-00. Is there a way I can change the format the dates are saved in MySQL?
View Replies !
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.
View Replies !
Dates Pre 1970 And Dates After 2050
i have MySQL version: 4.1.16-nt, when i try to insert a date pre 1970 or after 2050 into a timestamp field, the date is stored as all 0's, how can i save a date pre 1970 and after 2050 into a mysql field?
View Replies !
List All The Dates Between 2 Given Dates Using SELECT
how I can list all the dates between 2 dates (inclusive) using SELECT sql statement in mysql? I have a member table with the following columns: id int autoincrement name varchar(40) join_date date I want the following output (date, count of members joined on that date): 1 April 2009...... (2 members joined) 2 April 2009...... (0 members joined) 3 April 2009...... (3 members joined) .....so on ...upto 30 April 2009.....(1 members joined) For the above output I need the SQL command that is valid in mysql. Is there anyone who can produce the above result with a single SELECT command. Please don't suggest that is possible using php for loop or creating a mysqlorary table first and then inserting all the dates into it. Then make a join between mysql table and members table using date as GROUP by column etc. etc. I know this is possible using some user variables as I searched through the various SQL tutorial sites. But I am unable to figure out how I can produce a list of all dates between 2 given dates.
View Replies !
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 !
View Replies !
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.
View Replies !
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
View Replies !
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:
View Replies !
Selecting Data Between Dates
I need to extract data that is between the current date and 13 weeks back. I have tried the query below but it does not seem to be working. Can anyone tell me what i am doing wrong SELECT week_ending FROM leadership_input_sheet WHERE week_ending BETWEEN 2007-01-01 AND (CURDATE() - INTERVAL 13 WEEK)
View Replies !
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) .
View Replies !
Find Data BETWEEN Dates
I got the following structure in my table : -------------------- userid | reg_date -------------------- 1 | 2008-10-21 2 | 2008-10-22 3 | 2008-10-23 4 | 2008-10-23 -------------------- I need to get the users registered between 2 dates. But my input will be only a day of the month, something like : 21. So need to find users registered between 21 and the current date.
View Replies !
Iterate Over All Dates Requested, Even If No Data
So let's say I have a table with entries in it, with dates associated. date | message ==============+======= 2009-01-12 14:15:16 | Some Data 2009-01-13 01:23:45 | More Data 2009-01-15 21:09:57 | Data Now I want to do a report for the last 7 days on the 16th. Here's the report I want: day | count ========+==== 2009-01-09 | 0 2009-01-10 | 0 2009-01-11 | 0 2009-01-12 | 1 2009-01-13 | 1 2009-01-14 | 0 2009-01-15 | 1 Since the data in the table isn't a good source of dates, and I can't just do a substr() on the timestamp and group (leaving me with 3 report rows instead of 7), how do I tell mysql to create a mysqlorary data set with 7 days of rows? I know I can do it like this: set @a=7; // create a mysqlorary table // REPEAT an insert 7 times to insert 7 dates // do my select as a join between the two tables // delete mysqlorary table
View Replies !
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?
View Replies !
Converting Access To Mysql
I'm converting a MS Access database to MySQL and encountered the following column names and I'm not sure if it's okay or better not to use those names: CODE, MASTER (in lowercase by the way) MASTER made me doubt since there's the whole replicatin situation with master and slave, so I'm not sure if I can end up with problems naming a column 'master'.
View Replies !
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?
View Replies !
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.
View Replies !
LOAD DATA INFILE :: Access Denied
When I try to issue a LOAD DATA INFILE statement from the MySQLAdministrator SQL page, I get the following error: #1045 - Access denied for user I am logged in and I have access to the file being processed, but still nothing. I've also tried this from a PHP script and get no response whatsoever. HOWEVER, if I use the Import tab from MySQLAdministrator, I am able to process the file just fine! How does this code differ from what I'm trying to do with the LOAD DATA INFILE command?
View Replies !
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.
View Replies !
Converting MS Access Application To MySQL
I have several MS Access databases that I need to scale up (need to support more users, speed things up and make it more reliable that a JET database). I have experience of developing Access databases, MSSQL 2000 and MSQL 2005 databases and some experience of MySQL databases. I'd like to migrate to using MySQL for all our databases both for cost reasons and possibly being platform independent. Converting/migrating/re-designing the database structure in MySQL doesn't worry me but I'm unsure about which way to go in developing the front end application for the databases. I do like MS Access, even as a front end to MSSQL due to the speed with which I can develop and modify forms, reports etc. into a complete desktop application and know I could probably still use Access as a front end to MySQL but I just wondered if anyone has any advice on alternatives for the front end application - is the only other real option a browser based app using something like PHP for the logic?
View Replies !
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?
View Replies !
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?
View Replies !
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!
View Replies !
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.
View Replies !
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
View Replies !
Access Denied With LOAD DATA INFILE MacOS X 10.3.3
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'.
View Replies !
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).
View Replies !
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.
View Replies !
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?
View Replies !
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.
View Replies !
Converting Access From .CSV
I am trying converting a query in MSACCESS 2007 into *.csv File to upload them on the web server MYSQL database using PHPMYADMIN Actually I am a little bit confused because there are many types of *.csv file (common delimited, Macintosh, Dos ) Is *.csv file type is the best file format for MYSQL to be imported ?
View Replies !
Using Between For Dates
I use a query like this: [MYSQL]AND `updatedate` BETWEEN '2008-10-26' AND '2008-11-01'[/MYSQL] While that works, it does not display the records on Nov 1. I could use this: [MYSQL]AND `updatedate` BETWEEN '2008-10-26' AND '2008-11-02'[/MYSQL] but that includes all of the records on both Nov 1 and Nov 2. Anyway, how to get it only display records that have dates from Sept 26 to Nov 1 and excluding the rest?
View Replies !
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');
View Replies !
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
View Replies !
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?
View Replies !
|
TRACKER
