mm/dd/yyyy format question

[Roy]

Hi all,

I'm creating a project that should always use this date format when displays
the dates or create dates. The back end database is a SQL Server and I like
to know what is the logical way to configure server, sql server or program so
it always deals with date as mm/dd/yyyy format.
Thanks in advance.

Roy

[Armin Zingler]

"Roy" <Ro*@discussions.microsoft.com> schrieb

I'm creating a project that should always use this date format when
displays the dates or create dates. The back end database is a SQL
Server and I like to know what is the logical way to configure
server, sql server or program so it always deals with date as
mm/dd/yyyy format.
Thanks in advance.

It depends on how you display and enter dates. If you manually convert from
DateTime to String, use the format "MM\/dd\/yyyy". If you use databound
controls, I'm not sure because I never use them. I only know that there is
the System.Windows.Forms.Binding.Format event that can be used for
formatting.

If the user enters dates, and if you want to force this input format, you
can use Date.ParseExact.

Concerning sql server: If you use parameters with SqlCommands, you simply
set the value of the parameter. The SqlCommand does the correct formatting
for you.

See also:
http://msdn.microsoft.com/library/en...ttingtypes.asp
(especially "Date and Time Format Strings")

Though, I would prefer an application using my regional settings, i.e.
displaying and accepting the date in regional format.
Armin

[Cor Ligthert [MVP]]

Roy,

In addition to Armin,

Be aware that if you would use your application in the North America
continent where this format is used 1/12/2005 would mean in Canada 1th
December 2005

Have a pleasure time in future with correcting the problems if you boss
decide that they will have a client/location in Canada and tell nothing to
you.

Cor

[LDD]

I'd also suggest using the regional settings. It makes the app much more
robust.
I recently ran into this particular issue, and am in the process of changing
things to use the regional settings

LDD

"Armin Zingler" <az*******@freenet.de> wrote in message
news:%2***************@TK2MSFTNGP11.phx.gbl...

"Roy" <Ro*@discussions.microsoft.com> schrieb

I'm creating a project that should always use this date format when
displays the dates or create dates. The back end database is a SQL
Server and I like to know what is the logical way to configure
server, sql server or program so it always deals with date as
mm/dd/yyyy format.
Thanks in advance.
It depends on how you display and enter dates. If you manually convert

from DateTime to String, use the format "MM\/dd\/yyyy". If you use databound
controls, I'm not sure because I never use them. I only know that there is
the System.Windows.Forms.Binding.Format event that can be used for
formatting.

If the user enters dates, and if you want to force this input format, you
can use Date.ParseExact.

Concerning sql server: If you use parameters with SqlCommands, you simply
set the value of the parameter. The SqlCommand does the correct formatting
for you.

See also:
http://msdn.microsoft.com/library/en...ttingtypes.asp (especially "Date and Time Format Strings")

Though, I would prefer an application using my regional settings, i.e.
displaying and accepting the date in regional format.
Armin

[Roy]

Thanks everyone for your help.

"Cor Ligthert [MVP]" wrote:

Roy,

In addition to Armin,

Be aware that if you would use your application in the North America
continent where this format is used 1/12/2005 would mean in Canada 1th
December 2005

Have a pleasure time in future with correcting the problems if you boss
decide that they will have a client/location in Canada and tell nothing to
you.

Cor

[Joergen Bech]

On Tue, 20 Dec 2005 13:47:02 -0800, Roy
<Ro*@discussions.microsoft.com> wrote:

Hi all,

I'm creating a project that should always use this date format when displays
the dates or create dates. The back end database is a SQL Server and I like
to know what is the logical way to configure server, sql server or program so
it always deals with date as mm/dd/yyyy format.
Thanks in advance.

Roy

Use the ISO 8601 format when passing date information as a string to
SQL server, i.e. "YYYYMMDD".

No ambiguity. No language dependence. No regional nonsense.

For more information, see
http://support.microsoft.com/default...b;en-us;173907

Another interesting article about SQL Server date handling:
http://www.sqljunkies.com/Article/66...C7FD826E5.scuk

More info about ISO 8601:
http://www.cl.cam.ac.uk/~mgk25/iso-time.html

