Re: ASP > SQL date problem!?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Evertjan. (exjxw.hannivoort_at_interxnl.net)
Date: 09/02/04 

Date: 02 Sep 2004 09:51:24 GMT

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. 

-- 
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress,
but let us keep the discussions in the newsgroup)

Relevant Pages

  • Re: ASP > SQL date problem!?
    ... the general accepted format is yyyy-mm-dd ... I do not use SQL server. ... But if you 'give' it to the db as a ASP string, ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Linking tables access - sql server 2005
    ... Another advantage of this method is that you don't need an ODBC setting on the local computer, ... Create a linked table to SQL Server without using a DSN ... Name of the table that you are linking to on the SQL Server database ... Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Cannot Generate SSPI Context - help
    ... I have used the following DSN-less string: ... Microsoft OLE DB Provider for SQL Server error '80004005' ... When a connection is "trusted," it means ... > How would one connect to a remote SQL Server using Windows authen? ...
    (microsoft.public.inetserver.asp.db)
  • Re: INSERT Query problem with Quotes & Apostrophes
    ... I've got front-ends that go against both Jet and SQL Server databases. ... InputText As String, _ ... >> Delimiter, Delimiter & Delimiter) ... dDateTime, ...
    (microsoft.public.access.modulesdaovba)
  • Re: ADO.NET 2.0 saving single space to SQL?
    ... It is code someone else wrote quite some time ago and all of the string ... Hitchhiker's Guide to Visual Studio and SQL Server ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ...
    (microsoft.public.dotnet.framework.adonet)