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?
24  1988 
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...
"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...
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?
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).
"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...
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 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
"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?
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
"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...?
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.
"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...
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
"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...?
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...
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?
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...
"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...?
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.
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
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
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
"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... :-)
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Symon R |
last post by:
This is a bit of a weird one that I haven't yet been able to solve - I'm
hoping someone out there can disprove my findings and tell me where I've
gone wrong!
I have designed a web service that...
|
by: Chris |
last post by:
Hello,
I'm having some problems right now with something that would seem to be
rather simple (and probably is). But, the solution is escaping me right
now.
Currently, I have created a data...
|
by: Kevin |
last post by:
Hi All
I am having a problem retrieving a Date value from an
Access 2000 database using the OLEDbProvider. Can someone
tell me please how to create a DateTime Object with the
date fro m the...
|
by: nobody |
last post by:
I know that given a FormatString and a DateTime you can use
DateTime.ToString(...) to convert the DateTime to a String. My question is
how can you turn that around? Given a String and a...
|
by: Cor Ligthert |
last post by:
Hello everybody,
Jay and Herfried are telling me every time when I use CDate that using the
datetime.parseexact is always the best way to do String to datetime
conversions. They don't tell why...
|
by: Ugur Ekinci |
last post by:
Hi ,
I have two Sql Server 2000 on seperate machines ,
First one accepts datetime format like ("dd.MM.yyyy hh:mm:ss")
And Second one accepts datetime format like ("MM.dd.yyyy hh:mm:ss")
1-...
|
by: H5N1 |
last post by:
Hi there
I know that a problem of different datetime strings formatting between
asp.net and ms sql has been covered here widely, but what I couldn't
find is the 100% safe way of getting datetime...
|
by: iulian.ilea |
last post by:
Is correct to have a varchar field and insert dates of type dd/mm/yyyy
into it? I choose this method because I have an application that runs
on more than one server. So, if I used a datetime field...
|
by: Michel Posseth [MCP] |
last post by:
Hello we have encountered the following problems with the date time picker
control
A : datetime picker control gives focus to last entered field when moving
back and forward with focus how do...
|
by: Jeff |
last post by:
Hey
..NET 2.0
I'm about to create 2 input parameters for a method. These are 2 DateTime
parameters - one named "from" and another named "to"... - from and to
DateTime values...
Okay the...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |
