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
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.