Access to MySQL

------=_NextPart_000_0113_01CB9D43.03F61660
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable

I have to move the back-end of an Access application to MySQL, and =
Iâ€=99ve run into one issue that I havenâ€=99t been able to =
solve yet.

=20

The Access database stores dates as text in a =
â€=9Cyyyy/mm/ddâ€=9D format. The problem is that the default =
value is a formula that generates the current date, formatted as text. =
In Access, it looks like

=20

'=3DFormat$(Now(),\"yyyy/mm/dd\")'

=20

This construct is used throughout the table definitions.

=20

Is there any alternative to setting the default to something else (NULL, =
for example) and moving the â€=9Cdefaultâ€=9D into the =
application code? That would be a significant PITA.

=20

Regards,

=20

Jerry Schwartz

Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032

=20

860.674.8796 / FAX: 860.674.8341

E-mail: jerry@gii.co.jp=20

Web site: www.the-infoshop.com

=20


------=_NextPart_000_0113_01CB9D43.03F61660--

Re: Access to MySQL

--Apple-Mail-1-744199684
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=windows-1252

On 17/12/2010, at 9:02 AM, Jerry Schwartz wrote:

> I have to move the back-end of an Access application to MySQL, and =
I=92ve run into one issue that I haven=92t been able to solve yet.
>=20
> The Access database stores dates as text in a =93yyyy/mm/dd=94 format. =
The problem is that the default value is a formula that generates the =
current date, formatted as text. In Access, it looks like
>=20
> '=3DFormat$(Now(),\"yyyy/mm/dd\")'
>=20
> This construct is used throughout the table definitions.
>=20
> Is there any alternative to setting the default to something else =
(NULL, for example) and moving the =93default=94 into the application =
code? That would be a significant PITA.

If a 32-bit date range is enough, then you can use the timestamp data =
type. That supports having the current time as the default value. See =
also http://dev.mysql.com/doc/refman/5.1/en/timestamp.html

Regards,
Jesper=

--Apple-Mail-1-744199684--

Re: Access to MySQL

What about this?

date_format(now(), "%Y/%m/%d")

--
João Cândido de Souza Neto

""Jerry Schwartz"" escreveu na mensagem
news:011201cb9d6c$eccc1e60$c6645b20$@co.jp...
I have to move the back-end of an Access application to MySQL, and I've run
into one issue that I haven't been able to solve yet.



The Access database stores dates as text in a "yyyy/mm/dd" format. The
problem is that the default value is a formula that generates the current
date, formatted as text. In Access, it looks like



'=Format$(Now(),\"yyyy/mm/dd\")'



This construct is used throughout the table definitions.



Is there any alternative to setting the default to something else (NULL, for
example) and moving the "default" into the application code? That would be a
significant PITA.



Regards,



Jerry Schwartz

Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032



860.674.8796 / FAX: 860.674.8341

E-mail: jerry@gii.co.jp

Web site: www.the-infoshop.com






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Access to MySQL

>-----Original Message-----
>From: Jesper Wisborg Krogh [mailto:mysql@wisborg.dk]
>Sent: Friday, December 17, 2010 6:07 AM
>To: Jerry Schwartz
>Cc: 'MySQL'
>Subject: Re: Access to MySQL
>
>On 17/12/2010, at 9:02 AM, Jerry Schwartz wrote:
>
>> I have to move the back-end of an Access application to MySQL, and I've run
>into one issue that I haven't been able to solve yet.
>>
>> The Access database stores dates as text in a "yyyy/mm/dd" format. The
>problem is that the default value is a formula that generates the current
>date,
>formatted as text. In Access, it looks like
>>
>> '=Format$(Now(),\"yyyy/mm/dd\")'
>>
>> This construct is used throughout the table definitions.
>>
>> Is there any alternative to setting the default to something else (NULL,
>> for
>example) and moving the "default" into the application code? That would be a
>significant PITA.
>
>If a 32-bit date range is enough, then you can use the timestamp data type.
>That supports having the current time as the default value. See also
>http://dev.mysql.com/doc/refman/5.1/en/timestamp.html
>
[JS] Unfortunately, I have to keep that field as a text field.

Also, a timestamp would change every time a record is updated and you can only
have one per record.

Thanks for trying.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com



>Regards,
>Jesper




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Access to MySQL