As another poster replied, you should not presume that the
system is configured to handle dates formatted in any
particular manner. Specific (localized) formats should be reserved for
input validation and output representation.

Hope any of this helps.

Regards,

Joergen Bech

[Cor Ligthert [MVP]]

Joergen,

This is a VB.Net newsgroup not an SQL language newsgroup.

Why would you do that for an SQLSever which notate its dateTime in ticks and
for what has .Net very automatic localized methods?

To explain you more.
ISO8601 is used in Asia (not all countries)
dd-mm-yyyy is used in Europe/Africa/Australia/America with the exception of
USA and Mexico
mm-dd-yyyy is used in the USA and Mexico (from the last I am not even sure)

However with Net there is no need to think about that when you use the
settings of the OS (that is by doing nothing extra)

(If you use direct SQLServer without Net than you have to make a choose,
however that is not the situation).

In cases beside Net my preference is ISO8601 by the way.

Cor

"Joergen Bech @ post1.tele.dk>" <jbech<NOSPAMNOSPAM> schreef in bericht
news:fn********************************@4ax.com...

On Tue, 20 Dec 2005 13:47:02 -0800, Roy
<Ro*@discussions.microsoft.com> wrote:

Hi all,

I'm creating a project that should always use this date format when
displays
the dates or create dates. The back end database is a SQL Server and I
like
to know what is the logical way to configure server, sql server or program
so
it always deals with date as mm/dd/yyyy format.
Thanks in advance.

Roy

Use the ISO 8601 format when passing date information as a string to
SQL server, i.e. "YYYYMMDD".
For more information, see
http://support.microsoft.com/default...b;en-us;173907

Another interesting article about SQL Server date handling:
http://www.sqljunkies.com/Article/66...C7FD826E5.scuk

More info about ISO 8601:
http://www.cl.cam.ac.uk/~mgk25/iso-time.html

As another poster replied, you should not presume that the
system is configured to handle dates formatted in any
particular manner. Specific (localized) formats should be reserved for
input validation and output representation.

Hope any of this helps.

Regards,

Joergen Bech

[Joergen Bech]

The original question stated
"what is the logical way to configure server, sql server or program so
it always deals with date as mm/dd/yyyy format".

And I also said that I recommended the ISO approach when
"passing date information as a string" (I use it myself and have
never looked back).

I know that .Net takes care of all this provided that one goes
through the proper objects.

I guess my head was still in my last project where I bypassed
those objects in favor of building my sql inserts from scratch
for performance reasons.

Whatever the programming language or supporting constructs,
I still feel the links I provided are relevant to the question.

ISO 8601 specific to Asian countries? This is the first time I
have heard that. Can you provide me with some links to some
more information? As the article in the Microsoft link stated,
the ISO format is unambiguous to SQL Server and is recommended
for that reason, regardless of the locale settings or country.

I only mentioned the ISO format because other posters started
talking about mm-dd-yyyy and dd-mm-yyyy.

If the original question had been about what .Net classes/objects to
use in order to pass date information correctly to and from SQL
Server, I suppose we would have had an entirely different
discussion.

My apologies if I have misunderstood something.

Regards,

Joergen Bech

On Wed, 21 Dec 2005 17:05:58 +0100, "Cor Ligthert [MVP]"
<no************@planet.nl> wrote:

Joergen,

This is a VB.Net newsgroup not an SQL language newsgroup.

Why would you do that for an SQLSever which notate its dateTime in ticks and
for what has .Net very automatic localized methods?

To explain you more.
ISO8601 is used in Asia (not all countries)
dd-mm-yyyy is used in Europe/Africa/Australia/America with the exception of
USA and Mexico
mm-dd-yyyy is used in the USA and Mexico (from the last I am not even sure)

However with Net there is no need to think about that when you use the
settings of the OS (that is by doing nothing extra)

(If you use direct SQLServer without Net than you have to make a choose,
however that is not the situation).

In cases beside Net my preference is ISO8601 by the way.

Cor

"Joergen Bech @ post1.tele.dk>" <jbech<NOSPAMNOSPAM> schreef in bericht
news:fn********************************@4ax.com.. .

On Tue, 20 Dec 2005 13:47:02 -0800, Roy
<Ro*@discussions.microsoft.com> wrote:

Hi all,

I'm creating a project that should always use this date format when
displays
the dates or create dates. The back end database is a SQL Server and I
like
to know what is the logical way to configure server, sql server or program
so
it always deals with date as mm/dd/yyyy format.
Thanks in advance.

Roy

Use the ISO 8601 format when passing date information as a string to
SQL server, i.e. "YYYYMMDD".
For more information, see
http://support.microsoft.com/default...b;en-us;173907

Another interesting article about SQL Server date handling:
http://www.sqljunkies.com/Article/66...C7FD826E5.scuk

More info about ISO 8601:
http://www.cl.cam.ac.uk/~mgk25/iso-time.html

As another poster replied, you should not presume that the
system is configured to handle dates formatted in any
particular manner. Specific (localized) formats should be reserved for
input validation and output representation.

Hope any of this helps.

Regards,

Joergen Bech

[Cor Ligthert [MVP]]

Joergen,

The original question stated
"what is the logical way to configure server, sql server or program so
it always deals with date as mm/dd/yyyy format".
The SQL server nor a Net program do deal with a string format. Thet deal
with different ticks.
SQL server has ticks from 1000/3 millisecond starting at 1-1-1753 (for the
DateTime)
Net deals with ticks from a unit of hundred nanosecond starting at 1-1-1
And I also said that I recommended the ISO approach when
"passing date information as a string" (I use it myself and have
never looked back).

I did as well however Net has better methods. By using direct the internal
DateTime and only use the string when it is needed to show it on screen or
get it from the sceen.
I know that .Net takes care of all this provided that one goes
through the proper objects.

ISO 8601 specific to Asian countries? This is the first time I
have heard that. Can you provide me with some links to some
more information?
That is not so strange as you think that they don't use European languages.
The former communist countries have used it a while, however that is as well
gone again. I think that the Britans will drive earlier on the right lane
than ISO8601 is used in Europe. China started the Georgian calendar late in
1949 and Turkey in 1928. Most Islamic countries (not all see Turkey) have of
course there own calendar (Isreal as well an hebrewic). Therefore the main
ISO8601 countries are India and China. From other Asian countries I am not
sure what they use.

Searching for Calendar and ISO8601 in Google gives you a bunch of
information. So wants the state of Nevada only use ISO8601 in official
websites.
As the article in the Microsoft link stated,
the ISO format is unambiguous to SQL Server and is recommended
for that reason, regardless of the locale settings or country.
The article is clear for me from a probably SQL part of Microsoft, not from
the Net or the OS part. How would you look to it if it was writen by
Navision/Microsoft.
I only mentioned the ISO format because other posters started
talking about mm-dd-yyyy and dd-mm-yyyy.
That I understood, however those dates have given often misunderstandings,
while it is so fine done in Net.
If the original question had been about what .Net classes/objects to
use in order to pass date information correctly to and from SQL
Server, I suppose we would have had an entirely different
discussion.

The way as Armin wrote it was in my opinon clear for the question. That was
why I did not add anything beside the warning to it, while I mostly do that
in DateTime questions.

(In my opinion is it a pity that the VBNet IDE shows all dateTimes in USA
format, whatever it internal is. C# shows it in the culture format from the
system, my favorite would be as it was showed in ISO8601 in all IDE's,
however that is noted as point for the future)

I hope that you now understand why I gave a reply on your message.

Cor

[adh]

Good people, Look!
A simple daily question and 9 posts AND NO ANSWER!!!
All the heck with VS.NET and we are back at stone age to deal with the
master's creation date !!!
Microsoft PLEASEEEEEEEEEEEEE.....................

Thanks, adh

*** Sent via Developersdex http://www.developersdex.com ***

[Joergen Bech]

I thought about just letting this one die, but ...

1. Internal/external date/time representations:
----------------------------------------------------------------------
I was not talking about how .Net or SQL Server stores date/time values
internally. Only how those values are formatted in the twilight zone
between applications so they could be interpreted unambiguously.

2. ISO 8601 usage:
----------------------------------------------------------------------
I get the impression that you are talking about ISO 8601 in terms
of what areas of the world uses the format for human-readable
date/time information. Again, I am only concerned about where
applications use the format for unambiguously representing date/time
values across interfaces.

