DateTime Problems

  • Thread starter Thread starter rn5a
  • Start date Start date
R

rn5a

The date in my local machine is set to the dd/MM/yyyy format. When I
insert a date in a MS-Access DB table, it gets populated in the above
format. For e.g. if the date is, say, 8th March 2007, it gets
populated in the DB table as

08/03/2007

In other words, first the day is shown, then the month & finally the
year but when I retrieve it in ASP.NET & using DatePart, try to
extract the day & the month like this (assume that the date record
from the DB table is stored in a variable named dtOrderDate)

Response.Write("Day: " & DatePart("d", dtOrderDate))
Response.Write("Month: " & DatePart("m", dtOrderDate))

the first Response.Write outputs the day as 03 & the month as 08 where
as it should be the other way round i.e. the day should be 08 whereas
the month should be 03. The day & month values get reversed when I
just do Response.Write(dtOrderDate).

Can someone please point out what am I missing?
 
The date in my local machine is set to the dd/MM/yyyy format. When I
insert a date in a MS-Access DB table, it gets populated in the above
format. For e.g. if the date is, say, 8th March 2007, it gets
populated in the DB table as

08/03/2007

In other words, first the day is shown, then the month & finally the
year but when I retrieve it in ASP.NET & using DatePart, try to
extract the day & the month like this (assume that the date record
from the DB table is stored in a variable named dtOrderDate)

Response.Write("Day: " & DatePart("d", dtOrderDate))
Response.Write("Month: " & DatePart("m", dtOrderDate))

the first Response.Write outputs the day as 03 & the month as 08 where
as it should be the other way round i.e. the day should be 08 whereas
the month should be 03. The day & month values get reversed when I
just do Response.Write(dtOrderDate).

Can someone please point out what am I missing?
Click to expand...

I suppose, it means the date in the database is wrong? How do you
insert the date in the database (sql query format)? Try to insert
'2006-03-08' and see what happen...
 
I suppose, it means the date in the database is wrong? How do you
insert the date in the database (sql query format)? Try to insert
'2006-03-08' and see what happen...
Click to expand...

Or, even better, insert '08 Mar 2006' and you'll *never* have a problem
after that...
 
I suppose, it means the date in the database is wrong? How do you
insert the date in the database (sql query format)? Try to insert
'2006-03-08' and see what happen...- Hide quoted text -

- Show quoted text -
Click to expand...

Alexey, this is how I am inserting records in the Access DB table:

strSQL = "INSERT INTO CustomerDetails (CName, Mail, Address,
OrderDate) VALUES (?, ?, ?, ?)"

oledbCmd = New OleDbCommand(strSQL, oledbConn)

With oledbCmd
.Parameters.AddWithValue("?", strName)
.Parameters.AddWithValue("?", strEMail)
.Parameters.AddWithValue("?", strAddress)
.Parameters.AddWithValue("?", DateTime.Now.ToString)
End With

Note that I am casting the date (which is the last parameter) into
string. If I just use DateTime.Now (the data type of the column in the
DB table is Date/Time), then ASP.NET generates an error saying Data
type mismatch in criteria expression.

Any other ideas/suggestions?
 
Or, even better, insert '08 Mar 2006' and you'll *never* have a problem
after that...
Click to expand...

This format dependent on the system settings.
For example, with German locale it may not work (Mär).
 
Or, even better, insert '08 Mar 2006' and you'll *never* have a problem
after that...
Click to expand...

This format dependent on the system settings.
For example, with German locale it may not work (Mär).

Indeed but, in Germany, it would be '08 Mär 2007'

The point is that the only truly unambiguous date format is four digit year
and three digit month...
 
Mark said:
This format dependent on the system settings.
For example, with German locale it may not work (Mär).

Indeed but, in Germany, it would be '08 Mär 2007'

The point is that the only truly unambiguous date format is four digit year
and three digit month...
Click to expand...

The ISO 8601 format (yyyy-MM-dd) is also unambigous, and also it's
culture independent.
 
Alexey, this is how I am inserting records in the Access DB table:

strSQL = "INSERT INTO CustomerDetails (CName, Mail, Address,
OrderDate) VALUES (?, ?, ?, ?)"

oledbCmd = New OleDbCommand(strSQL, oledbConn)

With oledbCmd
.Parameters.AddWithValue("?", strName)
.Parameters.AddWithValue("?", strEMail)
.Parameters.AddWithValue("?", strAddress)
.Parameters.AddWithValue("?", DateTime.Now.ToString)
End With

Note that I am casting the date (which is the last parameter) into
string. If I just use DateTime.Now (the data type of the column in the
DB table is Date/Time), then ASP.NET generates an error saying Data
type mismatch in criteria expression.

Any other ideas/suggestions?
Click to expand...

As you are using parameters, there should be no problem with the date
format. The fact that you are converting the date to a string is causing
the problem.

You should specify the data type for the parameters:

..Parameters.Add("?", OleDbType.VarChar).Value = name
..Parameters.Add("?", OleDbType.VarChar).Value = email
..Parameters.Add("?", OleDbType.VarChar).Value = address
..Parameters.Add("?", OleDbType.Date).Value = DateTime.Now
 
