International Dates in SQL

owain · June 12th, 2003, 07:19 AM

Is there a tried and tested way of ensuring that dates will be correctly interpreted by the SQL parser?

For example the date 12th June 2003 can be written as 12/06/2003 or 06/12/2003 depending on whether you are using UK or US format. However, if the user wrote the date in UK format and the database understands US format then the database will think you are asking for 6th December 2003. To throw another spanner in the works, in Germany they use a period (.) as the date delimiter and not a slash (/) or a hyphen (-), so in German 12/06/2003 would not be a date at all.

Now I know you could use something like 12-Jun-2003, however in different languages Jun is not a recognised abbreviation, for example June in Bulgarian is ÃÃÃ¨.

This would all become easy if you knew what date format the database was using, but there is no way of guarantying what format is being used, so it could be UK, US, German, Bulgarian or anything else for that matter.

I have found a way of ensuring the correct date is used in MS Access, Access stores dates as an eight byte double precision number, where the integer part is the date and the decimal part is the time, so for example midnight on the 12th June 2003 would be 37784, and 13:04 on the same date would be 37784.5444444444.

Therefor in SQL you can use a statement like this

Code:

SELECT *
FROM Orders
WHERE OrderDate = 37784

or

Code:

SELECT *
FROM Orders
WHERE OrderDate BETWEEN 37784.375 AND 37784.7083333333

Instead of

Code:

SELECT *
FROM Orders
WHERE OrderDate = #12-Jun-2003#

and

Code:

SELECT *
FROM Orders
WHERE OrderDate BETWEEN #12-Jun-2003 09:00# AND #12-Jun-2003 17:00#

However does this work in all dialects of SQL on other DBMS's? If not, is there another way of ensuring that the correct date is used?

Regards
Owain Williams

pgtips · June 12th, 2003, 08:56 AM

I would say that your method of using the numeric equivalent couldn't be guaranteed to work on all RDBMSs since even if 2 different RDBMSs internally represent a date by this numeric method, they might use a different base date, so the same date would be represented by a different number in each RDBMS.

Personally in applications I never pass dates to the db as strings, I always use a Date variable and add that as a parameter to a stored procedure.

If I'm messing about with SQL in something like SQL Server's Query Analyzer I always use the ISO date format yyyy-mm-dd along with an explicit conversion to datetime which specifies that I've used that format, e.g
select * from table where datefield = convert(datetime, '2003-06-12', 111)

Ken has some stuff about dates and Access on his site www.adopenstatic.com

hth
Phil

defiant · June 13th, 2003, 03:12 PM

in sql server you can use a string like 'yyyymmdd' (ex '20030613' for 13 june 2003) and it qill be interpreted the same no matter the locale settings.

ex:

if @some_date_var > '20010101'
...

defiant