Leading Zeros dropped in date returned from SQL Data using ASP

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

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

"Sean" wrote in message
news:7EE8E271-71FF-4355-BFBF-EDB9DCA8EE23@microsoft.com...
> 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?

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

Mark,

Thanks for your quick response. I will review and test your suggestions.

I have changed the Date regional settings both on the Server (IIS, SQL) and
the clients(XP) to use the short date format of MM/DD/YYYY. This did not
help.

The other thing that bothers me is,"What changed?" Simply moving a .ASP page
from a W2000/IIS5 to W2K3/IIS6 should not have an effect on the way the date
value is interuppted. Should it?

"Mark McGinty" wrote:

>
> "Sean" wrote in message
> news:7EE8E271-71FF-4355-BFBF-EDB9DCA8EE23@microsoft.com...
> > 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?
>
>
>

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

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" wrote in message
> news:7EE8E271-71FF-4355-BFBF-EDB9DCA8EE23@microsoft.com...
> > 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?
>
>
>

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

Mark McGinty wrote:
> 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.
>
>
Only if the SQL Server is set up with the default settings. The
collation does not have to be SQL_Latin1_General_Cp1_CI_AS
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

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

Bob,

Thanks for the response.

It there a MM/DD/YYYY setting some where inside of SQL 2000? The server
this is failing on, is a fairly recently built server.

"Bob Barrows [MVP]" wrote:

> Mark McGinty wrote:
> > 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.
> >
> >
> Only if the SQL Server is set up with the default settings. The
> collation does not have to be SQL_Latin1_General_Cp1_CI_AS
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

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

"Sean" wrote in message
news:3EFA5D66-6DE5-4F7B-958E-C376BF76E8E4@microsoft.com...
> 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????

No, but I'll bet the settings are specific to the user's profile. A quick
reality check:

d = CDate("01/01/2006")
WScript.Echo d

Save to anything.vbs and run it; you can see the date format changes take
effect immediately. Not sure how to force the setting onto all users,
probably the system policy editor (or domain policy, if your server is a
domain member and there are GPOs published on the domain.)

-Mark




> "Mark McGinty" wrote:
>
>>
>> "Sean" wrote in message
>> news:7EE8E271-71FF-4355-BFBF-EDB9DCA8EE23@microsoft.com...
>> > 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?
>>
>>
>>

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

Mark,

Thanks for the repsonse.

I tried your script on both the server and the client. Both came back as
01/10/2006.

I even tried this in the .ASP code

DATE_DUE = rs("DATE_DUE")
response.write DATE_DUE

or

DATE_DUE = CDate(rs("DATE_DUE"))
response.write DATE_DUE

In either response, it comes back as 1/1/2006.

Sean


"Mark McGinty" wrote:

>
> "Sean" wrote in message
> news:3EFA5D66-6DE5-4F7B-958E-C376BF76E8E4@microsoft.com...
> > 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????
>
> No, but I'll bet the settings are specific to the user's profile. A quick
> reality check:
>
> d = CDate("01/01/2006")
> WScript.Echo d
>
> Save to anything.vbs and run it; you can see the date format changes take
> effect immediately. Not sure how to force the setting onto all users,
> probably the system policy editor (or domain policy, if your server is a
> domain member and there are GPOs published on the domain.)
>
> -Mark
>
>
>
>
> > "Mark McGinty" wrote:
> >
> >>
> >> "Sean" wrote in message
> >> news:7EE8E271-71FF-4355-BFBF-EDB9DCA8EE23@microsoft.com...
> >> > 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?
> >>
> >>
> >>
>
>
>

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

Bob Barrows [MVP] wrote:
> Mark McGinty wrote:
>> 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.
>>
>>
> Only if the SQL Server is set up with the default settings. The
> collation does not have to be SQL_Latin1_General_Cp1_CI_AS

I retract this. I read your post too fast.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

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

My approach would be :

#1 make sure the value you get is really a date (using TypeName for example)
#2 my personal preference for ASP was to use my own formatting function so
that I don't depend on any application external settings

--
Patrice

"Sean" a écrit dans le message de news:
3EFA5D66-6DE5-4F7B-958E-C376BF76E8E4@microsoft.com...
> 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" wrote in message
>> news:7EE8E271-71FF-4355-BFBF-EDB9DCA8EE23@microsoft.com...
>> > 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?
>>
>>
>>

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

Try :

Response.Write TypeName(rst("Date_due").value) & "
"
Response.Write Date & "
"
Response.Write rst("Date_due").value & "
"

and copy/paste the output...

--
Patrice

"Sean" a écrit dans le message de news:
B56C5460-C9EC-453D-9A59-2CCA456D5C04@microsoft.com...
> Mark,
>
> Thanks for the repsonse.
>
> I tried your script on both the server and the client. Both came back as
> 01/10/2006.
>
> I even tried this in the .ASP code
>
> DATE_DUE = rs("DATE_DUE")
> response.write DATE_DUE
>
> or
>
> DATE_DUE = CDate(rs("DATE_DUE"))
> response.write DATE_DUE
>
> In either response, it comes back as 1/1/2006.
>
> Sean
>
>
> "Mark McGinty" wrote:
>
>>
>> "Sean" wrote in message
>> news:3EFA5D66-6DE5-4F7B-958E-C376BF76E8E4@microsoft.com...
>> > 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????
>>
>> No, but I'll bet the settings are specific to the user's profile. A
>> quick
>> reality check:
>>
>> d = CDate("01/01/2006")
>> WScript.Echo d
>>
>> Save to anything.vbs and run it; you can see the date format changes take
>> effect immediately. Not sure how to force the setting onto all users,
>> probably the system policy editor (or domain policy, if your server is a
>> domain member and there are GPOs published on the domain.)
>>
>> -Mark
>>
>>
>>
>>
>> > "Mark McGinty" wrote:
>> >
>> >>
>> >> "Sean" wrote in message
>> >> news:7EE8E271-71FF-4355-BFBF-EDB9DCA8EE23@microsoft.com...
>> >> > 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?
>> >>
>> >>
>> >>
>>
>>
>>

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

