By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,281 Members | 2,268 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,281 IT Pros & Developers. It's quick & easy.

DateTime Problems

P: n/a
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?

Mar 10 '07 #1
Share this Question
Share on Google+
24 Replies


P: n/a
On Mar 10, 5:03 pm, r...@rediffmail.com wrote:
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...

Mar 10 '07 #2

P: n/a
"Alexey Smirnov" <al************@gmail.comwrote in message
news:11**********************@v33g2000cwv.googlegr oups.com...
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...
Mar 10 '07 #3

P: n/a
On Mar 10, 9:10 pm, "Alexey Smirnov" <alexey.smir...@gmail.comwrote:
On Mar 10, 5:03 pm, r...@rediffmail.com wrote:


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

Mar 10 '07 #4

P: n/a
On Mar 10, 5:18 pm, "Mark Rae" <m...@markNOSPAMrae.comwrote:
"Alexey Smirnov" <alexey.smir...@gmail.comwrote in message
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).

Mar 10 '07 #5

P: n/a
"Alexey Smirnov" <al************@gmail.comwrote in message
news:11**********************@j27g2000cwj.googlegr oups.com...

On Mar 10, 5:18 pm, "Mark Rae" <m...@markNOSPAMrae.comwrote:
"Alexey Smirnov" <alexey.smir...@gmail.comwrote in message
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...
Mar 10 '07 #6

P: n/a
Mark Rae wrote:
"Alexey Smirnov" <al************@gmail.comwrote in message
news:11**********************@j27g2000cwj.googlegr oups.com...

On Mar 10, 5:18 pm, "Mark Rae" <m...@markNOSPAMrae.comwrote:
>"Alexey Smirnov" <alexey.smir...@gmail.comwrote in message
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...
The ISO 8601 format (yyyy-MM-dd) is also unambigous, and also it's
culture independent.

--
Göran Andersson
_____
http://www.guffa.com
Mar 11 '07 #7

P: n/a
rn**@rediffmail.com wrote:
On Mar 10, 9:10 pm, "Alexey Smirnov" <alexey.smir...@gmail.comwrote:
>On Mar 10, 5:03 pm, r...@rediffmail.com wrote:


>>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...- 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?
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 Andersson
_____
http://www.guffa.com
Mar 11 '07 #8

P: n/a
"Göran Andersson" <gu***@guffa.comwrote in message
news:%2****************@TK2MSFTNGP05.phx.gbl...
>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.
No it isn't...

What date is 2007-08-03?
Mar 11 '07 #9

P: n/a
Mark Rae wrote:
"Göran Andersson" <gu***@guffa.comwrote in message
news:%2****************@TK2MSFTNGP05.phx.gbl...
>>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.

No it isn't...
Yes, it is...
What date is 2007-08-03?
That is the third of august in the year 2007.

--
Göran Andersson
_____
http://www.guffa.com
Mar 11 '07 #10

P: n/a
"Göran Andersson" <gu***@guffa.comwrote in message
news:%2***************@TK2MSFTNGP03.phx.gbl...
>What date is 2007-08-03?

That is the third of august in the year 2007.
How do you know...?
Mar 11 '07 #11

P: n/a
On Mar 11, 9:31 am, "Mark Rae" <m...@markNOSPAMrae.comwrote:
"Göran Andersson" <g...@guffa.comwrote in message

news:%2***************@TK2MSFTNGP03.phx.gbl...
What date is 2007-08-03?
That is the third of august in the year 2007.

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.

Mar 11 '07 #12

P: n/a
"Alexey Smirnov" <al************@gmail.comwrote in message
news:11********************@p10g2000cwp.googlegrou ps.com...
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...
Mar 11 '07 #13

P: n/a
On Mar 11, 10:47 am, "Mark Rae" <m...@markNOSPAMrae.comwrote:
"Alexey Smirnov" <alexey.smir...@gmail.comwrote in message

news:11********************@p10g2000cwp.googlegrou ps.com...
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...

ISO 8601 is language independent
http://www.iso.org/iso/en/prods-serv...esandtime.html

Mar 11 '07 #14

P: n/a
"Alexey Smirnov" <al************@gmail.comwrote in message
news:11**********************@8g2000cwh.googlegrou ps.com...
ISO 8601 is language independent
http://www.iso.org/iso/en/prods-serv...esandtime.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...?
Mar 11 '07 #15

P: n/a
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.


Juan T. Llibre, asp.net MVP
asp.net faq : http://asp.net.do/faq/
foros de asp.net, en español : http://asp.net.do/foros/
===================================
"Mark Rae" <ma**@markNOSPAMrae.comwrote in message news:ua*************@TK2MSFTNGP02.phx.gbl...
"Alexey Smirnov" <al************@gmail.comwrote in message
news:11********************@p10g2000cwp.googlegrou ps.com...
>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...

Mar 11 '07 #16

P: n/a
On Mar 10, 5:29 pm, r...@rediffmail.com wrote:
.Parameters.AddWithValue("?", DateTime.Now.ToString)
Maybe

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

will work?
Mar 11 '07 #17

P: n/a
On Mar 11, 12:09 pm, "Mark Rae" <m...@markNOSPAMrae.comwrote:
"Alexey Smirnov" <alexey.smir...@gmail.comwrote in message

news:11**********************@8g2000cwh.googlegrou ps.com...
ISO 8601 is language independent
http://www.iso.org/iso/en/prods-serv...esandtime.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...?
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...

Mar 11 '07 #18

P: n/a
"Alexey Smirnov" <al************@gmail.comwrote in message
news:11**********************@8g2000cwh.googlegrou ps.com...
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...?
Mar 11 '07 #19

P: n/a
On Mar 11, 9:08 pm, "Mark Rae" <m...@markNOSPAMrae.comwrote:
"Alexey Smirnov" <alexey.smir...@gmail.comwrote in message

news:11**********************@8g2000cwh.googlegrou ps.com...
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...?
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.

Mar 11 '07 #20

P: n/a
Mark Rae wrote:
"Göran Andersson" <gu***@guffa.comwrote in message
news:%2***************@TK2MSFTNGP03.phx.gbl...
>>What date is 2007-08-03?
That is the third of august in the year 2007.

How do you know...?
Because I have accuired knowledge about the ISO 8601 format. Also, I
live in one of the few countries in the world that is actually using the
international standard for date format as the standard date format, so I
am very used to reading dates in this format.

--
Göran Andersson
_____
http://www.guffa.com
Mar 11 '07 #21

P: n/a
Mark Rae wrote:
"Alexey Smirnov" <al************@gmail.comwrote in message
news:11********************@p10g2000cwp.googlegrou ps.com...
>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...
Yes, you can. Unless you invent your own date format, there is no way
that you can interpret it any other way.
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...
But it's not typed in by a user at all. It's created by converting the
DateTime.Now value into a string.
Just because 2007-08-03 means 3rd August 2007 to you, doesn't imply that it
means the same to somebody else...
Either they know the date format so that they know what it means, or
they don't so it doesn't mean anything at all.
The only 100% guaranteed unambiguous method of date entry where users are
involved is to force three digit months and four digit years...
Not at all. There are many ways of entering a date. But the question is
still not about user input, it's about date formats.

--
Göran Andersson
_____
http://www.guffa.com
Mar 11 '07 #22

P: n/a
Alexey Smirnov wrote:
On Mar 10, 5:29 pm, r...@rediffmail.com wrote:
>.Parameters.AddWithValue("?", DateTime.Now.ToString)

Maybe

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

will work?

Perhaps. But it's better to let the database driver handle the date
instead of trying to create a string in a format that it will understand.

--
Göran Andersson
_____
http://www.guffa.com
Mar 11 '07 #23

P: n/a
"Göran Andersson" <gu***@guffa.comwrote in message
news:eH**************@TK2MSFTNGP03.phx.gbl...
Because I have accuired knowledge about the ISO 8601 format.
Just like all your users, no doubt...?
Also, I live in one of the few countries in the world that is actually
using the international standard for date format as the standard date
format, so I am very used to reading dates in this format.
So, your apps can be used reliably only by people who have your level of
knowledge of ISO standards, and who have the good fortune to live in the
same country as you...?

It's a big old world, you know... :-)
Mar 12 '07 #24

P: n/a
Mark Rae wrote:
"Göran Andersson" <gu***@guffa.comwrote in message
news:eH**************@TK2MSFTNGP03.phx.gbl...
>Because I have accuired knowledge about the ISO 8601 format.

Just like all your users, no doubt...?
>Also, I live in one of the few countries in the world that is actually
using the international standard for date format as the standard date
format, so I am very used to reading dates in this format.

So, your apps can be used reliably only by people who have your level of
knowledge of ISO standards, and who have the good fortune to live in the
same country as you...?

It's a big old world, you know... :-)
This is not a discussion of user input, but of date formats. The data in
this case does not come from user input.

If you want to discuss user input, that is a completely different
business. Then the ISO 8601 format is of course only useful in countries
where people use that format.

Sometimes entering a date as a formatted string isn't even an option,
but other methods to select a date has to be used.

--
Göran Andersson
_____
http://www.guffa.com
Mar 12 '07 #25

This discussion thread is closed

Replies have been disabled for this discussion.