Dates In Querys
I'm returning the month from a datatime stamp in a mysql query using the following:
MONTH(theDate) AS tMonth
Only problem is, if the month is before november, it doesnt not return the preceeding 0, so for example.. september I only get 9 not 09.
Is there any way in the query to define this?
View Complete Forum Thread with Replies
Related Forum Messages:
Two Querys In One
How can I make this both querys be only one recordset? select distinct grupo from sentient_campos where id_madre = 1 select distinct grupo_madre from sentien_campos where id_madre = 2
View Replies !
Merging Two Querys
I have two query that I I would like to be one, but I can't figure out how to do it. (mysql 3.23) $sql = sprintf ("SELECT task,SUM(hours),login FROM activities WHERE login='%s' group by login",$usuario); $sql2 = sprintf ("SELECT SUM(hours) FROM activities WHERE login='%s' AND month='%s' group by login",$usuario,$getdate);
View Replies !
Multiple Querys
I'm working on a query where I'd like to be able to search one OR two attributes. I've come accorss a few postings that lead me to the code below. It works fine if I put something in both attributes but fails if I leave the second blanc. Can you help? SELECT * FROM books WHERE title LIKE '%$title%' AND title_sub LIKE '%$title_sub%' ORDER_BY print_id (p.s. I would lideally like to make the first attriubute optional. I may also want to add a few more attributes later)
View Replies !
Multiple Querys Into One
How can I reduce multiple querys into 1 I have 1 main select PHP //get all the booking data from the tables, now I need to $cust_sql = mysql_query("SELECT DISTINCT customer.bid, customer.oid, customer.c_name, customer.pnumber, customer.mnumber, customer.party_names, customer.paid, booking.startdate, booking.enddate, booking.cid, booking.resort, booking.villa, booking.vtype, booking.adults, booking.children, booking.infno, booking.outfno, booking.atime, booking.cost FROM customer,booking WHERE customer.bid= '" . $_GET['id'] . "' AND booking.id='" . $_GET['id'] . "'"); if (!$cust_sql) { echo 'An error has occured with the db and is detailed below:<br> ' . mysql_error(); exit; } $row = mysql_fetch_row($cust_sql); I then run a another to return the country name using the booking.cid from the main result set. PHP $country_sql = mysql_query("SELECT DISTINCT country_name FROM country WHERE cid = '".$row[14]."'"); Is there a way to condense the 2 or more queries into the main one.
View Replies !
Mysql Shuts Down During Rapid Execution Of Querys
I am using version 4.0.12-nt of MySQL and when I hit the enter key rapidly I can't connect to the database. The result is a message is returned to me from mysql that says I can't connect because the database is down. If I only hit the 'enter' key once (to execute the page that my queries are on(their are four queries on this particular web page)) my queries execute just fine. We have noticed that this only happens when we:
View Replies !
20 Different Tables Querys Slowing Page Display
I have an issue with a very large PHP page that calls over 20 different MySql Statements. This page is loading somewhat slow and i want to speed things up. I have looked into sql caching applications like http://eaccelerator.net/ and http://www.danga.com/memcached/ which i may install later, but i feel they are more for pages that are called frequently, my page calls the same data less times but can call more than 2000 different versions of the data. Being that my page is a little over 1000 lines, putting it all here is not gonna happen. I will try to shorten the code using examples... After writing the blow code.. it may be really confusing. I have stuck the file on my server http://www.empiresolutions.net/clien.../green_bar.zip in case anybody whats to get a better look. This file in it current state works fine, just is slow. Any and all help is so appreciated. Another Question... Can anyone provide suggestions on how to bench-test my sql scripts to see exact improvment speeds? Each indent means the following query is nested. Query 1 (2 Join Tables) Query 2 (2 Join Tables) Query 3 (1 Table) Query 4 (4 Join Tables, While Loop) Query 5 (2 Join Tables) Query 6 (1 Table) Query 7 (4 Join Tables, While Loop) Query 8 (2 Join Tables) Query 9 (1 Table) Query 10 (1 Table, While Loop) Query 11 (2 Join Tables, Calls every 10 rows from Query 10) Query 12 (2 Join Tables, Calls every 10 rows from Query 10, same as Query 11 basically) Query 13 (2 Table, While Loop) Query 14 (1 Table) Query 15 (2 Join Tables, This query and subs are called 4 times in a FOR loop) Query 16 (2 Join Tables) Query 17 (2 Join Tables) Query 18 (1 Table) Query 19 (2 Join Tables) Query 20 (1 Table)
View Replies !
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 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 !
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 !
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 !
Dates Between
I want to write a query that gets all the dates between 2 specific days (not the difference between them).
View Replies !
Between Dates
SELECT * FROM Bookings WHERE PropertyID = 1 AND ((StartDate BETWEEN 1122854400 AND 1125532799 )OR (EndDate BETWEEN 1122854400 AND 1125532799)) Thats my SQL statement, this is my end date 1124037406 which unless my maths is failing me, is between the 2 values but my sql query returns no results.
View Replies !
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.
View Replies !
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??
View Replies !
List Of Dates
i need a jsp program code for date generation.(mssql-back end) Fields are fromdate and todate both field are datetime datatype. Button:-save,cancel,exit both dates are given by the user how to generate the date between these two date. ie.lists of date between the fromdate and todate with datatype datetime
View Replies !
Average Between Two Dates
I am trying to figure out how to find the avg in between two dates in the work_date field. For example, let's say I want the avg from 2007-01-24 to 2007-05-06, I would have to find the avg by taking the values in the daily_typing_pages columns and add up all the columns between the the dates provided. (E.G. (100+220+350+250+170)/5).
View Replies !
Add Dates In Loop
but I can´t get it to work. I have an table with columns named like this: 2004-12-01, 2004-12-02 and so on, So I need to get the dates between two dates and update with the new values the columns with the same name as the dates I got. $date = 2004-12-01; $enddate = 2004-12-10; for($date<=$enddate; $sSQL++ { $sSQL = "SELECT DATE_ADD('$date', INTERVAL 1 DAY) date"; $sSQL = mysql_query($sSQL); echo "$date"; then I should update my table like this: $sSQLsql = mysql_query("UPDATE `disponibilidad` SET `$s` = 'w' WHERE `property` = 'Banana_Beach_3' ");
View Replies !
Compare Two Dates
I have a field in my database called rntl_date and this contain the date when the apartment will be available. I want to compare this with Curdate() and if this is less then Curdate() to show Availabe For example: rntl_data curdate() 2004-12-12 2005-10-10 ->Available 2006-12-12 2005-10-10 ->Non available
View Replies !
Formated Dates
Here is my dilemma.I have 2 date formats. One is in the format of 2006-07-11 and the other in 7/7/2006 for example. I need to pull data from MySQL withing a certain date range or where a date matches a certain criteria. So my question is if it is possible to compare different date formats like the ones above when executing a query in MySQL.
View Replies !
Finding All Dates Where The Value Changes
Example_ID Example_DateAndTime Example_Value This table currently has > 1,000,000 rows (and at least 10 other columns). The Example_DateAndTime column is different on almost all rows. The Example_Value column is quite often the same (changing less than once a day).
View Replies !
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?
View Replies !
Dates Prior
I'm working on a database of biographies and most of these people lived prior to 1969 ... Is there some secret? Or do I just have to make the field a VARCHAR instead of DATE?
View Replies !
Storing Dates
i'm currently working on a small php program to store details of petrol receipts. The part i'm stuck on is which field type to use to store the date. And then how to order the dates. i need to order by year then month and then day. I have no idea how to sort dates and need to be able to add receipts in any order. *edit* the date i want to store is in the format DD.MM.YYYY the date is created via pull down menu's
View Replies !
Jobs Between Two Dates
I was wondering if any of you could tell me is this statment will work. I have been running it but not getting any errors but not really getting results I would like. SELECT * FROM jobs WHERE P_date BETWEEN ('2007/01/22') AND ('2007/01/20') AND MA = 'true' AND Job_State = 'Open' AND (S='true' OR W='true' OR F='true') ORDER BY D_date I'm trying to get jobs between two dates with a lot of other AND's in there. Thank you for your time and effort in helping me with this, I hope my code has not caused you to much pain
View Replies !
Function For 2 Dates
i have inserted 2 dates say "2008-01-01" and "2008-01-05", I want the return to be "2008-01-01", "2008-01-02", "2008-01-03", "2008-01-04", "2008-01-05" . Is there a mysql function or need a custom function? IF so, can you provide some reference ?
View Replies !
Return Dates
I need to query the database to find dates and time slots that are not in the database, so that i can find what days and times I have available for my job booking script. I have a 'DATE' (MYSQL DATE FORMAT) column and a column named 'TIME' that gets a number either 1 - 5 this defines time slots i later use php to output the time to a readable format.( for ordering reasons) My script is a scheduling and booking system, I just need an easier way to find what I have available rather than outputting the jobs in the database and finding gaps by human deduction.
View Replies !
Query For Dates
Table Data: ----------- id__name___from_________to_______ ------------------------------------- 1___ABC___2006-10-01___2006-10-31 2___CDE___2006-11-01___2006-11-31 is there a syntax to select both rows at once? i.e. if I query for dates from 2006-10-15 to 2006-11-15, is there a possibility to have the two rows as a result of a single SQL Statement query?
View Replies !
Query Dates
I am trying to find records where my approved date is not NULL and my shipped date is NULL. Using phpMyAdmin the records show cells with italic NULL and others with dates ie. 2006-06-11, so I assume this is formated right. However using this query I get no records. SELECT * FROM records WHERE approved_date != NULL AND shipped_date = NULL I have used phpMyAdmin's search section with every conceivable way and I can't get any proper results.
View Replies !
Search Between Dates?
i have the following query ,PHP Code: $query_january = $db->query("SELECT * FROM stock_vehicles WHERE purchasedate BETWEEN '2005-01-01' AND '2005-01-31'"); which should as far as i know search between January the 1st and January the 31st but it seems to pull all entries from the database.
View Replies !
Search Between Dates
I thought I had this figured out but I guess I don't here is what I have. SELECT * FROM servers WHERE server_id=".$_GET['server_id']." AND DATE_FORMAT(serve_date, '%m/%d/%Y')>= ".$_GET['datefr']." AND DATE_FORMAT(serve_date, '%m/%d/%Y') <=".$_GET['dateto']." It works but when I enter dates 02/02/2007 to 03/01/2008 I get 02/25/2005 and 02/23/2008 returned. How come the 2005 showed up?
View Replies !
Selecting Dates
I have a table with events, which have a EVENT_DATE column of type date. For events listings I'm using the following SQL query to retrieve the events of the next 7 days: PHP Code: "SELECT event_id FROM events WHERE DATE_ADD(CURDATE(),INTERVAL 7 DAY) >= event_date ORDER BY event_date ASC" It does return the events of the next 7 days, but it also returns an event that happened 2 days ago. Any idea why this is happening? I thought the above SQL query means, select all events that are today (CURDATE()) or within the next 7 days
View Replies !
Get Recurring Dates
I have been pointed in the direction of SQL rather than PHP to accomplish what I want and I am not sure of the logic. I have a table that contains among other fields the following fields: uid, intrval, freq and start_date. I need to get the start date and if there is a freq higher than 0 (it will be month or week) I need to create a list of dates of recurrence for the next 12 months. I then need to dump everything into a mysql table. I was going to do this in PHP but I have been told SQL will be more efficient? I have done in PHP what I want to achieve in SQL: ....
View Replies !
Query Using Dates
I'm having trouble figuring out how to get two time periods in one query. Basically it's either one month at a time or a full year...So I need to get everything that starts and stops this month, or cases where this month falls between the start and stop dates. I couldn't figure out how to do the between bit, so I settled for calling an expiration date that is not older than this month.I get partially correct results. It's calling the five ads that start on 2006-12-01 and end 2006-12-31, and ignoring the ones that start 2007-01-01 and end 2007-01-31. However, I've got a test ad that starts 2006-01-01 and ends 2006-12-31 that it's ignoring...I think my timeframes are cancelling each other out or something. $sql = "SELECT ad_id, ad_filename,rally,affiliates,past,upcoming,start_date FROM ads WHERE Month(start_date)='$curmonth' AND Year(start_date)='$curyear' OR Month(end_date)>='$curmonth' AND Year(end_date)>='$curyear'ORDER BY rally='y' DESC";
View Replies !
Using Wildcard And Dates
This is my query: SELECT * FROM quick_look WHERE '%-08-25' BETWEEN start_date AND end_date % is the 4-dig year. If I replace % with a specific year, it will return results for that year, but I have several years I'm trying to search therefore I'm using the % character. However, it does not bring me back any results (nor errors).
View Replies !
Sorting Dates
I have a database which I am using to store written articles of text or a Website. I want to be able to offer the user the ability to search for articles in any given month, let's say July 2006 or August 2005. At present my tables consist of fields for - day - month and - year ...each of data type int. I then run a basic php function to find the number of days since 1/1/1970, given these 3 variables. It is this figure which I sort on. Now I know that this is not the best method, so which data type should I be using to make my life easier and my code more efficient.
View Replies !
Comparing Dates?
i have an 8 digit date string that i want to compare to a column of type 'date' in a MySQL db. the 8 digit string is in the format 'CCYYMMDD'. the question is: do i need to put it into the 'CCYY-MM-DD' format in order to get correct output from the datediff function, or can i simply leave it how it is?
View Replies !
Dates Not Changing
I know mySQL is supposed to accept years up to like 9999 or something. I have a variable thats stored as a DATE. The problem is if you enter a date in the future, it resets the value to 0000-00-00 but if I put in todays date or an earlier date it does not change it. Why?
View Replies !
Select Dates
I need to select all dates between (and including) two given dates. something like SELECT DATE() BETWEEN '(dynamic date)' AND DATE_ADD('(dynamic date)', INTERVAL 90 DAY) so that the result set would contain 90 days.... Many thanks in advance for your help.
View Replies !
Reading Dates
I have a form in html which has many date fields. I want mysql to read the dates through PHP and store it in a table.
View Replies !
|
TRACKER
