Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

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

Avatar of hmstechsupport
hmstechsupport asked on
Microsoft SQL ServerOracle Database
7 Comments1 Solution1234 ViewsLast Modified:
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