Date in database

B

Bob Butler

I don't understand the point you're trying to make. I entered this:

?#2000/8/1#

into the Immediate window

Enter the following line of code into a procedure in a VB code window, not
the immediate window

d=#2008/1/8#
 
B

Bob Barrows [MVP]

Bob said:
Enter the following line of code into a procedure in a VB code
window, not the immediate window

d=#2008/1/8#

Oh, I see. The IDE "helps" you out by turning it into:

d = #1/8/2008#

:) You could have just told us this instead of making someone (ok, me
<grin>) go to the trouble of finding it out.

Given that the immediate window accepts the y/m/d format, it seems to me
that the compiler would as well.
To test that, I opened the frm file in Notepad, entered the date in
y/m/d format, saved and closed it, compiled and ran the project, which
ran with no problem. Reopened the frm file which still had the date in
y/m/d format, so the compiler seems to have no problem with this date
format.

Given that, if I was still using VB, I would be averse to developing
with Notepad, I guess I would stick with the m/d/y format myself (or
train myself to always use dateserial).
 
S

Saga

As I said, VB expects mm/dd/yyyy when a date is used as a literal. It just "helps"
you out by rearranging the numbers to conform to this format, in this case correctly
interpreting the typed in yyyy/mm/dd format <g>

Saga
 
B

Bob Butler

Oh, I see. The IDE "helps" you out by turning it into:

d = #1/8/2008#

:) You could have just told us this instead of making someone (ok, me
<grin>) go to the trouble of finding it out.

If I'd just told you you wouldn't have believed me anyway! <g>

The # delimiters define a date in #m/d/yyyy# format when used in code
regardless of locale. That lets you copy & paste code between
projects/users without worrying about the local format. If you use
"m/d/yyyy" or "d/m/yyyy" then the same code could behave very differently on
different systems.

For anything where the developers are not likely to be US-centric using
DateSerial is a good option, if a tad more overhead, since it's not likely
to be misread by somebody more used to the d/m/yyyy format.
 
F

fniles

OK, I think I got confused before.
We have all the ASP pages and SQL Server database in the US, and people who
use our application can be in the US and abroad (like Europe) where the date
setting is dd/mm/yy.
Please let me know if I am thinking in the correct direction.
When a US user enter date they will enter 1/10/08 for January 10, 08. When
they do that I can simply save 10/1/08 in the database.
When a non US user (whose date format is dd/mm/yy) enter date they will
enter 10/1/08 for Jan 10, 2008. When they do that, before saving it to the
database I need to convert 10/1/08 to 1/10/08 so that I will store 1/10/08
to the database.
Is there a VB/VBScript function that I can call to find out the date format
of the user ? Or, can I use the GetLocale function ?

Thank you.
 
B

Bob Butler

fniles said:
OK, I think I got confused before.
We have all the ASP pages and SQL Server database in the US, and people
who use our application can be in the US and abroad (like Europe) where
the date setting is dd/mm/yy.
Please let me know if I am thinking in the correct direction.
When a US user enter date they will enter 1/10/08

No, they won't. users should be using some sort of calendar control or combo
box or other such methods of specifying a date so that there is no question
about it. Never accept date values in a text field.

For display you can always use something like "mmm d, yyyy" that everybody
will at least understand to mean the same thing.
 
B

Bob Butler

fniles said:
Did you mean there is function to get the user's date setting ?


No, I mean that users won't "enter 1/10/08" since they won't be entering a
date in a textbox. They'll be using combo boxes or clicking on a calendar
or some other such method. You don't want to get yourself into the position
where you have to know what the user's preferred date format is.
 
B

Bob Barrows [MVP]

fniles said:
Did you mean there is function to get the user's date setting ?

No. He means that the developer of the page that accepts the user's
input should not use a textbox to solicit dates from the user. Instead,
use " ... some sort of calendar control or combo box or other such
methods of specifying a date so that there is no question about it"

Of course, there is no such thing as a combo box in html: you could use
three cascading dropdown boxes (you would want the days dropdown to vary
depending on the year and month selected). The idea is to pass an
unambiguous year, month and day to the server.

But these are client-side html/scripting issues that are out of the
scope of this asp group which is focussed on server-side problems.

For html questions see:
http://groups.google.com/groups/dir?sel=33584039

For client-side scripting questions, go to
microsoft.public.scripting.jscript or comp.lang.javascript
 
M

Mike Williams

Did you mean there is function to get the user's date setting ?

As Bob has already said, you should not generally allow your user to
manually enter dates, because you can *never* be certain what the user
actually means by the data he inputs. Even if you know for a fact that your
code is running on a machine with standard English date settings (day,
month, year order) you cannot be sure that the user who happens to be
sitting in front of that machine is in fact English. It is entirely possible
that the user on an "English setting" machine just happens to be a visiting
American, and therefore has a rather strange illogical arrangement for his
dates where he uses neither "lo to hi" nor "hi to lo" format. These
Americans can be very strange people ;-)

Mike
 
B

Bob Butler

Bob Barrows said:
Of course, there is no such thing as a combo box in html:

right, I rarely use a VB combobox for anything other than a drop-down list
and sometimes use the term too loosely.
 
M

Mark J. McGinty

Bob Barrows said:
Please. Stop trying to force a particular format when inserting data into
databases. Databases do not store format. They store numeric
recpresentations of dates. Access stores date/times as Doubles, with the
whole number portion representing the number of days since the seed date
and the decimal portion representing the time (.0 = midnight, .5 = noon).
SQL Server stores datetimes as paired integers, with the first integer
containing the number of days since the seed date, and the second
containing the number of milliseconds since midnight.

[None of the following is meant to detract from what Bob has said...]

That may be exactly what BOL says, but it is not, in fact, correct. The
low-order int stores the number 3 millisecond intervals since midnight, not
the number of milliseconds. This can be proven conclusively in T-SQL
script, in a number of ways; perhaps the simplest being:

select convert(datetime, '2008-01-10 01:00:00.001')
[returns 2008-01-10 01:00:00.000]

select convert(datetime, '2008-01-10 01:00:00.002')
[returns 2008-01-10 01:00:00.003]

If you are interested in more-definitive proof let me know.

A logical question might be, why am I on about this? I can only answer that
with another question: why would Microsoft choose to publish incorrect
documentation about this, over three releases of SQL Server? To me it's
unfathomable. (Note that SQL 2008 BOL no longer publishes this lie, but
neither does it correct it, it merely omits any low-level storage details.)


-Mark
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top