Hi guys,

I'm a bit of a newbie to SQL so i was hoping someone could help!
The server is set to uk date format. However if i run this code:

select * from events where StartDate < 30/06/2005

I get nothing when i should get results. Thinking something is set to american date format somewhere i tried

select * from events where StartDate < 06/30/2005

Still nothing. However if i change it to:

select * from events where StartDate > 30/06/2005

All results are returned from the database including the ones with a date less than that!!? I get the same results whether i use Query Analyser or Enterprise Manager to run these queries.

Any help greatly appreciated

Steve

Try quoting your dates, as, e.g.:

DateTime constant values are represented as character strings whose value is interpreted according to the current language settings.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

hi StevieB,

whenever u r comparing Date field in SQL Server, u shud always use yyyy/MM/dd format

e.g., select * from events where StartDate < '2005/01/31'

it works well for all formats

Ashu

Hi Jeff,

Thanks for your help:

select * from events where StartDate < '30/06/2005'

didn't work, but

select * from events where StartDate < '06/30/2005' did!!

This is really confusing now - the front end of the website interrogates the databases using UK date format and works fine but when i use enterprise manager i have to use American!!?

I checked the servers regional settings and everythings UK??

Hi Ashu,

Thanks a lot for that - i'll know for next time!

Cheers
Steve

IMO, it is better to use the ISO format for datetime character strings. This format takes the form:
(Note that constant "T" in there if you include a time).

This is unambiguous no matter what your language settings are on the server, in the tools you are using to execute a query and/or in the connection used to access the server (which, as you've found out, are not necessarily consistent with one another). Other formats may work, but are not necessarily guaranteed to give consistent results from language setting to setting.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

The ANSI date format 'yyyy.mm.dd' also seems to avoid the ambiguities of mm/dd/yyyy vs. dd/mm/yyyy.

Rand