DateTime Problems

[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?

 

[Guest]

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?

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

 

[Mark Rae]

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

Or, even better, insert '08 Mar 2006' and you'll *never* have a problem
after that...

 

[rn5a]

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 -

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?

 

[Guest]

Or, even better, insert '08 Mar 2006' and you'll *never* have a problem
after that...

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

 

[Mark Rae]

Or, even better, insert '08 Mar 2006' and you'll *never* have a problem
after that...

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

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

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

The ISO 8601 format (yyyy-MM-dd) is also unambigous, and also it's
culture independent.

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

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?

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

 

[Mark Rae]

The ISO 8601 format (yyyy-MM-dd) is also unambigous, and also it's culture
independent.

No it isn't...

What date is 2007-08-03?

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

No it isn't...

Yes, it is...

What date is 2007-08-03?

That is the third of august in the year 2007.

 

[Mark Rae]

That is the third of august in the year 2007.

How do you know...?

 

[Guest]

How do you know...?

Göran is right. In my first answer I suggested to use ISO 8601 to be
sure which date is were inserted.

 

[Mark Rae]

Göran is right. In my first answer I suggested to use ISO 8601 to be
sure which date is were inserted.

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

 

[Guest]

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

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

 

[Mark Rae]

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

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

 

[Juan T. Llibre]

re:

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

Indeed, using yyyy-mm-dd, it stands for March 8, 2007.

 

[Guest]

.Parameters.AddWithValue("?", DateTime.Now.ToString)

Maybe

..Parameters.AddWithValue("?", Format(DateTime.Now, "yyyy-MM-dd"))

will work?

 

[Guest]

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

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

 

[Mark Rae]

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.

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

 

[Guest]

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

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.