MYSQL :: Date Field Saved As 0000-00-00

Problem With 0000-00-00 In 'Date' Field ?
I have a field in a table called 'ShipDate'. This particular field has allow null set to no. So by default this sets all the records to show 00/00/0000 in the field for each record that has no data entered.

In my .asp pages I am trying to test against this empty field.

i.e.

If RS("ShipDate") = 0000-00-00
or
If RS("ShipDate") = 00/00/0000

The only thing that seems to work is if I am testing for records with
a date in that field,

If RS("ShipDate") > 0000-00-00

How can I test if that field is empty ?, nothing seems to work ?

Date Field In Database Shows 0000-00-00 00:00:00
i have the following in my list of variables (partial list):

$id = $_POST['id'];
$name = $_POST['name'];
$date = date("D, d M yyyy, h:ia T");

and my connection includes:
$sqlquery = "INSERT INTO $table VALUES('$id','$name','$date')";

all other values are posting except the date.

in the mysql database, the date field is set up as follows:

Field Type Null Default
date timestamp Yes CURRENT_TIMESTAMP

my objective is simply to add a date field with the current date and time. i want mysql to insert the date for me in the format resulting in this type of output:

Fri, 19 Aug 2005, 10:23am CST. i just wasn't sure whether i was supposed to
include the date variable or not.

MYSQL Date Entry Shows 0000-00-00
i want the user to type in the date. Everything works fine and the form submits. When i open the database and query the results, the date field shows only 0000-00-00. It doesn't show the date entered (ex 05/23/07). What am i doing wrong?

I'm thinking it has to do something with converting the string to a date? If so, how do i do that in PHP.

During Import, The AM/PM Of Datetime Field Are Saved Incorrectly - How To Fix?
I am importing from a CSV file with date format like:
12/4/2006 12:02:13 AM
12/4/2006 1:21:04 PM
into a datetime field.
It is importing the above records as:
12/4/2006 12:02:13 AM
12/4/2006 1:21:04 AM
which obviously needs correcting.
Is there a painless way to correct this during the import process?

Loading Date Format 1-1-2007 To Date Field Doesn't Work
I'm trying to load the date into a date field in mysql so that I can sort by date, but the date field in mysql is yyyy dd mm or yyyy mm dd so it doesn't load. What can I do?

How To Convert A Varchar Field Into Proper Mysql Date Field?
I have a database in which the date is stored in varchar field in a following format: d-m-Y (06-08-2007), now the problem is that I want to change that field into mySQL date field as well as convert my older dates into MySQL date format i-e Y-m-d (2007-08-06)..

There are about 300 old entries..is there a way I can do that automatically without manually re-entering the dates again?

Select Date Only From Date Time Field
if I have "Date Time" field , How can I get the Date only from this field.

Date/time Field Update Only Date
I have a field(tmMessage) in a table, it's a date/time struct.
it looks like "2003-09-21 15:52:35"

All I want to do is change the date in this field.
I want the time to stay the same.