Göran is right. In my first answer I suggested to use ISO 8601 to be
sure which date is were inserted.
Click to expand...

But that's the whole point - you can't be sure with this notation...

The fact that your code uses one particular format is neither here nor
there... The issue here is the word "inserted" i.e. typed in by a user...

Just because 2007-08-03 means 3rd August 2007 to you, doesn't imply that it
means the same to somebody else...

The only 100% guaranteed unambiguous method of date entry where users are
involved is to force three digit months and four digit years...
 
But that's the whole point - you can't be sure with this notation...

The fact that your code uses one particular format is neither here nor
there... The issue here is the word "inserted" i.e. typed in by a user...

Just because 2007-08-03 means 3rd August 2007 to you, doesn't imply that it
means the same to somebody else...

The only 100% guaranteed unambiguous method of date entry where users are
involved is to force three digit months and four digit years...
Click to expand...


ISO 8601 is language independent
http://www.iso.org/iso/en/prods-services/popstds/datesandtime.html
 
ISO 8601 is language independent
http://www.iso.org/iso/en/prods-services/popstds/datesandtime.html
Click to expand...

Sigh...

OK, now watch...

You, the very clever developer, develop a very clever website where users
can register to receive a "Happy Birthday" email on their birthday.

You, the very clever developer, make sure that the textbox where the user
types in their date of birth uses the totally unambiguous industry-standard
ISO 8601 format.

I, the dumb user, come along and register on your site.

And I'm a *really* dumb user, so I don't pay attention to the writing beside
the textbox which tells me the date format that you, the very clever
developer, would like me to use - in fact, I'm such a dumb user that I don't
even understand what all that technical mumbo-jumbo means...

I, the dumb user, was born 12th March 1970.

However, I don't come from the same country as you - I come from a little
country that you, the very clever developer, have never even heard of...

So, I enter the following text:

1970-12-03

because that's how we format our dates in that little country that you, the
very clever developer, have never heard of...

I, the dumb user, hit the Save button.

You, the very clever developer, have written a superb validation routine
which examines the text that I, the dumb user, typed in. This validation
routine says "Yep, 1970-12-03 is a perfectly valid date" and permits my
registration to be saved to the database.

I, the dumb user, wonder why you, the very clever developer, are wishing me
a happy birthday in the first week of December...

*Now* do you get it...?
 
Sigh...

OK, now watch...

You, the very clever developer, develop a very clever website where users
can register to receive a "Happy Birthday" email on their birthday.

You, the very clever developer, make sure that the textbox where the user
types in their date of birth uses the totally unambiguous industry-standard
ISO 8601 format.

I, the dumb user, come along and register on your site.

And I'm a *really* dumb user, so I don't pay attention to the writing beside
the textbox which tells me the date format that you, the very clever
developer, would like me to use - in fact, I'm such a dumb user that I don't
even understand what all that technical mumbo-jumbo means...

I, the dumb user, was born 12th March 1970.

However, I don't come from the same country as you - I come from a little
country that you, the very clever developer, have never even heard of...

So, I enter the following text:

1970-12-03

because that's how we format our dates in that little country that you, the
very clever developer, have never heard of...

I, the dumb user, hit the Save button.

You, the very clever developer, have written a superb validation routine
which examines the text that I, the dumb user, typed in. This validation
routine says "Yep, 1970-12-03 is a perfectly valid date" and permits my
registration to be saved to the database.

I, the dumb user, wonder why you, the very clever developer, are wishing me
a happy birthday in the first week of December...

*Now* do you get it...?
Click to expand...

As for the format in the international applications, using the textbox
to enter the date, is not a good idea at all. The very clever
developer will use a calendar control or a separated fields for day,
month and year, then the user is never entering a date by hand, and
you can control the format throughout the process.

Here's the problem in Access/OleDb and not in the validation design...
 
As for the format in the international applications, using the textbox
to enter the date, is not a good idea at all. The very clever
developer will use a calendar control or a separated fields for day,
month and year, then the user is never entering a date by hand, and
you can control the format throughout the process.
Click to expand...

Well obviously, but that's not what we're discussing here, is it...?
 
Well obviously, but that's not what we're discussing here, is it...?
Click to expand...

Nope, here we have a totally different story.

Imagine, you have a Control Calendar on the page and you selected a
date, say, 8th March 2007.

Because your server is in UK you will get the string representation
Calendar.SelectedDate.ToString as

08/03/2007 (dd/MM/yyyy)

Note, this has nothing to do with the manual insert, it's a local date
representation acc. to site/server settings.

Now, try to insert this string to MS Access using OleDb:

oledbCmd.Parameters.AddWithValue("?", Calendar.SelectedDate.ToString)

Because OleDb expected to have yyyy-MM-yy, or at least MM/dd/yyyy, it
will get the date as 3rd Aug 2007...

So, the problem is here not in the original date, but in the date
format used by OleDb and MS Access.
 
Back
Top