Main entry: http://en.wikipedia.org/wiki/ISO_8601
Some adopters of the format: http://www.qsl.net/g1smd/isoimp.htm
Campaign: http://www.saqqara.demon.co.uk/datefmt.htm
....and all the other links from the Wikipedia article.

In .Net, the ISO 8601 format is used when serializing date/time values
to XML. No specific settings. This is the default format.

Going outside Microsoft:

MySQL: http://www.php-faq.de/q/q-datum-mysql.html
"MySQL verarbeitet Datumsangaben im ISO-8601-Format (siehe die
Abhandlung von Markus Kuhn zu diesem Thema). Dies ist das offizielle
deutsche Datumsformat, eine Umwandlung ist nicht notwendig, weil nicht
normgerecht."
Support was broken for a short while, but has been fixed now:
http://bugs.mysql.com/bug.php?id=7308

XML Schema: http://www.w3.org/TR/xmlschema-2/#isoformats

Oracle seems to happy to work with the ISO 8601 format:
http://www.oracle.com/technology/pro...TimeRange.html

IBM:
http://www-128.ibm.com/developerwork...le/dm-0510roy/

Atom specification:
http://www.atomenabled.org/developer...ormat-spec.php

....and I could Google all day long.

So my question to you (Cor) is: If you send a query to SQL Server (or
another database), how do you format your date/time values, e.g. if
the query goes something like
"Select <fields> From Orders Where OrderDate >= '<somedate>'",
how would you write the '<somedate>' portion to ensure that it
succeeds, regardless of client/server configuration?

*That* is what I have been talking about all along.

If you were talking about something else, ok, we have two
different discussions. If the same, let's just agree to disagree.

Merry Christmas 'n'all!

Joergen Bech

On Wed, 21 Dec 2005 18:54:33 +0100, "Cor Ligthert [MVP]"
<no************@planet.nl> wrote:

Joergen,

The original question stated
"what is the logical way to configure server, sql server or program so
it always deals with date as mm/dd/yyyy format".

The SQL server nor a Net program do deal with a string format. Thet deal
with different ticks.
SQL server has ticks from 1000/3 millisecond starting at 1-1-1753 (for the
DateTime)
Net deals with ticks from a unit of hundred nanosecond starting at 1-1-1

And I also said that I recommended the ISO approach when
"passing date information as a string" (I use it myself and have
never looked back).

I did as well however Net has better methods. By using direct the internal
DateTime and only use the string when it is needed to show it on screen or
get it from the sceen.

I know that .Net takes care of all this provided that one goes
through the proper objects.

ISO 8601 specific to Asian countries? This is the first time I
have heard that. Can you provide me with some links to some
more information?

That is not so strange as you think that they don't use European languages.
The former communist countries have used it a while, however that is as well
gone again. I think that the Britans will drive earlier on the right lane
than ISO8601 is used in Europe. China started the Georgian calendar late in
1949 and Turkey in 1928. Most Islamic countries (not all see Turkey) have of
course there own calendar (Isreal as well an hebrewic). Therefore the main
ISO8601 countries are India and China. From other Asian countries I am not
sure what they use.

Searching for Calendar and ISO8601 in Google gives you a bunch of
information. So wants the state of Nevada only use ISO8601 in official
websites.

As the article in the Microsoft link stated,
the ISO format is unambiguous to SQL Server and is recommended
for that reason, regardless of the locale settings or country.

The article is clear for me from a probably SQL part of Microsoft, not from
the Net or the OS part. How would you look to it if it was writen by
Navision/Microsoft.

I only mentioned the ISO format because other posters started
talking about mm-dd-yyyy and dd-mm-yyyy.

That I understood, however those dates have given often misunderstandings,
while it is so fine done in Net.

If the original question had been about what .Net classes/objects to
use in order to pass date information correctly to and from SQL
Server, I suppose we would have had an entirely different
discussion.

The way as Armin wrote it was in my opinon clear for the question. That was
why I did not add anything beside the warning to it, while I mostly do that
in DateTime questions.

(In my opinion is it a pity that the VBNet IDE shows all dateTimes in USA
format, whatever it internal is. C# shows it in the culture format from the
system, my favorite would be as it was showed in ISO8601 in all IDE's,
however that is noted as point for the future)

