ASP Insert Date & Time Problem

[Nicolae Fieraru]

Hi All,

I live in Australia and I have Access installed on my computer. Regional
settings are set to Australian Standards (dd/MM/yyyy)
I try to insert the current date into a table from an asp page.

I set the date format with

Session.LCID = 3081

I use SQL expressions to enter the current date into a database. I noticed
that there is a difference between

objConn.Execute "Insert Into tbDate(Date1) " & "Values ('" & Now() & "')"

and

strQ2 = "Select * From tbDate Where Index = " & maxindex
objRS2.Open strQ2, objConn, 2, 2

objRS2("Date1") = Now()
objRS2.Update


The second sequence updates the date to the right value in the database.
The first one reverses the day and month. I read extensively on the internet
and I tried almost any advice I found, but none seem to make the first
expression to do the right thing. (I tried using #, or to format the date to
ISO standard - which Access doesn't accept!). I could do some silly thing,
like to insert the date with the first expression and then to correct it
with the second one, but I am sure somebody should have solved this problem
in a better way.

Any idea would be appreciated

Regards,
Nicolae

 

[Ken Schaefer]

Hi,

If you do this:

<%
strSQL = "UPDATE myDatabase SET myDateField = #2003/08/05#"
%>

it'll work (I know, I do it all the time)
www.adopenstatic.com/faq/dateswithaccess.asp
(notice that I put the # in, and I used / as delimiters, unlike plain ISO)

HOWEVER, for you particular case, you can just use the built in VBA
functions:

strSQL = "UPDATE myDatabase SET myDateField = Date()"

as Access has an inbuilt Date() function that returns the current date. Then
you don't have to worry about formatting...

Cheers
Ken

: Hi All,
:
: I live in Australia and I have Access installed on my computer. Regional
: settings are set to Australian Standards (dd/MM/yyyy)
: I try to insert the current date into a table from an asp page.
:
: I set the date format with
:
: Session.LCID = 3081
:
: I use SQL expressions to enter the current date into a database. I noticed
: that there is a difference between
:
: objConn.Execute "Insert Into tbDate(Date1) " & "Values ('" & Now() & "')"
:
: and
:
: strQ2 = "Select * From tbDate Where Index = " & maxindex
: objRS2.Open strQ2, objConn, 2, 2
:
: objRS2("Date1") = Now()
: objRS2.Update
:
:
: The second sequence updates the date to the right value in the database.
: The first one reverses the day and month. I read extensively on the
internet
: and I tried almost any advice I found, but none seem to make the first
: expression to do the right thing. (I tried using #, or to format the date
to
: ISO standard - which Access doesn't accept!). I could do some silly thing,
: like to insert the date with the first expression and then to correct it
: with the second one, but I am sure somebody should have solved this
problem
: in a better way.
:
: Any idea would be appreciated
:
: Regards,
: Nicolae
:
:

 

[Nicolae Fieraru]

Hi Ken,

Than you for your fast reply.
You are right, if I format the date as you said, it should work. But I need
more than the Date, I also need the time.
Meantime I solved the problem. I modified the function I had from NG and now
I can insert the Date and Time

The function I am using is:

Function isoDate(inDate)
' ===============
On Error Resume Next
isoDate = Year(inDate) & "/" & right("0" & Month(inDate),2) & "/" &
right("0" & Day(inDate),2) & " " & right("0" & Hour(inDate),2) & ":" &
right("0" & Minute(inDate),2) & ":" & right("0" & Second(inDate),2)
On Error GoTo 0
End Function

I had to add the slashes between year, month and day because Access doesn't
like all of them together, as yyyymmdd

Best regards,
Nicolae