Character Saved
i'm having a probelm because i'm upgrading mysql 4.0 to 4.1
in field password, i'm keeping hashed password, so the characters has been scrambled.
when i'm using mysql 4.0, i'm able to keep all my hashed password, but after upgrading to 4.1, few character of my hashed password not saved.
i'm currently using varchar for my password.
this is an example of the password. l€A;š–Á1,ÃF·ÒQ{„F>Ý
mysql 4.1 only save the first character.
anybody can help me??
is it only a settings problem, a bug, or...... what?

Date Field, Time Field, Sort By Date And Time
I have a MySQL date field (e.g 13/12/2006) and a MySQL time field (e.g.13:00) in the same row.

I'd like to join these two fields to make a date/time field (e.g. 13:00 13/12/2006) on the fly and to be able to sort the query results on the resulting field.

Date Range On Date Field
I have a table that has birthday dates stored as a DATE field. How would I get all the rows returned based on a min years old and a max years old...Let's say...
My form has....

Display Rows by birthdate in years....

Show between (23 years old) AND (36 years old) from the current month/day/year

Not All Character Saved In Mysql 4.1
i'm having a probelm because i'm upgrading mysql 4.0 to 4.1 in field password, i'm keeping hashed password, so the characters has been scrambled.

when i'm using mysql 4.0, i'm able to keep all my hashed password, but after upgrading to 4.1, few character of my hashed password not saved. i'm currently using varchar for my password.

this is an example of the password. lâ‚¬A;Å¡â€“Ã�1,ÃƒFÂ·Ã’Q{â€žF>Ã�
mysql 4.1 only save the first character.

Scheduled Backup Cannot Be Saved
I am working with MySQL Administrator v1.0.17 on a W2K SP4. When I try to create a scheduled backup the message appears:

"Error reading PasswordEdit.|*|Tab: Property |*|Tab does not exist" and it is not saved. If I try to save without scheduled backup it works.

Does someone have any idea whats wrong? P.S.: in the password storage options i have checked the "Store passwords" box and "Password storage method" is Obscured.

0000 Turns To 0 With INT
Okay I need an id that is 4 (four) digit id but when I put 0000 its turns to 0 how to I avoid this.

Date Field, Time Field
I have a MySQL date field (e.g 13/12/2006) and a MySQL time field (e.g.13:00) in the same row.

I'd like to join these two fields to make a date/time field (e.g. 13:00 13/12/2006) and to be able to sort the query results on the resulting field.

MySQL Db - Data Not Saved (Using Frontpage ASP)
I have set up a MySQL db on one server, IIS 6.0 on another. Using
Frontpage I created some forms to input data. On a test page I made, I
have 4 fields. First is the ID which is autonumber (I leave alone). The
second is marked as TEXT, the third is MEDIUMTEXT, and the fourth is
DATE. This is just an example though, the problem exists in no matter
what manner I create the form.

Now here is the issue. In the TEXT fields nothing is saved into the db.
But whatever I put in the DATE field is saved. I played around a bit and
found if I put in a single letter (a for example) it will be saved in
the TEXT fields. If I put in "aa" it will not save.

Now if I create a form that is to accept just 4 fields of a numbers the
form will work properly. I.e. a form made for inputing IP addresses and
telephone numbers will work. Once I add a field for a name then the form
will only save the number.

Yet in the db itself I have no issues putting in long text phrase into
the same field (Using OpenOffice). Therefore I assume this is some issue
on how either Frontpage created the form or the webserver is sending the
data. Is there anything special I need to do to get a from made with
Frontpage to work with MySQL db? I have made numerous forms to use a MS
Access db without any issue. Code:

MySQL Db And Frontpage- Data Not Saved
I have set up a MySQL db on one server, the IIS 6.0 on another. Using Frontpage I created some forms to input data. On a test page I made I have 4 fields. First is the ID which is autonumber (I leave alone). The second is marked as TEXT, the third is MEDIUMTEXT, and the fourth is DATE.

In the TEXT fields nothing is saved into the db. But whatever I put in the DATE field is saved. I played around a bit and found if I put in a single letter (a for example) it will be saved in the TEXT fields. If I put in "aa" it will not save.

Yet in the db itself I have no issues putting in long text phrase into the same field. Therefore I assume this is some issue on how either Frontpage created the form or the webserver is sending the data. Is there anything special I need to do to get a from made with Frontpage to work with MySQL db? I have made numerous forms to use a MS Access db without any issue.

Curdate() Is 0000-00-00 For New Entry
I have a web form that users submit information through and it gets stored in a MySQL table. I wanted to capture the "creation date" of the record. So, I do the following:

1) I connect to the database and ask for the curdate() assigning it to a variable.
2) Then I turn around and store the user input into the table along with the curdate() variable that I first retrieved.

However, I just noticed something while still testing this. If I drop/add the table, the first entry always retrieves a curdate() of 0000-00-00. And this is a issue as I later allow users to retrieve these records based upon their creation dates. So, the first one will be lost.

Why is the first record recording 0000-00-00? The table exists even without any data in it. And, how do I get around this?

'datetime' Shows Only '0000-00-00 00:00:00'
I currently have a form set up on a site to send data entered into a database.
Now, the form submits correctly, the data is stored in the database correctly,
but the 'datetime' shows up as '0000-00-00 00:00:00' in each record.

I set the datetime variable to 'not null' and when I update the table, it sets the
default value to '0000-00-00 00:00:00'. Am I forgetting to set something or
where and how does the form get that date and time info?

0000-00-00 00:00:00 Format Related Question ...
I am trying to count number of rows by month and year. In the mysql db, I am recording data as NOW(), means 0000-00-00 00:00:00 format.

DATE Field....
i have a date field, that my users enter for when they captured a lead...

field name type
Lead_Date DATE

and the format is yyyy-mm-dd

i then have a page that calls the entire table for my agents to view...

but if they did not happen to enter a date into the Lead_Date field, it shows: 0000-00-00

how can i just not have it show, or show blank?

Date Field
I have a date field set as default="NULL". I think that is causing me problems when I try to see if that date field contains a value. The mysql below only works when I get rid of the date field AND charged='NULL'. How can I tell if a date contains a NULL value??

"SELECT *
FROM orders
WHERE enterdate >= '$fromsql'
AND enterdate <= '$tosql'
AND orderstatus='CLOSED'
AND charged='NULL'

Date Field
I have a table with a date field, stored I believe as yyyy-mm-dd. I want to pass a date value to a page and match records based on that particular date. I've tried this:

SL.seminar_date = '" & request("date") & "'"

and several variatons of the same but keep getting an error.

Date Field Gets Changed ...
Through my php script user x uploading files to mysql table.
then user y login and correct that file & put grade for that file up to that the grade
field is empty. now i am getting problem here.
my table having date_time field, it is indicating the uploaded date & time of user
x.
when user y update that grade the date_time field also gets updated by the current
time, but i didn't change that field through my query.
here i want to keep the previous date_time .
How it is? Thanks in advance.
shyami

How To Get Date From DateTime Field
my Login field is in Date n Time format---->6/12/2006 02:30:25pm

And in my query, i juz wan to get the date wic is 6/12/2006.. how to write the sql?

Date Field Gets Changed ...
Through my php script user x uploading files to mysql table.

then user y login and correct that file & put grade for that file up to that the grade
field is empty. now i am getting problem here.

my table having date_time field, it is indicating the uploaded date & time of user
x.

when user y update that grade the date_time field also gets updated by the current
time, but i didn't change that field through my query.

here i want to keep the previous date_time .

Date Field Importing
Our informix database has a date format(MM/DD/YYYY) in all tables. We are trying to import into MySQl. MySQl requires YYYY-MM-DD format. Is there a way to configure Mysql to accept the informix(MM/DD/YYYY)date format.

MySql Date Field
I need to query data from a MySql database based on a date field but I notice all the date fields appear to be stored as numeric data types, see below:

Ticket_ID    Opened_Date    Closed_Date
1            1115904879    0
2            1116515087    1116515801
3            1118401271    0

Update A Date Using A SUM(field)
I have been asked to come up with a way of updating a date field, so that it subtracts the total number of days. If there was only one record to add to the field then it would be a simple case of

DATE_SUB(m1.date_joined, INTERVAL p1.qual_days DAY)

but unfortunately, there can be more than one record in the second table, so I need something like

DATE_SUB(m1.date_joined, INTERVAL SUM(p1.qualdays) DAY)

but it won't work, even after I add in a GROUP BY statement at the end of the query, I get an error using GROUP BY.

I have now resorted to creating a temp table to dump all the calculations in there and then doing an update using the values but I'm trying to find out if there is a better way of doing it.

Search A Date Field
I have a date field defined as:

'created_date' timestamp NOT NULL default '0000-00-00 00:00:00'

It is also an indexed KEY. What would be the fastest search on that field if my desired results were for records created during a specific month?

created_date LIKE '2007-07%'

created_date REGEXP '^2007-07'

created_date>'2007-07-01 00:00:00' AND created_date<'2007-08-01 00:00:00'

Thanks for your opinions.

BTW, if you know Perl do you think it would be faster for MySQL to give me created _date data as DAY(created_date) or for Perl to split the date (first on a space character and again on the hyphen character)?

Date Field Puzzle
I'd like to sort the files by the date field (DoA), when when it is used like this, how do I, please, Gurus?

... CONCAT(TO_DAYS(Curdate()) - TO_DAYS(DoA), 'days') AS LateBy ... ORDER BY LateBy DESC;

Default Value For A Date Field
I am trying to create a column inside a preexisting table. The column has a type of date. Is there a way to set the default value to now() or curdate()? In MSSQL the default value would be 'getdate()', but when i try to use the MySQL varient an error returns saying this is an invalid default value.

Statement Concerning Date Field
I'm pulling my hair out with what seems to be a simple operation.

My table has a DATE field. I'm simply trying to pull out records that satisfy the highest date in the table.

Via PHP, I'm first running a query to get the highest date value--that works fine. Then I try to run a second query with a WHERE statement using the previous max date as criteria. I get an error. I've tried quoting/not quoting and even using timestamps. I'm stumped!

CODE//first, get the date of last chart

$query = "SELECT max(tbl_charts.date) AS HighestDate FROM tbl_charts";
$queryname = "getMax";
include "querydb.php";
while ($line = mysql_fetch_array($getMax,MYSQL_ASSOC)) {
$lastdate=$line['HighestDate'];
//$year=substr($lastdate,0,4);
//$month=substr($lastdate,5,2);
//$day=substr($lastdate,8,2);
//$datestring=$month."/".$day."/".$year;

code...

Now() In Default Value Of Date Field
I have been using the MySQL Administrator in order to create tables and have been unable to set the default value of any date fields to the current date by using the Now() function. When I attempt to do so, I receive the error message: MySQL error number 1067.

Has anyone experienced this problem? If so, how do I set the default property of a date field to the current date?

Blank Date Field
How do you determine if a Date field is blank. In my Sql statement I
only want records who's date field is blank (i.e. checking for only open
invoices where Date_Paid = '' ...still open ...it doesn't work when I use
that syntax)