I hope that you now understand why I gave a reply on your message.

Cor

[Cor Ligthert [MVP]]

Joergen,

Net is been able to overcome the problems as they have forever been with
giving hardcoded dates to database servers.

I am very glad that it is done, this does it not make needed anymore to use
systems as they where in the time of the punch card, for which ISO 8601 is
really faremost the best method.

For a direct answer on your latest question in the most simple way.

http://www.vb-tips.com/default.aspx?...6-7139b8970071

(This even works if the setting is Danish as day fullmonthname (written in
Danish) year)

I hope this gives the idea

Cor

[Joergen Bech]

On Mon, 26 Dec 2005 11:30:54 +0100, "Cor Ligthert [MVP]"
<no************@planet.nl> wrote:

Joergen,

Net is been able to overcome the problems as they have forever been with
giving hardcoded dates to database servers.

I am very glad that it is done, this does it not make needed anymore to use
systems as they where in the time of the punch card, for which ISO 8601 is
really faremost the best method.

For a direct answer on your latest question in the most simple way.

http://www.vb-tips.com/default.aspx?...6-7139b8970071

(This even works if the setting is Danish as day fullmonthname (written in
Danish) year)

I hope this gives the idea

Cor

So I type this up:

---snip---
Private Sub CommandTest2()
Dim conn As System.Data.SqlClient.SqlConnection = New
System.Data.SqlClient.SqlConnection
conn.ConnectionString = "Data Source=JB;Initial
Catalog=Northwind;User Id=sa;Password=;"
conn.Open()

Dim dsNorthwind1 As New DataSet()
Dim da As New SqlClient.SqlDataAdapter()
Dim cmd As New SqlClient.SqlCommand("SELECT * FROM Orders
WHERE OrderDate >= @STARTDATE", conn)
cmd.Parameters.Add("@STARTDATE", SqlDbType.DateTime)
cmd.Parameters("@STARTDATE").Value = New Date(1998, 1, 1)

da.SelectCommand = cmd
da.Fill(dsNorthwind1)

dsNorthwind1.WriteXml("d:\out.xml")

conn.Close()

End Sub
---snip---

and what the SQL Server Profiler sees as a result of running this is

---snip---
exec sp_executesql N'SELECT * FROM Orders WHERE OrderDate >=
@STARTDATE', N'@STARTDATE datetime', @STARTDATE = 'Jan 1 1998
12:00:00:000AM'
---snip---

That is horrible. I haven't bothered to check if that is actually the
text transferred across the wire (can anyone confirm this?), but I
am fairly positive that this is the case.

I understand that this might make the code more readabable on the
..Net side, but (leaving SQL 2005 and its bulk copy functions outside
this discussion) there are situations where this approach would be
highly inefficient, e.g. in cases where a lot of data needs to be
transformed and sent to the server.

If those command parameter values are sent to the server in
a binary format without going through a .NetNative->Text->SQLNative
transformation, I would tend to agree with you. If not, .Net is just
hiding the details from you and there will be situations where you
will want to skip the overhead and prepare the SQL yourself.

But that is probably another discussion for another newsgroup.

"Punch cards" are alive and well.

Regards,

Joergen Bech

[Joergen Bech]

>

and what the SQL Server Profiler sees as a result of running this is

---snip---
exec sp_executesql N'SELECT * FROM Orders WHERE OrderDate >=
@STARTDATE', N'@STARTDATE datetime', @STARTDATE = 'Jan 1 1998
12:00:00:000AM'
---snip---

That is horrible. I haven't bothered to check if that is actually the
text transferred across the wire (can anyone confirm this?), but I
am fairly positive that this is the case.

I downloaded a packet sniffer and checked: Looks like the
"sp_executesql" part is correct. Same goes for the command text.
Cannot from the raw data see how the parameters are transferred,
though.

Can anyone recommend a packet sniffer which will display the
SQL Server traffic data specifically, rather than just raw packet
data? Something like http://www.sqlpower.com/ ?

Just to check if SQL Profiler provides the correct picture of
what .Net is sending to the server when using the command
objects in .Net.

Guess this is one for the data groups.

/JB