UK date problems (again! but slighty different!)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

UK date problems (again! but slighty different!)

Author

Topic

jackstow
Posting Yak Master

160 Posts

Posted - 2002-01-11 : 10:43:49

This is the setup;

Windows 2000 Server, SQL Server 2000 set to British English, ASP 3, global.asa with

*******
sub Session_OnStart
'converts local system to be English(United Kingdom)
Session.LCID = 2057.
******

to explicitly set the system to UK date and currency formats.

This is the problem;

Set a date in a table as a default value using getdate() and it is UK format (dd/mm/yyyy) - OK
Insert a date from QA and it is UK format - OK
Write a date from the db to the screen and it is UK - OK
Response.Write NOW and it is UK - OK
Response.Write a variable with a date in it and it is UK - OK
Insert that variable into a table and suddenly it is US format - not OK

IIS seems to be using UK format and so does SQL Server, so is the problem somewhere inbetween (ADO ?)

Any ideas?

Jack

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-11 : 10:56:43

If you always tranfer dates as datetimes or yyyymmdd then you will never have a problem.
Get used to treating presentation as a separate layer, it should not depend on anything else.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

izaltsman
A custom title

1139 Posts

Posted - 2002-01-11 : 10:59:36

We had a similar issue just yesterday... It only appears to happen with Win2k/IIS5 The following seems to work:

Go to webserver's registry, and set

HKEY_USERS\.DEFAULT\Control
Panel\International\"sShortDate"="dd/MM/yyyy"

Edited by - izaltsman on 01/11/2002 11:01:02

jackstow
Posting Yak Master

160 Posts

Posted - 2002-01-30 : 06:47:32

I have a question about this. If you are storing dates as yyyymmdd in a field, or even across several fields (i.e. one for day, month and year), to avoid any problems/confusion with date formatting, how do you then do ORDER BY on that field, i.e. how do you know 20020129 is more recent than 200010128

Nazim
A custom title

1408 Posts

Posted - 2002-01-30 : 07:31:03

if its a datetime field and you are storing it in yyyymmdd ,sorting will be taken care by itself.

but if you storing across different fields then sorting can be done as ORDER BY YY,MM,DD

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

jackstow
Posting Yak Master

160 Posts

Posted - 2002-01-30 : 07:38:08

Thanks - I didn't realise that order by would reliably cascade like that across several fields.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources