inconsistent date format

[pb]

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("Timecreated")
dateNow = Now()

If (DateDiff(DateInterval.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

[Jon Skeet [C# MVP]]

pb <ph**********@hotmail.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 - <sk***@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

[pb]

On Jun 7, 9:31 am, Jon Skeet [C# MVP] <[e-mail]:

pb <[e-mail]:

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 - <[e-mail]>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?

[mpetrotta]

On Jun 6, 3:41 pm, pb <[e-mail]:

On Jun 7, 9:31 am, Jon Skeet [C# MVP] <[e-mail]:

pb <[e-mail]:

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 - <[e-mail]>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

[mpetrotta]

On Jun 6, 3:26 pm, pb <[e-mail]:

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';truncate Visits;--"

Don't try that on your production database.

Michael

[pb]

On Jun 7, 10:26 am, "[e-mail]" <[e-mail]:

On Jun 6, 3:41 pm, pb <[e-mail]:

On Jun 7, 9:31 am, Jon Skeet [C# MVP] <[e-mail]:

pb <[e-mail]:
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 - <[e-mail]>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!

[Jon Skeet [C# MVP]]

pb <ph**********@hotmail.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 - <sk***@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

[Andrew Morton]

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