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]]

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.

 

[pb]

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.

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

 

[mpetrotta]

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.

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]

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]

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.

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]]

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.

 

[Andrew Morton]

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