Link to home
Start Free TrialLog in
Avatar of hmstechsupport
hmstechsupport

asked on

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

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
Avatar of dwkor
dwkor
Flag of United States of America image

Try to use: '2009-05-29T16:00:00.000'
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of hmstechsupport
hmstechsupport

ASKER

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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.