I doubt I'll get an answer, but yyyy-??-?? don't work in SQL Server so why
do you keep telling people to use it?

select cast( '2007-04-01' as datetime )
select cast( '2007-04-01T00:00:00' as datetime )

Here in the UK in cloudy Harpenden with the default connection settings
gives these results....

-----------------------
2007-01-04 00:00:00.000

(1 row(s) affected)


-----------------------
2007-04-01 00:00:00.000

(1 row(s) affected)

Why do you keep telling people to use yyyy-mm-dd when you have been told
several times of this behaviour?

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]


"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote

On Aug 23, 5:13 pm, "rdraider" <rdrai... (AT) sbcglobal (DOT) net> wrote:

What the user wants and what the Db needs are two different things.
You must store the date as a standard and THEN, formatting the output,
you can write whatever you want.

But NOT in the DB

P

Unfortunately I have no control over the format of the data in SQL nor the
application. I am however expected to please the users :-/
Time for a new job. I've always wanted to be a pilot...

Thanks for all the help.

RD

"Piero 'Giops' Giorgi" <giorgi.piero (AT) gmail (DOT) com> wrote

"rdraider" <rdraider (AT) sbcglobal (DOT) net> wrote in
news:5xIzi.1664$vU4.326 (AT) nlpi068 (DOT) nbdc.sbc.com:

Well, if you can't control it in SQL, *or* in the application,
then you're well and truly fucked.

rdraider (rdraider (AT) sbcglobal (DOT) net) writes:
Yes, that's the point with returning the date as datetime. The client
settings takes full control, so that each user can get the date the way
he prefers.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

That's funny -- here in sunny Austin I get the right answer with my
connection settings. Moral to the story "Think globally (i.e. ISO)
and act locally (i.e fix your dialect and proprietary setting)" to
paraphrase the Greens.

--CELKO-- (jcelko212 (AT) earthlink (DOT) net) writes:
And think global means "if it works in where I am, it works"? I know
that it's hard to be humble if you are from Texas, but this is getting
out of hand.

Fact is, in SQL 2005 the format YYYY-MM-DD is subject to local settings
and cannot be trusted, ISO or not. Of course, it was a design made in
California, so I can understand that it's hard for you to swallow.

To soothe you, these two formats are safe:

YYYY-MM-DDThh:mm:ss
YYYY-MM-DDZ

And it seems that when you work with the new date and time data types in
SQL 2000, YYYY-MM-DD is always correctly interpreted.




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

I see, with YOUR regional settings it works - well that explains it all.

The rest of the planet should adopt Texas Regional settings I guess then.

Perhaps you should alter your statement about regional settings and people
letting the client sort it out then...

This issue is a good marker as to just how professional you really are; now
you know that use the date format YYYY-MM-DD cannot be trusted in SQL
Server, will you keep preaching the use of it?

Or will you use the ISO compliant YYYY-MM-DDT00:00:00 or YYYYMMDD instead?

I look forward to seeing that over the coming months.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

On Aug 24, 3:01 pm, "rdraider" <rdrai... (AT) sbcglobal (DOT) net> wrote:

I have my PPL (Private Pilot License) since 1982... :-)
You can't imagine how relaxing it is to go fly after a day in front of
the computer screen.

Only downside... damn expensive!!!

Anyway... do not compromise on the data format in the DB.
VERY wrong.

P