date return problem

[acc1]

Hi. I have a peculiar problem. I am running a Win2K Server on a PII 400 PC with SQL 2K to do my development.
When I do a "select datefield from table" in ASP, the dates returned are always in MM/DD/YYYY format, regardless of the regional settings.
However, went I move my codes to another server (Win2K Server on a PIII 800 PC with SQL 2K), I find that the dates returned is either DD/MM/YYYY or MM/DD/YYYY depending on the server's regional settings' date.
Any ideas? Does SQL returns date in a fixed format or is it dependent on the regional setting?
The connection string I used is:
"Provider=SQLOLEDB.1; User ID=sa; Password=password; DATABASE=testdb;"
I run my query in the following manner:
sqlstr = "select datefrom, dateto from datetable "
set rsDates = server.CreateObject("adodb.recordset&quot
rsDates.Open sqlstr,conn,1,3
I display the field in HTML using:
<%=rsDates(&quot;datefrom&quot%>

Thanks in advance!

 

[mukund]

Hi Acc1,
You can use a Convert statement to convert tht date being returned in the desired format.
for e.g. you can use

select convert(varchar(10), datefrom, 101) from tablename
This will return you the date in American format.

Refer Convert in SQL BOL.

I will recommend you to use the style 100, this removes all the ambiguity as the Date is returned
as
Jul 16 2001

convert(varchar(11), datefrom , 100)


Hope this helps.
Mukund.

 

[acc1]

Thanks Mukund. I will try it.