Date Output from SQL Server

[Bob Duffy]

Hi,

I have some ASP VbScript that loops through an SQL query and outputs the
results to an HTML page. The problem is that date/time fields are coming out
formatted as US format rather than UK format.

Eg. "8/31/00 11:58:00 AM" instead of "31/08/00 11:58:00 AM"

I have tried using the formatDateTime function of VBscript, but it still
outputs as USA format.

I have also checked the regional settings on server / client and they are
dd/mm/yyyy


Has anyone any suggestions ?

Regards,


Bob Duffy
Prodata
MCSD, MCDBA, MCSE

Sample Code:
===========
dim con
dim rs
dim strSQL

set conn = server.CreateObject("ADODB.Connection")
set rs = server.CreateObject("ADODB.Recordset")
strSQL = "Select * from Blah"

conn.Open ""DRIVER=SQL
Server;SERVER=MyServer;APP=MyApp;WSID=BOB;DATABASE=MyDatabase;Trusted_Connec
tion=Yes;Regional=Yes"

rs.Open strSQL, conn, adOpenForwardOnly, adLockReadOnly

Response.Write "<TABLE>"
do while not rs.EOF
Response.Write "<TR>"
for x = 0 to rs.Fields.Count-1
Response.Write "<TD nowrap>"
select case rs.Fields (X).type
case 135 'Date Format
Response.Write rs.Fields (x).Value
case else
Response.Write rs.Fields (x).Value
end select
Response.Write "</TD>"
next
Response.Write "</TR>"
rs.MoveNext
Loop
Response.Write "</TABLE>"

[Sébastien Ros]

You have to change Regional Settings in the Configuration Panel.
Then it will output the way you want

----
Sébastien Ros
s.ros@essaim.univ-mulhouse.Fr

[Bob Duffy]

Hi,

I have checked the Regional Settings on the control panel and they are
dd/mm/yy on both client and server.

When I run queries in query analyser or any other App apart from IE it
outputs in the correct format, (dd/mm/yy). Its just IE that doesnt give me
the desired format.

Any ideas ?

Regards,


Bob Duffy
Prodata

Sébastien Ros wrote in message <39b37741@news.devx.com>...
>You have to change Regional Settings in the Configuration Panel.
>Then it will output the way you want
>
>----
>Sébastien Ros
>s.ros@essaim.univ-mulhouse.Fr
>
>
>
>

[A D Kendall]

I think VBScript handles all dates internally in US format. (I'm sure I read
it somewhere in MSDN)

You could build up your output string using the Day(), Month() and Year()
functions

Bob Duffy <bob@prodata.ie> wrote in message news:39ae6828$1@news.devx.com...
> Hi,
>
> I have some ASP VbScript that loops through an SQL query and outputs the
> results to an HTML page. The problem is that date/time fields are coming
out
> formatted as US format rather than UK format.
>
> Eg. "8/31/00 11:58:00 AM" instead of "31/08/00 11:58:00 AM"
>
> I have tried using the formatDateTime function of VBscript, but it still
> outputs as USA format.
>
> I have also checked the regional settings on server / client and they are
> dd/mm/yyyy
>
>
> Has anyone any suggestions ?
>
> Regards,
>
>
> Bob Duffy
> Prodata
> MCSD, MCDBA, MCSE
>
> Sample Code:
> ===========
> dim con
> dim rs
> dim strSQL
>
> set conn = server.CreateObject("ADODB.Connection")
> set rs = server.CreateObject("ADODB.Recordset")
> strSQL = "Select * from Blah"
>
> conn.Open ""DRIVER=SQL
>
Server;SERVER=MyServer;APP=MyApp;WSID=BOB;DATABASE=MyDatabase;Trusted_Connec
> tion=Yes;Regional=Yes"
>
> rs.Open strSQL, conn, adOpenForwardOnly, adLockReadOnly
>
> Response.Write "<TABLE>"
> do while not rs.EOF
> Response.Write "<TR>"
> for x = 0 to rs.Fields.Count-1
> Response.Write "<TD nowrap>"
> select case rs.Fields (X).type
> case 135 'Date Format
> Response.Write rs.Fields (x).Value
> case else
> Response.Write rs.Fields (x).Value
> end select
> Response.Write "</TD>"
> next
> Response.Write "</TR>"
> rs.MoveNext
> Loop
> Response.Write "</TABLE>"
>
>

[Bob Duffy]

Hi,

You must be right. I've opted for using the Day, Month, Year route and it
works - a bit slower I'm sure but at least its works ;-)

Many Thanks,


Bob Duffy
Prodata

[Gene Black]

See the documentation for the T-SQL function 'convert'

you should be able to do the following

select convert(char,mydate,3) from mytable

or

select convert(char,mydate,103) from mytable

This will return a date in the format

dd/mm/yy
dd/mm/yyyy

respectively

If you need the time also add a '+ convert(char,mydate,8)'



"Bob Duffy" <bob@prodata.ie> wrote in message
news:39ae6828$1@news.devx.com...
> Hi,
>
> I have some ASP VbScript that loops through an SQL query and outputs the
> results to an HTML page. The problem is that date/time fields are coming
out
> formatted as US format rather than UK format.
>
> Eg. "8/31/00 11:58:00 AM" instead of "31/08/00 11:58:00 AM"
>
> I have tried using the formatDateTime function of VBscript, but it still
> outputs as USA format.
>
> I have also checked the regional settings on server / client and they are
> dd/mm/yyyy
>
>
> Has anyone any suggestions ?
>
> Regards,
>
>
> Bob Duffy
> Prodata
> MCSD, MCDBA, MCSE
>
> Sample Code:
> ===========
> dim con
> dim rs
> dim strSQL
>
> set conn = server.CreateObject("ADODB.Connection")
> set rs = server.CreateObject("ADODB.Recordset")
> strSQL = "Select * from Blah"
>
> conn.Open ""DRIVER=SQL
>
Server;SERVER=MyServer;APP=MyApp;WSID=BOB;DATABASE=MyDatabase;Trusted_Connec
> tion=Yes;Regional=Yes"
>
> rs.Open strSQL, conn, adOpenForwardOnly, adLockReadOnly
>
> Response.Write "<TABLE>"
> do while not rs.EOF
> Response.Write "<TR>"
> for x = 0 to rs.Fields.Count-1
> Response.Write "<TD nowrap>"
> select case rs.Fields (X).type
> case 135 'Date Format
> Response.Write rs.Fields (x).Value
> case else
> Response.Write rs.Fields (x).Value
> end select
> Response.Write "</TD>"
> next
> Response.Write "</TR>"
> rs.MoveNext
> Loop
> Response.Write "</TABLE>"
>
>