Limitations on data for default...
I am trying to change a column specification, a date column, so that the default value is the current date. Is this possible? I just get an error on the change query: alter table Hits modify modify Date date NOT NULL default CURDATE(); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'date NOT NULL default CURDATE()' at line 1 Am I just stupid trying to have a function for a default? And if so, is it possible to get the 'current date' as the default value for a field? TIA! :) -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limitations on data for default...
i believe you'll need to use a timestamp column, which will set to the current date/time at each insert/update. I don't think you can set the date default to a function. You are also using a reserved word Date for your column name without escaping it, i.e. `Date`, also you have two modify's in your SQL...unless thats an email typo. hth Jeff Mike Morton [EMAIL PROTECTED]To: [EMAIL PROTECTED] m cc: Subject: Limitations on data for default... 11/13/2003 10:15 AM I am trying to change a column specification, a date column, so that the default value is the current date. Is this possible? I just get an error on the change query: alter table Hits modify modify Date date NOT NULL default CURDATE(); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'date NOT NULL default CURDATE()' at line 1 Am I just stupid trying to have a function for a default? And if so, is it possible to get the 'current date' as the default value for a field? TIA! :) -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limitations on data for default...
http://www.mysql.com/doc/en/CREATE_TABLE.html A DEFAULT value has to be a constant, it cannot be a function or an expression. ... Default values must be constants. 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. You have to use NOW() or CURRENT_DATE functions in your insert queries instead. or read about the TIMESTAMP type, but I guess that's too much precision since you only wanted the date, not the time. http://www.mysql.com/doc/en/DATETIME.html Mike On Thursday 13 November 2003 16.15, Mike Morton wrote: I am trying to change a column specification, a date column, so that the default value is the current date. Is this possible? I just get an error on the change query: alter table Hits modify modify Date date NOT NULL default CURDATE(); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'date NOT NULL default CURDATE()' at line 1 Am I just stupid trying to have a function for a default? And if so, is it possible to get the 'current date' as the default value for a field? TIA! :) -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- Ongame E-Solutions AB Mikael Fridh Junior Systems Administrator Smedsgränd 3, 753 20 Uppsala, Sweden Mobile: +46 708 17 42 00 Office: +46 18 69 55 00 Fax: +46 18 69 44 11 e-mail: [EMAIL PROTECTED] http://www.ongame.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limitations on data for default...
Mike Morton [EMAIL PROTECTED] wrote: I am trying to change a column specification, a date column, so that the default value is the current date. Is this possible? I just get an error on the change query: alter table Hits modify modify Date date NOT NULL default CURDATE(); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'date NOT NULL default CURDATE()' at line 1 Am I just stupid trying to have a function for a default? And if so, is it possible to get the 'current date' as the default value for a field? Default value cannot be a function. In your case TIMESTAMP column may help you: http://www.mysql.com/doc/en/DATETIME.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limitations on data for default...
Mikael: Thanks - I must have missed that in the create table docs - I did look there first - honest! And you are correct - in that the timestamp is too much precision, and unfortunately due to the thousands of distributed apps that access this DB, I cannot modify all the queries - oh well, guess it is just better to write a cron to modify the bad data :) Thanks for the info! On 11/13/03 10:28 AM, Mikael Fridh [EMAIL PROTECTED] wrote: http://www.mysql.com/doc/en/CREATE_TABLE.html A DEFAULT value has to be a constant, it cannot be a function or an expression. ... Default values must be constants. 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. You have to use NOW() or CURRENT_DATE functions in your insert queries instead. or read about the TIMESTAMP type, but I guess that's too much precision since you only wanted the date, not the time. http://www.mysql.com/doc/en/DATETIME.html Mike On Thursday 13 November 2003 16.15, Mike Morton wrote: I am trying to change a column specification, a date column, so that the default value is the current date. Is this possible? I just get an error on the change query: alter table Hits modify modify Date date NOT NULL default CURDATE(); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'date NOT NULL default CURDATE()' at line 1 Am I just stupid trying to have a function for a default? And if so, is it possible to get the 'current date' as the default value for a field? TIA! :) -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Limitations on data for default...
From: Mikael Fridh [mailto:[EMAIL PROTECTED] or read about the TIMESTAMP type, but I guess that's too much precision since you only wanted the date, not the time. http://www.mysql.com/doc/en/DATETIME.html It's only too much precision when you're selecting the whole field. You can simply select a timestamp as... SELECT DATE_FORMAT(column, '%Y-%m-%d') AS date ...and get a date just fine. -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