MySQL Date Field
I have three drop downlists which contain month, year and day. I want to be able to select a date from this drop downlist and match it to a date in the my sql database.
The problem is, is that when I select a date from the drop downlist and use a query to match it to a field in the database it only works when the field in the database is not in format:datetime. It works with format:varchar but not date/time format.
Can anyone help me with this problem as I cant seem to find a solution.

Date Related Field
This may sound awkward, but I have a field that is of varchar(10) type with dates in the format mm/dd/yyyy
Is there anyway to query on this table and sort by this field although its not of date type? I want to sort asc and desc but I am guessing I need to cast on the fly or something? I'd like to use this convention going forward (I know its stupid) It was done this way as an easy way out

INSERT Non-MySQL Date Into MySQL Date Field
I have a non-MySQL database that has a date value that is an integer value (4 byte unsigned integer representing the number of days since 1904, ie 01/01/2000 = 35063)

Do I need to re-format this to use in an SQL to INSERT it into a MySQL table?

If I do need to reformat it, what is the best format to use.

Date Field In Mysql Query
Just after a bit of advice or guidance on the best way to go about an issue i have..

At present i have a simple user form with drop down lists for the user to enter a date. (eg. 1,2,3, - Jan, feb, mar, - 2006,2007 etc..) as -$day -$month- $year...

All other input areas on this form are text and will go in a Varchar fields so will not be an issue...

My query is what is the best way to handle this date, and how..?
I undestand Mysql needs the date as yyyy mm dd to be in a date field..
Im just really unsure of how to put my format date in to a date field on the table and query it out so it displays in my format again..

Ive done quite of bit of searching for this and seen many suggestions including using INT and Varchar fields so i am really confused as the best and correct way to go about this..

Sorting DATE Field By Year
I've have a field ('gamedate') in a table ('games') that is in DATE format, but what I can't figure out is how to use the year as part of a search form option. I'm hoping to get a drop down menu that is a list of years, that when selected will bring back just the records from that year, or in the future, use BETWEEN to find all records from a set group of dates.

Is there a step I'm missing to "extract" the "YYYY" part of the DATE field before using it? Or maybe there's a wildcard that needs to be stated to get "MM" and "DD" to equal "any"?

Retrieve Information Using Date Field?
I have a table called Event. In the table I have a date field. I want to Select specific fields and retrive events from the begining of this month to the end of february. In other words, 3 months span.

This is what I have,

SELECT EventDate, EventID, EventTitle, Event, DeptCode
FROM Events
WHERE DeptCode == '2'
ORDER BY EventDate ASC

Now my question is how do I ask for events in 3 monts span.

Comparing DATETIME Field With Date...
I'd like to make query which compares two dates - one from database, ang second in php (date("Y-m-d H:i:s")). I'd like to make query which gives me back those rows where the date from db is more than three days older than current date.

Empty 'Date' Field In Database ?
I have field in a table which is of type 'date'

If the particular record has no date listed, how can I test for an empty date ?

If RS("ShipDate") > 0 then

or

If RS("ShipDate") = 0 then (or "", or ##)

Not Able Give Default Value For DATE Field
I would like to specify a default value for "DATE" datatype as a system
date. But i could find from the help tutorial, "default value cannot be
added for a DATE datatype". But i could specify in Oracle. I have found
below statment from the help documents.

"The DEFAULT value clause in a data type specification indicates a default
value for a column. With one exception, the default value must be a
constant; it cannot be a function or an expression. This means, for
example, that you cannot set the default for a date column to be the value
of a function such as NOW() or CURRENT_DATE. The exception is that you can
specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column."

Why cant we use a functions or variable values in date? Is this the
limitation or didnot explore this functionality yet?
Is this limitation applicable for only DATE type or any other datatypes?

I have such a situation to use this "default" feature for a date column
with value as system date.

is there a way to do that?

Optimizing A Deletion By Date Field?
i have a field that is described as follows:

Field | Type | Null | Key | Default | Extra
------------------------------------------------------------------
Dt_From | date | YES | MUL | NULL |

I am using JDBC to do some maintenance on this DB. The first thing I need to do is
delete all rows where the 'From' date is before a specified date. I am getting, what I believe to be, horrible results in terms of speed. This is an indexed field, as
shown above. Here is the query string I am using:

"DELETE from allrows where Dt_From <= 20050404;"

My test DB here has just shy of 10 million rows. This query should delete around 1.8 million of those.

I launched my test almost 2 hours ago and it still hasn't finished. Is that to be expected? I would think that with an indexed field I should get much quicker results. Am I wrong here? Is there something I'm missing?

Date Field Saved As 0000-00-00

I have a table with a field type date and null is set to yes. How can I insert a blank value without it being save as 0000-00-00.