Inserting Dates into SQL Server table

[rob...@ihug.co.nz]

Why the hell can't I get a common date format across my NT/ASP/SQL Server
platform??? ARGH!!!!!!

My problem is this. I have set my NT server to Regional settings (dd/mm/yy -
New Zealand, FYI). The SQL Server tables are set up and when I enter a date
into a table it correctly formats the date to dd/mm/yy even if I put it in as
mm/dd/yy.
Now, ASP comes in a stuffs it all up when I try to add or modify these dates
with ASP.

I have an Add page (add.asp) and a Modify page (modify.asp).
On the add page, I have the ActiveX Calendar control that puts the selected
date into a hidden form field. This in turn is used as part of the "insert
into..." SQL command.

Here is an example of a successfult query:
insert into Vacancies values ('Support','Wellington','General Lacky','Person
required to perform dull, boring tasks that nobody else will
do.','07/09/98','Jan Blair');

As you can see, the date is correctly entered as 07/09/98 (7 Sept. 98).
However, when I go to list the entry on a subsequent page, I get:

Support Wellington General Lacky Person required to perform dull, boring tasks
that nobody else will do. 09/07/98 Jan Blair

The sodding date has been reversed!!!. This driving me mental!!!

Now, if I wish to modify this entry, I click a link and modify.asp loads up
that record into a form for me to edit. You guessed it, the date is 09/07/98.

Does anyone have any ideas where to start checking for something wrong?

Thanks!

Please CC replies to my email.

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum

[ipo...@dial.pipex.com]

In article <6t7jhg$gqg$1...@nnrp1.dejanews.com>,

The easiest way is to add an additional language to the SQL Server. Here is my
script to do it (run in the master database with the SQL option Allow Updates
set to 1 for duration of query):

insert into syslanguages values (1, 'dmy', 7, 1, 'uk_english',
'BritishEnglish',
'January,February,March,April,May,June,July,August,September,October,November
,De cember', 'Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec',
'Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday')

Then, when setting up a DSN (you MUST use a FileDSN or a version of ODBC which
queries the SQL Server to find installed languages), you select uk_english as
the default language.

Thereafter, no further translation of dmy <->mdy is necessary.

--
Ian Posner
MindQuest Solutions Ltd
ipo...@dial.pipex.com