DateTime: Huh?

  • Thread starter Thread starter ChrisM
  • Start date Start date
C

ChrisM

Could anyone please tell me the difference between these 2 lines:

myDate = DateTime.Now;

and

myDate = DateTime.Parse(DateTime.Now.ToString("dd/MM/yyyy"));


Seeing as later on in the code, the ExecuteNonQuery works with the first
option, but not with the second. (I get : {"Data type mismatch in criteria
expression." })

Private OleDbCommand cmd;

....

cmd.Parameters.Add("issueDate",myDate
cmd.ExecuteNonQuery();

The command object is a Access update query with a single date parameter.
Connection string is Jet. Command type is 'StoredProcedure'


Please no-one make any comments about the overload of 'Parameter.Add' I'm
using, I know that if I constructed my parameters properly, I wouldn't get
the problem. I'm just interested as to why one version works and the other
doesn't.

Thanks,

ChrisM.
 
ChrisM,

While you asked for no comments about the overload of the Add method, if
you know it will work if you construct the parameters properly, then why not
use that?

That being said, the difference between the first and second line, in
terms of results, should be in the time portion of the value only. The
parsing of the datetime does not yield a value for time in the second line,
while the first line will have the time of day included in it.

Hope this helps.
 
Hi Chris,

ChrisM said:
Could anyone please tell me the difference between these 2 lines:

myDate = DateTime.Now;

and

myDate = DateTime.Parse(DateTime.Now.ToString("dd/MM/yyyy"));
Click to expand...

Besides Nicholas' observations, I will point out that the above will
swap the date and month (assuming your current culture is en-us).
DateTime.Parse will assume a date format of "MM/dd/yyyy", but you are giving
it "dd/MM/yyyy".

Regards,
Dan
 
ChrisM said:
Could anyone please tell me the difference between these 2 lines:

myDate = DateTime.Now;

and

myDate = DateTime.Parse(DateTime.Now.ToString("dd/MM/yyyy"));


Seeing as later on in the code, the ExecuteNonQuery works with the first
option, but not with the second. (I get : {"Data type mismatch in criteria
expression." })

Private OleDbCommand cmd;

...

cmd.Parameters.Add("issueDate",myDate
cmd.ExecuteNonQuery();

The command object is a Access update query with a single date parameter.
Connection string is Jet. Command type is 'StoredProcedure'

Please no-one make any comments about the overload of 'Parameter.Add' I'm
using, I know that if I constructed my parameters properly, I wouldn't get
the problem. I'm just interested as to why one version works and the other
doesn't.
Click to expand...

I suspect it's guessing that the parameter is a date rather than a
datetime, as you'll only have non-zero date information. Just create
the parameter fully though, I'd recommend.
 
Thanks for your comments Guys.

Nicholas: I will construct the parameters properly, I was just interested in
why it wasn't working as I expected.

Cheers,


ChrisM
 
ChrisM said:
Could anyone please tell me the difference between these 2 lines:

myDate = DateTime.Now;

and

myDate = DateTime.Parse(DateTime.Now.ToString("dd/MM/yyyy"));


Seeing as later on in the code, the ExecuteNonQuery works with the first
option, but not with the second. (I get : {"Data type mismatch in criteria
expression." })

Private OleDbCommand cmd;

...

cmd.Parameters.Add("issueDate",myDate
cmd.ExecuteNonQuery();

The command object is a Access update query with a single date parameter.
Connection string is Jet. Command type is 'StoredProcedure'
Click to expand...

Is it because the date is not what the query expects in the second case?

here's what I get:

-- code -----------
DateTime d1 = DateTime.Now;
DateTime d2 = DateTime.Parse(DateTime.Now.ToString("dd/MM/yyyy"));

Console.WriteLine( d1);
Console.WriteLine( d2);

-- output ----------
3/5/2004 10:37:37 AM
5/3/2004 12:00:00 AM
 
ChrisM,
As the others have pointed out, the first includes Date & Time, while the
second includes only a Date.

I would recommend the following instead of the
DateTime.Parse(DateTime.Now...):

myDate = DateTime.Today

or

myDate = DateTime.Now.Date

As you are not susceptible to culture settings where dd/MM/yyyy may yield an
invalid date in foreign countries.

Hope this helps
Jay
 
ChrisM said:
Could anyone please tell me the difference between these 2 lines:

myDate = DateTime.Now;

and

myDate = DateTime.Parse(DateTime.Now.ToString("dd/MM/yyyy"));


Seeing as later on in the code, the ExecuteNonQuery works with the
first option, but not with the second. (I get : {"Data type mismatch
in criteria expression." })

Private OleDbCommand cmd;

...

cmd.Parameters.Add("issueDate",myDate
cmd.ExecuteNonQuery();

The command object is a Access update query with a single date
parameter. Connection string is Jet. Command type is
'StoredProcedure'


Please no-one make any comments about the overload of 'Parameter.Add'
I'm using, I know that if I constructed my parameters properly, I
wouldn't get the problem. I'm just interested as to why one version
works and the other doesn't.

Thanks,

ChrisM.
Click to expand...

IIRC the sql92 standard for date time formats requires that the date is
represented as yyyy/mm/dd (it is the same as ISO 8601), but it is very
common for mm/dd/yyyy to be accepted as well. however the format
dd/mm/yyyy is almost never accepted, even if your international
settings are set to this.

Your second format specifies the exact date format to use, whereas your
first format will return whatever your short date form is in your
settings i.e. in America it will be MM/dd/yyyy. (on my system here in
the UK both return the same)

Cheers Tim.
 
I do also an addition.

If you want to use this, to make it more sure for the globalization you
could add by instance.

Thread.CurrentThread.CurrentCulture = new CultureInfo("nl-NL");
myDate = DateTime.Parse(DateTime.Now.ToString("dd/MM/yyyy"));
Click to expand...

I am curious what Miha and both Willams add to this thread,

:-))

Cor
 
Tim Jarvis said:
IIRC the sql92 standard for date time formats requires that the date is
represented as yyyy/mm/dd (it is the same as ISO 8601), but it is very
common for mm/dd/yyyy to be accepted as well. however the format
dd/mm/yyyy is almost never accepted, even if your international
settings are set to this.

Your second format specifies the exact date format to use, whereas your
first format will return whatever your short date form is in your
settings i.e. in America it will be MM/dd/yyyy. (on my system here in
the UK both return the same)
Click to expand...

No, it doesn't specify the format for sending the DateTime up to the
database in at all - it just specifies the DateTime value itself.
There's no format information implicit in a DateTime.
 
Jon said:
No, it doesn't specify the format for sending the DateTime up to the
database in at all - it just specifies the DateTime value itself.
There's no format information implicit in a DateTime.
Click to expand...

Hi Jon,

Well, that's actually what I thought as well, but try this (or your
local variant)

DateTime MyDate1 = DateTime.Now;
DateTime MyDate2 = DateTime.Parse(DateTime.Now.ToString("MM/dd/yyyy"));
MessageBox.Show(MyDate1 + Environment.NewLine + MyDate2);

this gives me (here in the UK)

07/03/2004 11:39:23
03/07/2004 00:00:00

What I had expected was to see was the same value for both variables,
as they are both DateTimes and both calling the ToString() method, the
metod of creation was different that's all. So it would seem that it
does make a difference how you construct your DateTime variable.

Cheers Tim.
 
Tim Jarvis said:
Hi Jon,

Well, that's actually what I thought as well, but try this (or your
local variant)

DateTime MyDate1 = DateTime.Now;
DateTime MyDate2 = DateTime.Parse(DateTime.Now.ToString("MM/dd/yyyy"));
MessageBox.Show(MyDate1 + Environment.NewLine + MyDate2);

this gives me (here in the UK)

07/03/2004 11:39:23
03/07/2004 00:00:00
Click to expand...

Yes, and so it would - because the second way you've constructed it has
only parsed the date part, and not the time part.
What I had expected was to see was the same value for both variables,
as they are both DateTimes and both calling the ToString() method, the
metod of creation was different that's all. So it would seem that it
does make a difference how you construct your DateTime variable.
Click to expand...

No, it doesn't make any *formatting* difference - it's just that the
way you've constructed the second DateTime doesn't provide it any time
information.

If you have two DateTimes with different amounts of information then
yes, they will display differently. That's not the same as the DateTime
itself having any format information associated with it.
 
Tim Jarvis said:
DateTime MyDate1 = DateTime.Now;
DateTime MyDate2 = DateTime.Parse(DateTime.Now.ToString("MM/dd/yyyy"));
MessageBox.Show(MyDate1 + Environment.NewLine + MyDate2);

this gives me (here in the UK)

07/03/2004 11:39:23
03/07/2004 00:00:00

What I had expected was to see was the same value for
both variables, as they are both DateTimes and both
calling the ToString() method, the metod of creation
was different that's all. So it would seem that it
does make a difference how you construct your
DateTime variable.
Click to expand...

If we split your example a bit, it might show what's really happening:

// First the date is created

DateTime MyDate1 = DateTime.Now;

// You construct a string containing the date
// in the order of month/day/year-parts

string date1String = DateTime.Now.ToString("MM/dd/yyyy");

// date1String now contains "03/07/2004"

// But when you parse it, the Parse-method doesn't know
// in what order the parts are, and believes based on
// your locale that it's in the order day/month/year

DateTime MyDate2 = DateTime.Parse(date1String);

If you want to parse a string into a DateTime, where the string doesn't
follow your locale, you can use one of the overloaded methods for Parse,
where you can use an IFormatProvider.

// Bjorn A
 
Jon said:
Yes, and so it would - because the second way you've constructed it
has only parsed the date part, and not the time part.
Click to expand...

Actually I wasn't thinking about the time portion I was thinking about
the dd and MM portions, but rather stupidly forgot that 03/07/2004 and
07/03/2004 are both valid dates, and in my UK settings are
(respectively) 3rd July and 7th March. My second line was simply
creating a different date from portions of the first date, and was just
lucky not to barf.

I think that the original poster is doing the same thing, i.e.
constructing a DateTime variable using a format that is different from
the internationalization format.

Cheers Tim.
 
Tim Jarvis said:
I think that the original poster is doing the same thing, i.e.
constructing a DateTime variable using a format that is different from
the internationalization format.
Click to expand...

No, because (as I said before) the DateTime itself doesn't have any
format associated with it at all. It's just a date and time - except
that in his case, the second version doesn't really have any time
information.

Note that the problem doesn't come at the parsing stage (as far as the
OP indicated, anyway) - it comes when the DateTime is being used as a
parameter value.
 
Jon said:
No, because (as I said before) the DateTime itself doesn't have any
format associated with it at all. It's just a date and time - except
that in his case, the second version doesn't really have any time
information.
Click to expand...

Yeah, you are right.

My blond moment has passed, I see the light.

:-)

Cheers Tim.
 
Back
Top