mystery date formats

Hi All:

Something really strange is occurring here:

When I execute the following SQL string against an Access 2000 database:

'UPDATE mytable SET firstname = 'John', Datestart = '3/10/2006' WHERE ID =
1'

and later run:

'SELECT firstname, datestart FROM mytable'

then it turns out that the Access has stored the date as '10/03/2006'!

Why?

Any advice would greatly appreciated.

Thanks

TB

Re: mystery date formats

Because you forgot to use an UNAMBIGUOUS, STANDARD date format in your
UPDATE statement. I don't even know if you meant March 10 or October 3, so
how do you expect Access to know? (Not that I'm smarter than Access, but
like the animals, I think perception-based logic is one thing that separates
us.)

Have a read here:
http://www.aspfaq.com/2023

--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.




"TB" wrote in message
news:usMFLvxLFHA.508@TK2MSFTNGP12.phx.gbl...
> Hi All:
>
> Something really strange is occurring here:
>
> When I execute the following SQL string against an Access 2000 database:
>
> 'UPDATE mytable SET firstname = 'John', Datestart = '3/10/2006' WHERE ID =
> 1'
>
> and later run:
>
> 'SELECT firstname, datestart FROM mytable'
>
> then it turns out that the Access has stored the date as '10/03/2006'!
>
> Why?
>
> Any advice would greatly appreciated.
>
> Thanks
>
> TB
>
>

Re: mystery date formats

"TB" wrote in message
news:usMFLvxLFHA.508@TK2MSFTNGP12.phx.gbl...
> Hi All:
>
> Something really strange is occurring here:
>
> When I execute the following SQL string against an Access 2000 database:
>
> 'UPDATE mytable SET firstname = 'John', Datestart = '3/10/2006' WHERE ID =
> 1'
>
> and later run:
>
> 'SELECT firstname, datestart FROM mytable'
>
> then it turns out that the Access has stored the date as '10/03/2006'!
>
> Why?
>
> Any advice would greatly appreciated.
>
> Thanks
>
> TB
>
http://aspfaq.com/show.asp?id=2260

Re: mystery date formats

Thanks a lot. You just saved my day (or rather: my night).

TB
"Aaron [SQL Server MVP]" wrote in message
news:%23VGLk2xLFHA.3340@TK2MSFTNGP14.phx.gbl...
> Because you forgot to use an UNAMBIGUOUS, STANDARD date format in your
> UPDATE statement. I don't even know if you meant March 10 or October 3,
> so
> how do you expect Access to know? (Not that I'm smarter than Access, but
> like the animals, I think perception-based logic is one thing that
> separates
> us.)
>
> Have a read here:
> http://www.aspfaq.com/2023
>
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
>
>
> "TB" wrote in message
> news:usMFLvxLFHA.508@TK2MSFTNGP12.phx.gbl...
>> Hi All:
>>
>> Something really strange is occurring here:
>>
>> When I execute the following SQL string against an Access 2000 database:
>>
>> 'UPDATE mytable SET firstname = 'John', Datestart = '3/10/2006' WHERE ID
>> =
>> 1'
>>
>> and later run:
>>
>> 'SELECT firstname, datestart FROM mytable'
>>
>> then it turns out that the Access has stored the date as '10/03/2006'!
>>
>> Why?
>>
>> Any advice would greatly appreciated.
>>
>> Thanks
>>
>> TB
>>
>>
>
>

Re: mystery date formats

Thanks a lot. You just saved my day (or rather: my night).

TB
"Chris Hohmann" wrote in message
news:OXMPt3xLFHA.1956@TK2MSFTNGP15.phx.gbl...
> "TB" wrote in message
> news:usMFLvxLFHA.508@TK2MSFTNGP12.phx.gbl...
>> Hi All:
>>
>> Something really strange is occurring here:
>>
>> When I execute the following SQL string against an Access 2000 database:
>>
>> 'UPDATE mytable SET firstname = 'John', Datestart = '3/10/2006' WHERE ID
>> = 1'
>>
>> and later run:
>>
>> 'SELECT firstname, datestart FROM mytable'
>>
>> then it turns out that the Access has stored the date as '10/03/2006'!
>>
>> Why?
>>
>> Any advice would greatly appreciated.
>>
>> Thanks
>>
>> TB
>>
> http://aspfaq.com/show.asp?id=2260
>