By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,041 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Datetime condition in 'where' - international problem

P: n/a
I use datetime condition in sql-query. For example:

select something from sometable
where date between '06/15/04 00:00:00' and '06/15/04 23:59:59'

and result on two synchronized servers is different.

In USA server result contains where date equal '06/14/04' only,
but Russian server gives where date equal '06/15/04'.

I read mysql online book article 'Writing International Transact-SQL Statements'
and not found any recommendations how solve this problem.

Thanks for any help.
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Always format date-time strings in the ISO format (equivalent to CONVERT
style 126), which is valid under any regional settings.

Also it's better to avoid using BETWEEN when trying to compare ranges of
dates with times other than midnight. The literal string '06/15/04 23:59:59'
will actually be cast as the date 2004-06-16T00:00:00, which I don't think
is what you intended. For clarity use >= and < instead. For example:

....
WHERE date_col >= '2004-06-15T00:00:00'
date_col < '2004-06-16T00:00:00'

If you just want to specify dates without times then use YYYYMMDD format.
Don't use any punctuation between the date parts.

....
WHERE date_col >= '20040615'
date_col < '20040616'

--
David Portas
SQL Server MVP
--
Jul 20 '05 #2

P: n/a
seigo (se******@yahoo.com) writes:
I use datetime condition in sql-query. For example:

select something from sometable
where date between '06/15/04 00:00:00' and '06/15/04 23:59:59'

and result on two synchronized servers is different.

In USA server result contains where date equal '06/14/04' only,
but Russian server gives where date equal '06/15/04'.

I read mysql online book article 'Writing International Transact-SQL
Statements' and not found any recommendations how solve this problem.


The topic could give more background, but it does give some quite
precis advice.

The story is that depending on the commands SET LANGUAGE and SET DATEFORMAT
SQL Server interprets date literals differently. For instance:

SET LANGUAGE us_english
SELECT convert(datetime, '06/15/04')
go
SET LANGUAGE Russian
SELECT convert(datetime, '06/15/04')
go
SET DATEFORMAT myd
SELECT convert(datetime, '06/15/04')

There are two formats that are independent of these settings: YYYYMMDD
(withou delimiters that is) and YYYY-MM-DDTHH:MM:SS[.sss] where T
stands for itself.

Also the condition you have is better written as

date >= '20040615' AND date < '20040616'

The way you've written you lose data from the last second of the day.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.