ASP > SQL date problem!?

[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

 

[Evertjan.]

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.

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!?

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)

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).

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.

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?

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.

 

[Bob Barrows [MVP]]

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.

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!?

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)

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.

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]

And I bet there'll

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

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

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

 

[Evertjan.]

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.

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

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.

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.

;-}

 

[CJM]

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

YYYYMMDD

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

 

[Thomas Dodds]

right - pick on me for having the tech skills to answer the question ....