Need help: Session.LCID in ASP page causing database date/time issue

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View


Having a problem on an ASP page when its inserting information into a MS SQL
2000 database...

In my shopping cart I have 3 columns:
"datecreated" this is smalldatetime and records the date the shopping cart
was created
"datesent" this is smalldatetime and records the date the order was actually
"ordersent" this is a bit and flags whether or not the order has been sent
or not

These columns are mainly used for maintenance/upkeep or to allow the
customer to recall past orders...   when a cart is created "datecreated" is
set with the current time/date... when the order is sent "datesent" is set
with the current time/date and "ordersent" is set to TRUE (1)

At the end of the day when maintenance scripts run all carts that were
created more than 24hrs ago and not sent are deleted and all carts that are
created more than 30 days ago that were sent are moved to the archived
orders database...

This is done because carts that are more than 24hrs old are pretty much
useless and archived orders are kept in the event a customer wants to come
back and say "what did I order in the past" or "I want to resend a past
order" and they will see orders from the last 30 days... if they want to go
back further then the archive database is accessed for old orders...

The problem:

Everything works fine for restaurants in the US, but we're doing the rollout
ot the UK now and thats causing some problems...

To handle the different date format and the GBP symbol I have set the
"SESSION.LCID=2057" in all web pages.

Things worked fine when we started setting up restaurants back on December
1st and running tests... but as soon as we hit Tuesday this week (December
13th) problems started coming up.

The problem is under the US LCID dates are written as:  MM/DD/YYYY...  but
under the UK LCID dates are written as:  DD/MM/YYYY and so though it looked
like things were working, as soon as we hit December 13th we hit the problem
where there is no 13th month so SQL errors started coming up as "can't
convert CHAR format to SMALLDATETIME"

I took a look around SQL and can't seem to find anything where I can set the
date/time format on the one database to use a different date/time format...
and I don't want to set the SESSION.LCID back to US before each write that
involved a date and then back to UK once its done (it'd probably work, but
seems like it would be an innefficient way of doing things)... and I don't
want to convert the dates in the DB columns to CHAR format because that
might bugger up the date/time sorting.

So looking to see if there are any suggestions out there.. I Googled for
ideas, but didn't really come up with anything



Re: Need help: Session.LCID in ASP page causing database date/time issue

And lo, Auggie didst speak in alt.www.webmaster:

Quoted text here. Click to load it


Quoted text here. Click to load it

I store all my dates as UNIX timestamps.  Sure most of the processing  
needs to be done before and after the query to delimit them and return  
them to an understandable format, but an integer representation of the  
date is easy to order and offset, with none of the disadvantages of  
varying date formats around the world.

If you need the program to display a new date format, just change the  
function which translates it from timestamp to string.  The data in the  
table remains the same.


The technical axiom that nothing is impossible sinisterly implies the  
pitfall corollary that nothing is ridiculous.
- - Orca Search: Full-featured spider  
and site-search engine

Re: Need help: Session.LCID in ASP page causing database date/time issue

Writing in news:alt.www.webmaster
 From the safety of the  cafeteria

Quoted text here. Click to load it

How do you do?

Quoted text here. Click to load it

I'd guess you're setting the dates in ASP.  Have you considered letting  
SQL Server create them?

William Tasso

Save the drama
for your Mama.

Re: Need help: Session.LCID in ASP page causing database date/time issue

I used to have numerous problems with dates in SQL server not marrying up
properly with front end applications - mainly Access and Visual Basic. I now
store all dates in International Date format (YYYY-MM-DD) and never have a
problem - means a little bit of extra work on formatting at the front end
but I think that's a better place to handle it, rather than running the risk
of inconsistent records in the database itself.

Site Timeline