How can I select data from a datetime field with a "Modern_Spanish_CI

I have an application that is running in several countries and the most recent one is Chile. The issue I have is that when I go to select from the table I seem to need to know exactly what the date format is or I do not get the results. To worsen my issue, the application runs in many countries and I need a SINGLE way to SELECT by a specific date and also INSERT into this table the correct dates as part of another process.

I restored a copy of the SQL 2005 database from my client which has it's collation set to "Modern_Spanish_CI_AS" and I get the following results back.

This returns a result. The selected value is exactly as I see it in the data.
SELECT * FROM mytable WHERE start_date = '2009-05-29 16:00:00.000'
These 3 do not and I have tried a host of other formats. When the format is wrong I get a conversion error, so SQL accepts these as dates, but still does not give me the data.
SELECT * FROM mytable WHERE start_date = '2009/05/29'
SELECT * FROM mytable WHERE start_date = '05/29/2009'
SELECT * FROM mytable WHERE start_date = '29-MAY-2009'

Executing all three of these return a result from my sample database which has a collation of "SQL_Latin1_General_CP1_CI_AS"
SELECT * FROM mytable WHERE start_date='2000-04-22 00:00:00.000'
SELECT * FROM mytable WHERE start_date='22-Apr-2000'
SELECT * FROM mytable WHERE start_date='04-22-2000'

I don't mind doing the regional formating in the applicaiton as long as I know it will work across all regions, but I can't seem to find one yet that work.

So....Does anyone know how I can generate a sure fire date format that will work across all regions?
What other format could I use to select the date from the Spanish database?

Thanks in Advance

Try to use: '2009-05-29T16:00:00.000'

in regards to date/time, you have 2 options:
* ensure you use ONLY explicit data type conversions (CONVERT ... )
* try to rely on the login's language setting, which defines the implicit data type conversion

see here for some details

http://www.experts-exchange.com/articles/Database/Miscellaneous/DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html

Well, if I understand this correctly - http://msdn.microsoft.com/en-us/library/ms190977%28SQL.90%29.aspx - ISO8601 format yyyy-mm-ddThh:mm:ss[.mmm] would also work

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Thanks dwkor, but I cannot use the time as I do not ever know the time. It is inserted by another application and could be any time of the day. I need to strip that part out.
The same is true angellll as when I try it with the Convert in converts to '2009-05-29 00:00:00.000' and I lose the time and it does not return the result.

another part which is important, also mentioned in the article:

http://www.experts-exchange.com/articles/Database/Miscellaneous/DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html

you rarely use field = datevalue, but usually:
WHERE date >= datevalue
AND date < dateadd(day, 1, datevalue)

In the end this seems to work for me. It's a little sloppy and I have to seperate the SQL based on the database (ie) SQL Server or Oracle, but that won't be too bad and it works!.

This works for both cases under Oracle
SELECT * FROM rate WHERE TO_CHAR(datefield,'YYYY') = 2000 AND TO_CHAR(datefield,'MM') = 04 AND TO_CHAR(datefield,'DD') = 17

This works for both cases under SQL Server
SELECT * FROM rate WHERE
DATEPART(YY, [datefield]) = 2000 AND
DATEPART(MM, [datefield]) = 04 AND
DATEPART(DD, [datefield]) = 15

Thanks to both of you. I banged my head for hours when I should have gone right to this source.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 13

dwkorCommented: 2010-03-24

LVL 143

Guy Hengel [angelIII / a3]Billing EngineerCommented: 2010-03-24

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

hmstechsupportAuthor Commented: 2010-03-24

How can I select data from a datetime field with a "Modern_Spanish_CI_AS" Collation

Who is Participating?