ASP Insert Date & Time Problem

Discussion in 'ASP General' started by Nicolae Fieraru, Aug 5, 2003.

  1. 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, Aug 5, 2003
    #1
    1. Advertising

  2. Nicolae Fieraru

    Ken Schaefer Guest

    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

    "Nicolae Fieraru" <> wrote in message
    news:%23$...
    : 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, Aug 5, 2003
    #2
    1. Advertising

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


    "Ken Schaefer" <> wrote in message
    news:#...
    > 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
    >
    > "Nicolae Fieraru" <> wrote in message
    > news:%23$...
    > : 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, Aug 5, 2003
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Chris Berg
    Replies:
    0
    Views:
    795
    Chris Berg
    Oct 27, 2003
  2. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,278
    Michael Borgwardt
    May 30, 2004
  3. Keith Cochrane
    Replies:
    2
    Views:
    626
    Keith Cochrane
    Aug 6, 2006
  4. Summu82
    Replies:
    5
    Views:
    869
    Richard Heathfield
    Jun 7, 2006
  5. Robin Lawrie
    Replies:
    18
    Views:
    790
    Mark Schupp
    Mar 1, 2005
Loading...

Share This Page