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 Comments2 Solutions1234 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
Join our community to see this answer!
Unlock 2 Answers and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros