Date formats

I'm running the same insert 'insert into jftemp values ('2006/09/11')' on 2
different servers, one saves this date as 11th Sep the other saves it as 9th
Nov. Both servers have the same regional settings, language settings, defaul
t
language - both are SQL Server 2000 (SP4). Can someone suggest why the dates
are being stored differently?
thanks, JohnJohn - have a look at the default languages of the Logins you are using
(sp_helplogins) - are they different on each server?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||that's it! Thanks very much Paul, it's obvious when you know the answer!
"Paul Ibison" wrote:

> John - have a look at the default languages of the Logins you are using
> (sp_helplogins) - are they different on each server?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||On Fri, 25 Aug 2006 02:20:01 -0700, John wrote:

>I'm running the same insert 'insert into jftemp values ('2006/09/11')' on 2
>different servers, one saves this date as 11th Sep the other saves it as 9t
h
>Nov. Both servers have the same regional settings, language settings, defau
lt
>language - both are SQL Server 2000 (SP4). Can someone suggest why the date
s
>are being stored differently?
>thanks, John
Hi John,
They are not stored differently, the string constant is interpreted in
different ways.
To prevent this in the future, always use one of the unambiguous
formats. For date only, use yyyymmdd (i.e. '20060825' for 11th Sep). For
date plus time, use yyyy-mm-ddThh:mm:ss, optionally followed by .mmm for
milliseconds (i.e. '2006-08-25T02:20:01' or '2006-08-25T02:20:01.373').
Hugo Kornelis, SQL Server MVP|||>> (i.e. '20060825' for 11th Sep) <<
This may be non-ambiguous, but it's also very non-intuitive.
:-)
HTH
Kalen Delaney, SQL Server MVP
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:t1uue21q4qa741hvpb898lnv55ennsehs3@.
4ax.com...
> On Fri, 25 Aug 2006 02:20:01 -0700, John wrote:
>
> Hi John,
> They are not stored differently, the string constant is interpreted in
> different ways.
> To prevent this in the future, always use one of the unambiguous
> formats. For date only, use yyyymmdd (i.e. '20060825' for 11th Sep). For
> date plus time, use yyyy-mm-ddThh:mm:ss, optionally followed by .mmm for
> milliseconds (i.e. '2006-08-25T02:20:01' or '2006-08-25T02:20:01.373').
> --
> Hugo Kornelis, SQL Server MVP|||On Mon, 28 Aug 2006 15:14:35 -0700, Kalen Delaney wrote:

>This may be non-ambiguous, but it's also very non-intuitive.
>:-)
Ouch!
Good catch, Kalen.
Hugo Kornelis, SQL Server MVP