Date Format dd/MM/yyyy

[Kwong]

I am using frontpage with access to save some data, including dates. I
need the date to be saved in the format dd/MM/yyyy (and all
calculations and queries to be compatible) so I used the FormatDateTime
function. I changed my regional settings such that the short date is
dd/MM/yyyy but, it still uses the m/d/yyyy for an unknown reason.

on one page, i have the user use three drop down menus to input day,
month, and year. these are sent to another page - and below is what I
have coded. Please let me know what I am doing wrong.
Thank you.

<%

dim myDate(2)
dim strQty
dim strDate, stringDate


strQty = request("Qty")

myDate(0) = request("Date")
myDate(1) = request("Month")
myDate(2) = request("Year")

strDate = Join(myDate, "-")
strDate = FormatDateTime(strDate, 2)
stringDate = FormatDateTime(strDate, 1)

response.write "<p>date is: --> : " &strDate& " "

if IsDate(strDate) = FALSE then

response.write "<p>The date " & strDate & " does not exist <p> Please
enter a new date.<p>"
response.write "<p>Return to <a href='date.htm'>Form</a> or click
back<p>"

else

response.write "<p>all is good <p>"
response.write "<p>i repeat, the date entered is: " & stringDate & " "

session("Date") = strDate
session("Qty") = strQty
response.write "<p>qty entered is: " &strQty& " "

end if


response.write "<p><a href='index.htm'>Homepage</a></p>"

%>

 

[Kevin Spencer]

Hi Kwong,

Interesting question. You seem to have done your homework. Format 1 is
vbLongDate, which should use your computer's regional settings. Format 2 is
vbShortDate, which should also use your computer's regional settings. My
first question would be, where are you requesting this page from? If the ASP
page is on your local computer, it should display the date as you expect. On
the other hand, if the ASP page is on a remote computer, it will use that
computer's regional settings. Once the date has been formatted, it is a
string in an HTML page, and it will not "translate" to the browser
computer's regional settings.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
What You Seek Is What You Get.

 

[Jon Spivey]

Hi,
you can't save a date to a database in any format - MS databases store dates
as numbers and assume they're in US mm/dd/yyyy format. First we need to get
the date into the database correctly. To avoid confusion it's much easier to
use the ISO date format yyyy-mm-dd so today is 2005-05-10 - this format is
understood by every database and avoids the issue of 10/5/2005 being 10 May
or 5 October

<%
day = request("Date")
month = request("Month")
year = request("Year")
d = year & "-" & month & "-" & day
if isdate(d) then
' it's a date
' stick it into the database
end if
%>

Now we've got the date stored we can display it in the right format

SELECT Format(DateField, 'dd/mm/yyyy') AS UkDate

You can format however you like here - eg long dates, short dates etc

 

[Kwong]

Hi,

I am using the ASP on my own computer.
I am trying to use the SELECT Format(DateField, 'dd/mm/yyyy') AS UkDate
statement that you suggested, but i do not know where to add it. i
tried to add it as part of the main SQL statement when i am listing the
records in the database - but that does not work.

if i leave it as is, it displays the info but the date is d/m/yyyy

this is what i have:
.....

strSQL = "SELECT * FROM math ORDER BY id;"

' strSQL = "SELECT Format(date, 'dd/mm/yyyy') AS UkDate FROM math ORDER
BY id;"
' date = FormatDateTime(date, 1)

Set rstDBEdit = Server.CreateObject("ADODB.Recordset")
rstDBEdit.Open strSQL, CONN_STRING, adOpenForwardOnly, adLockReadOnly,
adCmdText
%>

<table border="1" cellspacing="2" cellpadding="2">
<thead>
<tr>
<th>id</th>
<th>Qty</th>
<th>Date</th>
</tr>
</thead>
<tbody>
<%

Do While Not rstDBEdit.EOF
%>
<tr>
<% ' date = Format(date, 'dd/mm/yyyy')%>
<td><%= rstDBEdit.Fields("id").Value %>&nbsp;</td>
<td><%= rstDBEdit.Fields("qty").Value %>&nbsp;</td>
<td><%= rstDBEdit.Fields("date").Value %>&nbsp;</td>

</tr>
<%
rstDBEdit.MoveNext

Loop

%>

</tbody>
<tfoot>
<tr>

</tr>
</tfoot>
</table>
<%
rstDBEdit.Close
Set rstDBEdit = Nothing
%>

Thanks a lot for all the help.

 

[Jon Spivey]

Hi,

Ideally you should list all the fields you want in your select rather than
using select * - it's more efficient. So you'd have something like
select field1, field2, field3, Format(DateField, 'dd/mm/yyyy') AS UkDate
from table
this will give you a field called UkDate containg the formatted date

If you want to stick with select * you can do
select *, Format(DateField, 'dd/mm/yyyy') AS UkDate from table
This will give you all the fields in your table plus the UkDate field

 

[Kwong]

that works!
thank you all very much.