ASP > SQL date problem!?

  • Thread starter Thread starter Jerome
  • Start date Start date
J

Jerome

Hi,

I know this is an old problem and I've already tried to look the
solution up on the web but I didn't find what I need.

So, there's the following situation:

I've got an ASP page with a form, containing two fields that poses problems

1. birthday field
SQL server will only accept it if entered in MM/DD/YYYY. Which is bad
since we're in Europe, people are accustomed to DD/MM/YYYY. I tried to
use LCID but that didn't work either. Funny thing is when the birthday
is entered in MM/DD/YYYY on the page, it later gets displayed as
DD/MM/YYYY in SQL Server!? Which is good though! (It would be better of
course if it could be entered in DD/MM/YYYY and be recorder in SQL as such)

2. field with the current date & time
The ASP page displays it correctly as 02/09/2004 (September 2nd). But
SQL Server saves it as 09/02/2004 (February 9th). And I bet there'll be
an error or September 13th ... Or is there a way to define in SQL Server
something similar to the Default Value: Now() in Access? Then I could
drop this field on the ASP page.

So somehow there's a complete confusion!?

How can I streamline that or where on the Internet could I find more
info about it?

Thank you very much,

Jerome
 
Jerome wrote on 02 sep 2004 in microsoft.public.inetserver.asp.general:
1. birthday field
SQL server will only accept it if entered in MM/DD/YYYY.
Click to expand...

No, the general accepted format is yyyy-mm-dd
Which is bad
since we're in Europe, people are accustomed to DD/MM/YYYY. I tried to
use LCID but that didn't work either. Funny thing is when the birthday
is entered in MM/DD/YYYY on the page, it later gets displayed as
DD/MM/YYYY in SQL Server!?
Click to expand...

SQL server does not "display"
Which is good though! (It would be better
of course if it could be entered in DD/MM/YYYY and be recorder in SQL
as such)
Click to expand...

In a database field with the type date(!!), as you are using here,
the date is stored as a number.
2. field with the current date & time
The ASP page displays it correctly as 02/09/2004 (September 2nd). But
SQL Server saves it as 09/02/2004 (February 9th).
Click to expand...

You should "give" it to the SQL string as yyyy-mm-dd or yyyy/mm/dd
And I bet there'll
be an error or September 13th ... Or is there a way to define in SQL
Server something similar to the Default Value: Now() in Access? Then I
could drop this field on the ASP page.
Click to expand...

Perhaps, I do not use SQL server. now() will probably include the actual
serverlocal time. I suspect that is not what you want.

But if you 'give' it to the db as a ASP string, convert your input to a
yyyy-mm-dd string

If you retrieve it from the databas, and it comes as mm/dd/yyyy, convert
it by ASP(vbs/jscript) to you preferred string.
So somehow there's a complete confusion!?

How can I streamline that or where on the Internet could I find more
info about it?
Click to expand...

Concluding:

Make two functions,
one converting your preferred date string to a yyyy-mm-dd string
and
one converting the database output string to your preferred date string.
 
Jerome said:
Hi,

I know this is an old problem and I've already tried to look the
solution up on the web but I didn't find what I need.

So, there's the following situation:

I've got an ASP page with a form, containing two fields that poses
problems
1. birthday field
SQL server will only accept it if entered in MM/DD/YYYY.
Click to expand...

Wron. SQL Server will also accept the ISO standard date:

YYYYMMDD


Which is bad
since we're in Europe, people are accustomed to DD/MM/YYYY. I tried to
use LCID but that didn't work either. Funny thing is when the birthday
is entered in MM/DD/YYYY on the page, it later gets displayed as
DD/MM/YYYY in SQL Server!?
Click to expand...

No it doesn't. Datetimes are stored without format as paired integers: the
first integer containing the number of days since the seed date, and the
second containing the number of milliseconds since midnight (to indicate
time of day). Any formatting is done by the client tool used to retrieve the
date from the database, usually using the Regional Settings for the user as
the default format (although this varies depending on which client tool you
are using to retrieve the dates).
Which is good though! (It would be better
of course if it could be entered in DD/MM/YYYY and be recorder in SQL
as such)
Click to expand...

You should always pass dates to SQL Server in a non-ambiguous format (the
ISO standard). If you allow users to enter dates in any way they wish, you
need to reformat them to YYYYMMDD before passing them to SQL. You need to
take control of the display process so the dates will be displayed in your
preferred format. See the links below.
2. field with the current date & time
The ASP page displays it correctly as 02/09/2004 (September 2nd). But
SQL Server saves it as 09/02/2004 (February 9th). And I bet there'll
be an error or September 13th ... Or is there a way to define in SQL
Server something similar to the Default Value: Now() in Access? Then
I could drop this field on the ASP page.
Click to expand...

Again, this is entirely dependant on the Regional Settings for the IUSR
account on your web server machine. See these articles:

http://www.aspfaq.com/show.asp?id=2313 vbscript
http://www.aspfaq.com/show.asp?id=2040 help with dates
http://www.aspfaq.com/show.asp?id=2260 dd/mm/yyy confusion

Bob Barrows
 
Thomas Dodds wrote on 02 sep 2004 in
microsoft.public.inetserver.asp.general:
Perhaps, I do not use SQL server. now() will probably include the
actual serverlocal time. I suspect that is not what you want.
Click to expand...

set default value for column to GETDATE() (SQL Server) [or SYSDATE
(Oracle)]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlre
f/ts_ga-gz_4z51.asp


Evertjan. said:
Jerome wrote on 02 sep 2004 in
microsoft.public.inetserver.asp.general:

No, the general accepted format is yyyy-mm-dd


SQL server does not "display"


In a database field with the type date(!!), as you are using here,
the date is stored as a number.


You should "give" it to the SQL string as yyyy-mm-dd or yyyy/mm/dd


Perhaps, I do not use SQL server. now() will probably include the
actual serverlocal time. I suspect that is not what you want.

But if you 'give' it to the db as a ASP string, convert your input to
a yyyy-mm-dd string

If you retrieve it from the databas, and it comes as mm/dd/yyyy,
convert it by ASP(vbs/jscript) to you preferred string.


Concluding:

Make two functions,
one converting your preferred date string to a yyyy-mm-dd string
and
one converting the database output string to your preferred date
string.
Click to expand...
Click to expand...

Certainly not topposting, nor interposting, nor topquoting.
Thomas, wat is this?

Now() I am sure you ment well,
just forgot to delete the bottom part.

;-}
 
Bob Barrows said:
Wron. SQL Server will also accept the ISO standard date:

YYYYMMDD
Click to expand...

I agree with your advice, Bob, but I'm uneasy about the use of the word
'Standard'.

ISO 8601 states that the standard date format is YYYY-MM-DD, but then goes
on the explain that other delimiters are acceptable as is their omission.

So YYYYMMDD, YYYY-MM-DD and YY-MM-DD are all 'standard' date formats that
meet the ISO 8601 requirements. If you add a time element, as is often
important in DB work then you have further variations still, eg.
YYYY-MM-DDTHH:MM:SS.

Unfortunately, different DB's only accept a subset of the valid formats! So
there's often no way to code some SQL that is truly portable.

Further reading:
RFC3339
ISO8601
etc...

Not quite as straightforward as it should be, perhaps?

Chris
 
Back
Top