>-----Original Message-----
>From: João Cândido de Souza Neto [mailto:joao@consultorweb.cnt.br]
>Sent: Friday, December 17, 2010 6:11 AM
>To: mysql@lists.mysql.com
>Subject: Re: Access to MySQL
>
>What about this?
>
>date_format(now(), "%Y/%m/%d")
>
[JS] I don't think you can use anything but a constant as a default value.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com



>--
>João Cândido de Souza Neto
>
>""Jerry Schwartz"" escreveu na mensagem
>news:011201cb9d6c$eccc1e60$c6645b20$@co.jp...
>I have to move the back-end of an Access application to MySQL, and I've run
>into one issue that I haven't been able to solve yet.
>
>
>
>The Access database stores dates as text in a "yyyy/mm/dd" format. The
>problem is that the default value is a formula that generates the current
>date, formatted as text. In Access, it looks like
>
>
>
>'=Format$(Now(),\"yyyy/mm/dd\")'
>
>
>
>This construct is used throughout the table definitions.
>
>
>
>Is there any alternative to setting the default to something else (NULL, for
>example) and moving the "default" into the application code? That would be a
>significant PITA.
>
>
>
>Regards,
>
>
>
>Jerry Schwartz
>
>Global Information Incorporated
>
>195 Farmington Ave.
>
>Farmington, CT 06032
>
>
>
>860.674.8796 / FAX: 860.674.8341
>
>E-mail: jerry@gii.co.jp
>
>Web site: www.the-infoshop.com
>
>
>
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jerry@gii.co.jp





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Access to MySQL

Hi Jerry,

On 12/17/2010 09:34, Jerry Schwartz wrote:
>> -----Original Message-----
>> From: Jo�o C�ndido de Souza Neto [mailto:joao@consultorweb.cnt.br]
>> Sent: Friday, December 17, 2010 6:11 AM
>> To: mysql@lists.mysql.com
>> Subject: Re: Access to MySQL
>>
>> What about this?
>>
>> date_format(now(), "%Y/%m/%d")
>>
> [JS] I don't think you can use anything but a constant as a default value.
>

You are correct with one exception that was already mentioned earlier:
the TIMESTAMP storage type.

from http://dev.mysql.com/doc/refman/5.1/en/create-table.html
###
The DEFAULT clause specifies 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. See Section 10.3.1.1, “TIMESTAMP
Properties”.
###

However, nothing says you can't use a function or other computation in a
TRIGGER to set the default value to an empty column of a new row to
whatever you wanted it to be.

http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html

I know it's a workaround but it will keep the default value management
out of your application and inside the database.

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Access to MySQL

>-----Original Message-----
>From: Shawn Green (MySQL) [mailto:shawn.l.green@oracle.com]
>Sent: Friday, December 17, 2010 10:35 AM
>To: Jerry Schwartz
>Cc: joao@consultorweb.cnt.br; mysql@lists.mysql.com
>Subject: Re: Access to MySQL
>
>Hi Jerry,
>
>On 12/17/2010 09:34, Jerry Schwartz wrote:
>>> -----Original Message-----
>>> From: Jo?o C?ndido de Souza Neto [mailto:joao@consultorweb.cnt.br]
>>> Sent: Friday, December 17, 2010 6:11 AM
>>> To: mysql@lists.mysql.com
>>> Subject: Re: Access to MySQL
>>>
>>> What about this?
>>>
>>> date_format(now(), "%Y/%m/%d")
>>>
>> [JS] I don't think you can use anything but a constant as a default value.
>>
>
>You are correct with one exception that was already mentioned earlier:
>the TIMESTAMP storage type.
>
>from http://dev.mysql.com/doc/refman/5.1/en/create-table.html
>###
>The DEFAULT clause specifies 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. See Section 10.3.1.1, "TIMESTAMP
>Properties".
>###
>
>However, nothing says you can't use a function or other computation in a
>TRIGGER to set the default value to an empty column of a new row to
>whatever you wanted it to be.
>
>http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html
>
>I know it's a workaround but it will keep the default value management
>out of your application and inside the database.
>
[JS] Thanks. I did have that in the back of my mind, but to be honest I never
used a trigger.

I'll have to think about this.


Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com



>--
>Shawn Green
>MySQL Principal Technical Support Engineer
>Oracle USA, Inc.
>Office: Blountville, TN
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jerry@gii.co.jp





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org