"Bob Barrows [MVP]" wrote in message
news:etE8IvaCHHA.4256@TK2MSFTNGP04.phx.gbl...
> Mark McGinty wrote:
>> 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.
>>
>>
> Only if the SQL Server is set up with the default settings. The
> collation does not have to be SQL_Latin1_General_Cp1_CI_AS

I thought the the output format dictated by the style parameter was
absolute... BOL certainly infers this, are you sure about that?


-Mark


> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

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

Added this code:

Response.Write "TEST1: " & TypeName(rs("Date_due").value) & "
"
Response.Write "TEST2: " & Date & "
"
Response.Write "TEST3: " & rs("Date_due").value & "
"

Output:

TEST2: 11/16/2006

"Patrice" wrote:

> Try :
>
> Response.Write TypeName(rst("Date_due").value) & "
"
> Response.Write Date & "
"
> Response.Write rst("Date_due").value & "
"
>
> and copy/paste the output...
>
> --
> Patrice
>
> "Sean" a écrit dans le message de news:
> B56C5460-C9EC-453D-9A59-2CCA456D5C04@microsoft.com...
> > Mark,
> >
> > Thanks for the repsonse.
> >
> > I tried your script on both the server and the client. Both came back as
> > 01/10/2006.
> >
> > I even tried this in the .ASP code
> >
> > DATE_DUE = rs("DATE_DUE")
> > response.write DATE_DUE
> >
> > or
> >
> > DATE_DUE = CDate(rs("DATE_DUE"))
> > response.write DATE_DUE
> >
> > In either response, it comes back as 1/1/2006.
> >
> > Sean
> >
> >
> > "Mark McGinty" wrote:
> >
> >>
> >> "Sean" wrote in message
> >> news:3EFA5D66-6DE5-4F7B-958E-C376BF76E8E4@microsoft.com...
> >> > 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????
> >>
> >> No, but I'll bet the settings are specific to the user's profile. A
> >> quick
> >> reality check:
> >>
> >> d = CDate("01/01/2006")
> >> WScript.Echo d
> >>
> >> Save to anything.vbs and run it; you can see the date format changes take
> >> effect immediately. Not sure how to force the setting onto all users,
> >> probably the system policy editor (or domain policy, if your server is a
> >> domain member and there are GPOs published on the domain.)
> >>
> >> -Mark
> >>
> >>
> >>
> >>
> >> > "Mark McGinty" wrote:
> >> >
> >> >>
> >> >> "Sean" wrote in message
> >> >> news:7EE8E271-71FF-4355-BFBF-EDB9DCA8EE23@microsoft.com...
> >> >> > 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?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

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

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.

"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" wrote in message
> > news:7EE8E271-71FF-4355-BFBF-EDB9DCA8EE23@microsoft.com...
> > > 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?
> >
> >
> >

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

"Sean" wrote in message
news:B56C5460-C9EC-453D-9A59-2CCA456D5C04@microsoft.com...
> Mark,
>
> Thanks for the repsonse.
>
> I tried your script on both the server and the client. Both came back as
> 01/10/2006.
>
> I even tried this in the .ASP code
>
> DATE_DUE = rs("DATE_DUE")
> response.write DATE_DUE
>
> or
>
> DATE_DUE = CDate(rs("DATE_DUE"))
> response.write DATE_DUE
>
> In either response, it comes back as 1/1/2006.

IIS/ASP runs in a different user context, and regional settings are
definitely maintained per user. There must be a way to set them for the
whole system...

Out of curiosity, why don't you want to format it on the SQL side?


-Mark


>
Sean
>
>
> "Mark McGinty" wrote:
>
>>
>> "Sean" wrote in message
>> news:3EFA5D66-6DE5-4F7B-958E-C376BF76E8E4@microsoft.com...
>> > 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????
>>
>> No, but I'll bet the settings are specific to the user's profile. A
>> quick
>> reality check:
>>
>> d = CDate("01/01/2006")
>> WScript.Echo d
>>
>> Save to anything.vbs and run it; you can see the date format changes take
>> effect immediately. Not sure how to force the setting onto all users,
>> probably the system policy editor (or domain policy, if your server is a
>> domain member and there are GPOs published on the domain.)
>>
>> -Mark
>>
>>
>>
>>
>> > "Mark McGinty" wrote:
>> >
>> >>
>> >> "Sean" wrote in message
>> >> news:7EE8E271-71FF-4355-BFBF-EDB9DCA8EE23@microsoft.com...
>> >> > 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?
>> >>
>> >>
>> >>
>>
>>
>>

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

"Sean" wrote in message
news:01C5932A-4CC7-4344-B21C-03592A1BDDBC@microsoft.com...
> 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" wrote in message
>> > news:7EE8E271-71FF-4355-BFBF-EDB9DCA8EE23@microsoft.com...
>> > > 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?
>> >
>> >
>> >