Re: Leading Zeros dropped in date returned from SQL Data using ASP


"Sean" <Sean@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:01C5932A-4CC7-4344-B21C-03592A1BDDBC@xxxxxxxxxxxxxxxx
We did note that the regional date settings are profile based. We logged
onto the server with the Network ID that has been assigned to SQL and
changed
the regional data short date format to MM/DD/YYYY. This did not help.

It's not SQL that's formatting it, it's ASP, that's the relevant context.
(Typically the IUSR_[machine name] account.

-Mark




"Sean" wrote:

If I change the ShortDate format in Regional and Language to MM/DD/YYYY,
does
that require the Server/PC to be rebooted in order for the change to take
effect????


"Mark McGinty" wrote:


"Sean" <Sean@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7EE8E271-71FF-4355-BFBF-EDB9DCA8EE23@xxxxxxxxxxxxxxxx
Hello,

I am moving a fairly simple .ASP page from a W2000/IIS5 to a
W2K3/IIS6
box.
It uses a simple select statement against a SQL2000 Table. One of
the
fields
in the table is set a type of DateTime and the value of the field is
01/01/2006.

Actually, the value of that, in SQL Server, is 0x0000973C00000000;
VBScript
stores it internally as 38718.0, and ECMA Script (JavaScript/JScript)
stores
the same thing internally as 1136102400000. The strings that people
commonly understand are merely renderings for output.

If you return the field as a date type, you rely on VBS (or whatever
language you're using) to render a string representation of it. You
can set
the way VBS renders dates on a system in the Control Panel -> Regional
and
Language settings applet, assuming you have admin access to.

If you want to insure a specific format is used for that rendering,
[arguably] the best place to do it is in the SQL statement:

convert(varchar, mydate, 101)

will get you USA-style month/day/year, with month and day zero-padded
on the
left to 2 digits, no matter what the web server's regional settings may
happen to be.


-Mark



In the ASP, the code to retireve is as simply as DATE-DUE =
rs('DATEDUE"). On the old website, the ASP page would return
01/01/2006.
Running the page from the new website, I get 1/1/2006.

I have checked Short Date format in Regional Settings. Tried using
the
FormatDateTime. Tried looking for settings in IIS and SQL. No luck.

Any thoughts?





.

Relevant Pages

  • Re: How to Set Datetime on SQL 2000 server
    ... I ran Query Analyzer on the SQL Enterprise Manager and ran: ... Windows 2000 server and the test 2003 server actually use US date format, ... regional settings, since you now are dependent on that as well. ...
    (microsoft.public.sqlserver.setup)
  • Re: mySQL Problem
    ... Actually, problem is proper quoting, not the format or anything else. ... the SQL standard and won't work on any other RDBMS I'm familiar with. ... MS SQL Server use square brackets in the same way. ... I think You are wrong and lack of proper quotation in field names isan ...
    (comp.lang.php)
  • Re: Feldinhalt nach Aktualiserung formatieren
    ... > Auf dem SQL Server habe ich noch das Problem das er mir ... dass Du das Feld im Richtigen ... Format übergibst, am besten wandelst Du das einfach in eine Zahl um, ...
    (microsoft.public.de.access)
  • VB->SQL: Date Format Problem.
    ... which although it worked on my development machine, ... but wasnt accepted by the live server. ... it appears that the SQL code that is called ... I imagine that this is something to do with regional settings or ...
    (microsoft.public.sqlserver.programming)
  • VB->SQL: Date Format Problem.
    ... which although it worked on my development machine, ... but wasnt accepted by the live server. ... it appears that the SQL code that is called ... I imagine that this is something to do with regional settings or ...
    (microsoft.public.vb.database)
Quantcast