inconsistent date format

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • pb
    #1

    inconsistent date format

    Hi,

    I am updating a database based and adding the time the entry was added
    with the query below...

    strQuery = "INSERT INTO Visits ( EmailAddress, [Password],
    TimeCreated ) values ('" & emailaddress & "','" & password & "','" &
    Now() & "')"

    The TimeCreated field is specified as a date field.

    I then subsequently read from the database and check against the
    current time....

    dateCreated = dr.Item("Timecr eated")
    dateNow = Now()

    If (DateDiff(DateI nterval.Minute, dateCreated, dateNow)) 5
    Then.....


    What I am finding is the day and month has been interchanged...

    7/6/2007 9:03:14 = datecreated
    6/7/2007 9:03:16 = datenow

    Now the annoying thing was I wrote this yesterday on the 6/6 and
    everything worked as expected. Come today!!!

    What trick can I use to remedy this?

    Cheers,

    Pb

    Tags: None
    • Jon Skeet [C# MVP]
      #2
      Re: inconsistent date format

      pb <philbrierley@h otmail.comwrote :
      I am updating a database based and adding the time the entry was added
      with the query below...
      >
      strQuery = "INSERT INTO Visits ( EmailAddress, [Password],
      TimeCreated ) values ('" & emailaddress & "','" & password & "','" &
      Now() & "')"
      That's a very bad way of updating a database. It leaves you open for
      SQL injection attacks, and as you've found there's a problem with
      formatting dates and times.

      Use a parameterized SQL command instead.

      --
      Jon Skeet - <skeet@pobox.co m>
      http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
      If replying to the group, please do not mail me too

        Comment

        • pb
          #3
          Re: inconsistent date format

          On Jun 7, 9:31 am, Jon Skeet [C# MVP] <s...@pobox.com wrote:
          pb <philbrier...@h otmail.comwrote :
          I am updating a database based and adding the time the entry was added
          with the query below...
          >
          strQuery = "INSERT INTO Visits ( EmailAddress, [Password],
          TimeCreated ) values ('" & emailaddress & "','" & password & "','" &
          Now() & "')"
          >
          That's a very bad way of updating a database. It leaves you open for
          SQL injection attacks, and as you've found there's a problem with
          formatting dates and times.
          >
          Use a parameterized SQL command instead.
          >
          --
          Jon Skeet - <s...@pobox.com >http://www.pobox.com/~skeet Blog:http://www.msmvps.com/jon.skeet
          If replying to the group, please do not mail me too

          Thanks for the response, but my question is why there is a problem
          with the dates?


            Comment

            • mpetrotta@gmail.com
              #4
              Re: inconsistent date format

              On Jun 6, 3:41 pm, pb <philbrier...@h otmail.comwrote :
              On Jun 7, 9:31 am, Jon Skeet [C# MVP] <s...@pobox.com wrote:
              >
              >
              >
              pb <philbrier...@h otmail.comwrote :
              I am updating a database based and adding the time the entry was added
              with the query below...
              >
              strQuery = "INSERT INTO Visits ( EmailAddress, [Password],
              TimeCreated ) values ('" & emailaddress & "','" & password & "','" &
              Now() & "')"
              >
              That's a very bad way of updating a database. It leaves you open for
              SQL injection attacks, and as you've found there's a problem with
              formatting dates and times.
              >
              Use a parameterized SQL command instead.
              >
              --
              Jon Skeet - <s...@pobox.com >http://www.pobox.com/~skeet Blog:http://www.msmvps.com/jon.skeet
              If replying to the group, please do not mail me too
              >
              Thanks for the response, but my question is why there is a problem
              with the dates?
              There's no problem with the dates at all. Both '7/6/2007' and
              '6/7/2007' are valid dates representing June 7, 2007.

              June 7, 2007 is 6/7/2007 in some parts of the world, 7/6/2007 in
              others. It's common in Europe, I think. What date format your
              computer gives you (month-first or day-first) depends on your
              operating system's localization settings (Regional & Language Options
              control panel, in recent versions of Windows). You're likely seeing
              one of your client computers with a different localization setting
              than what you're expecting. Wait another week until 13/6/2007, and
              see what happens with your statements!

              The best way to fix this is to switch to parameterized sql statements,
              as Jon suggested.

              Michael

                Comment

                • mpetrotta@gmail.com
                  #5
                  Re: inconsistent date format

                  On Jun 6, 3:26 pm, pb <philbrier...@h otmail.comwrote :
                  Hi,
                  >
                  I am updating a database based and adding the time the entry was added
                  with the query below...
                  >
                  strQuery = "INSERT INTO Visits ( EmailAddress, [Password],
                  TimeCreated ) values ('" & emailaddress & "','" & password & "','" &
                  Now() & "')"
                  Hey Pb, I don't mean to hassle you, but have you considered what would
                  happen if a user learned a little something about your application and
                  entered their email address as "foo';trunc ate Visits;--"

                  Don't try that on your production database.

                  Michael

                    Comment

                    • pb
                      #6
                      Re: inconsistent date format

                      On Jun 7, 10:26 am, "mpetro...@gmai l.com" <mpetro...@gmai l.comwrote:
                      On Jun 6, 3:41 pm, pb <philbrier...@h otmail.comwrote :
                      >
                      >
                      >
                      >
                      >
                      On Jun 7, 9:31 am, Jon Skeet [C# MVP] <s...@pobox.com wrote:
                      >
                      pb <philbrier...@h otmail.comwrote :
                      I am updating a database based and adding the time the entry was added
                      with the query below...
                      >
                      strQuery = "INSERT INTO Visits ( EmailAddress, [Password],
                      TimeCreated ) values ('" & emailaddress & "','" & password & "','" &
                      Now() & "')"
                      >
                      That's a very bad way of updating a database. It leaves you open for
                      SQL injection attacks, and as you've found there's a problem with
                      formatting dates and times.
                      >
                      Use a parameterized SQL command instead.
                      >
                      --
                      Jon Skeet - <s...@pobox.com >http://www.pobox.com/~skeetBlog:http....com/jon.skeet
                      If replying to the group, please do not mail me too
                      >
                      Thanks for the response, but my question is why there is a problem
                      with the dates?
                      >
                      There's no problem with the dates at all. Both '7/6/2007' and
                      '6/7/2007' are valid dates representing June 7, 2007.
                      >
                      June 7, 2007 is 6/7/2007 in some parts of the world, 7/6/2007 in
                      others. It's common in Europe, I think. What date format your
                      computer gives you (month-first or day-first) depends on your
                      operating system's localization settings (Regional & Language Options
                      control panel, in recent versions of Windows). You're likely seeing
                      one of your client computers with a different localization setting
                      than what you're expecting. Wait another week until 13/6/2007, and
                      see what happens with your statements!
                      >
                      The best way to fix this is to switch to parameterized sql statements,
                      as Jon suggested.
                      >
                      Michael- Hide quoted text -
                      >
                      - Show quoted text -
                      Hi - I am going to swithch to parameterised queries, just need a
                      little more time to investigate how to do this...


                      BUT...

                      I am running both these queries on my local development machine - not
                      on the web. So how does that explain why the dates are different -
                      nothing has gone outside of my laptop!






                        Comment

                        • Jon Skeet [C# MVP]
                          #7
                          Re: inconsistent date format

                          pb <philbrierley@h otmail.comwrote :
                          Hi - I am going to swithch to parameterised queries, just need a
                          little more time to investigate how to do this...
                          >
                          >
                          BUT...
                          >
                          I am running both these queries on my local development machine - not
                          on the web. So how does that explain why the dates are different -
                          nothing has gone outside of my laptop!
                          It's possible that the database instance itself is in a particular
                          culture.

                          --
                          Jon Skeet - <skeet@pobox.co m>
                          http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
                          If replying to the group, please do not mail me too

                            Comment

                            • Andrew Morton
                              #8
                              Re: inconsistent date format

                              pb wrote:
                              I am updating a database based and adding the time the entry was added
                              with the query below...
                              >
                              strQuery = "INSERT INTO Visits ( EmailAddress, [Password],
                              TimeCreated ) values ('" & emailaddress & "','" & password & "','" &
                              Now() & "')"
                              >
                              The TimeCreated field is specified as a date field.
                              How about making sure the dates/times are specified unambiguously in ISO
                              8601 format, like yyyy-MM-dd hh:mm:ss ?

                              Andrew


                                Comment

                                Previous template Next
                                Working...