Date in database

On my machine in the office I change the computer setting to English (UK) so
the date format is dd/mm/yyyy instead of mm/dd/yyyy for US.
This problem happens in either Access or SQL Server.
In the database I have a table with Date/time column. The database is
located on a machine that is set to dd/mm/yyyy also.
When I enter date 7/1/08 (as in January 7, 2008), it stores it in the
database as 1/7/08 instead of 7/1/08. Why is it like that and how can I make
the database stores it as 7/1/08 ?
Thank you.

Re: Date in database

You can set the regional date format to anything you like, but when using SQLs to
insert or update data, you must use the yyyy-mm-dd format for SQL Server or the
mm/dd/yyyy format for Access.

If using the Addnew method then assigning the date value to the field, you must use
a date variable type:

dim strDate as string

strDate = date

open recordset "rs"

rs.addnew
rs!DateField = strDate
rs.update

The above wont work!!! (for any date format other than mm/dd/yyyy)

Change it to this:

dim dteDate as date

dteDate = date

open recordset "rs"

rs.addnew
rs!DateField = dteDate
rs.update

This example is so trivial, that you can assign Date directly, but when using
calculated dates the variable will most likely be needed.

To build an SQL with a date:

sSQL = "insert into MyTable (Mydate) values ("
sSQL = sSQL & "'" & format$(date, "yyyy-mm-dd") & "')"

For Access change that to:

sSQL = sSQL & "'" & format$(date, "mm/dd/yyyy") & "')"

HTH Saga
--



"fniles" wrote in message
news:%23oWviTkUIHA.5360@TK2MSFTNGP03.phx.gbl...
> On my machine in the office I change the computer setting to English (UK) so the date format is
> dd/mm/yyyy instead of mm/dd/yyyy for US.
> This problem happens in either Access or SQL Server.
> In the database I have a table with Date/time column. The database is located on a machine that is
> set to dd/mm/yyyy also.
> When I enter date 7/1/08 (as in January 7, 2008), it stores it in the database as 1/7/08 instead
> of 7/1/08. Why is it like that and how can I make the database stores it as 7/1/08 ?
> Thank you.
>
>

Re: Date in database

Access will work fine with yyyy-mm-dd as well. However, you need to delimit
the dates with # characters in Access, not single quotes.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Saga" wrote in message
news:e2zmZbkUIHA.4712@TK2MSFTNGP04.phx.gbl...
> This example is so trivial, that you can assign Date directly, but when
> using
> calculated dates the variable will most likely be needed.
>
> To build an SQL with a date:
>
> sSQL = "insert into MyTable (Mydate) values ("
> sSQL = sSQL & "'" & format$(date, "yyyy-mm-dd") & "')"
>
> For Access change that to:
>
> sSQL = sSQL & "'" & format$(date, "mm/dd/yyyy") & "')"
>
>
> "fniles" wrote in message
> news:%23oWviTkUIHA.5360@TK2MSFTNGP03.phx.gbl...
>> On my machine in the office I change the computer setting to English (UK)
>> so the date format is dd/mm/yyyy instead of mm/dd/yyyy for US.
>> This problem happens in either Access or SQL Server.
>> In the database I have a table with Date/time column. The database is
>> located on a machine that is set to dd/mm/yyyy also.
>> When I enter date 7/1/08 (as in January 7, 2008), it stores it in the
>> database as 1/7/08 instead of 7/1/08. Why is it like that and how can I
>> make the database stores it as 7/1/08 ?
>> Thank you.

Re: Date in database

Thanks for catching that :-) I neglected to swap out the quotes for the # char.

Noted on the yyyy-mm-dd format for Access.

Saga
--


"Douglas J. Steele" wrote in message
news:OJyZUhkUIHA.536@TK2MSFTNGP06.phx.gbl...
> Access will work fine with yyyy-mm-dd as well. However, you need to delimit the dates with #
> characters in Access, not single quotes.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Saga" wrote in message news:e2zmZbkUIHA.4712@TK2MSFTNGP04.phx.gbl...
>> This example is so trivial, that you can assign Date directly, but when using
>> calculated dates the variable will most likely be needed.
>>
>> To build an SQL with a date:
>>
>> sSQL = "insert into MyTable (Mydate) values ("
>> sSQL = sSQL & "'" & format$(date, "yyyy-mm-dd") & "')"
>>
>> For Access change that to:
>>
>> sSQL = sSQL & "'" & format$(date, "mm/dd/yyyy") & "')"
>>
>>
>> "fniles" wrote in message
>> news:%23oWviTkUIHA.5360@TK2MSFTNGP03.phx.gbl...
>>> On my machine in the office I change the computer setting to English (UK) so the date format is
>>> dd/mm/yyyy instead of mm/dd/yyyy for US.
>>> This problem happens in either Access or SQL Server.
>>> In the database I have a table with Date/time column. The database is located on a machine that
>>> is set to dd/mm/yyyy also.
>>> When I enter date 7/1/08 (as in January 7, 2008), it stores it in the database as 1/7/08 instead
>>> of 7/1/08. Why is it like that and how can I make the database stores it as 7/1/08 ?
>>> Thank you.
>
>

Re: Date in database

"fniles" wrote in message
news:%23oWviTkUIHA.5360@TK2MSFTNGP03.phx.gbl...
> On my machine in the office I change the computer setting to English (UK)
> so the date format is dd/mm/yyyy instead of mm/dd/yyyy for US.
> This problem happens in either Access or SQL Server.
> In the database I have a table with Date/time column. The database is
> located on a machine that is set to dd/mm/yyyy also.
> When I enter date 7/1/08 (as in January 7, 2008), it stores it in the
> database as 1/7/08 instead of 7/1/08. Why is it like that and how can I
> make the database stores it as 7/1/08 ?

If the data type of the column is truly one of the various "date" data
types, the format of the date is irrelevant. Don't worry about it.

However, if the data type is actually text or characters, then you've got a
huge problem.


--
Mike
Microsoft MVP Visual Basic

Re: Date in database

Thank you everybody.
It turns out that in VB it works fine, but it does not work in ASP.
The data type of the column is truly a "date/time" column in Access
and "Datetime" column in SQL Server.

I do need the date to be stored in the correct format in the database,
because in my ASP program I do the following:
sDay =3D day(d)
sMonth =3D month(d)
sYear =3D year(d)
If it is not stored correctly in the database, the above functions do
not return the correct values.

It seems to work when I do the following (it stores 8/1/08 in the
database)
ssql =3D "update myTBL set colDate =3D format('8/1/08','dd/mm/yy') where
ID =3D 1"
Set rs =3D Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection =3D dbConnection
rs.open ssql

But when I do the following, it stores 1/8/08 in the database:
ssql =3D "select * from myTBL where ID =3D 1"
Set rs =3D Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection =3D dbConnection
rs.CursorType =3D adOpenKeyset
rs.LockType =3D adLockOptimistic
rs.Source =3D sSql
rs.Open
rs("colDate") =3D cdate(#8/1/08#) '--> got the same result when i do
rs("colDate") =3D cdate("8/1/08")
rs.Update
rs.close
set rs =3D nothing

I could use the "update" command on the 1st method, but I would like
to use the 2nd method if possible. Is it possible to make the 2nd
method above work ?
Thank you.


On Jan 8, 5:41=A0pm, "MikeD" wrote:
> "fniles" wrote in message
>
> news:%23oWviTkUIHA.5360@TK2MSFTNGP03.phx.gbl...
>
> > On my machine in the office I change the computer setting to English (UK=
)
> > so the date format is dd/mm/yyyy instead of mm/dd/yyyy for US.
> > This problem happens in either Access or SQL Server.
> > In the database I have a table with Date/time column. The database is
> > located on a machine that is set to dd/mm/yyyy also.
> > When I enter date 7/1/08 (as in January 7, 2008), it stores it in the
> > database as 1/7/08 instead of 7/1/08. Why is it like that and how can I
> > make the database stores it as 7/1/08 ?
>
> If the data type of the column is truly one of the various "date" data
> types, the format of the date is irrelevant. Don't worry about it.
>
> However, if the data type is actually text or characters, then you've got =
a
> huge problem.
>
> --
> Mike
> Microsoft MVP Visual Basic

Re: Date in database

>>> I do need the date to be stored in the correct format in the database,

The database has it's own way of storing dates - typically in a Julian
value.
How you want to display it is done via formating.

Take a look at this link:
http://www.sql-server-performance.com/articles/dev/datetime_ datatype_p1.aspx

I myself work mainly with Oracle, which I think does something similar and
occasionally with Access.

Not sure about Access, but a google search could get you the details I'm
sure.
But what the Database engine itself is storing is irrelevant.
What's important is getting the data entered properly and displayed as
needed.

Steve Mussler

Re: Date in database

Inline

Saga

--


wrote in message
news:45488f81-bd97-48bf-941e-81df707f28cc@v4g2000hsf.googleg roups.com...
Thank you everybody.
It turns out that in VB it works fine, but it does not work in ASP.
The data type of the column is truly a "date/time" column in Access
and "Datetime" column in SQL Server.

I do need the date to be stored in the correct format in the database,
because in my ASP program I do the following:
sDay = day(d)
sMonth = month(d)
sYear = year(d)
If it is not stored correctly in the database, the above functions do
not return the correct values.

It seems to work when I do the following (it stores 8/1/08 in the
database)
ssql = "update myTBL set colDate = format('8/1/08','dd/mm/yy') where
ID = 1"

***Reply***
That format statement just does not look right. Use 4 digits for year
and yyyy-mm-dd format. Using dd/mm/yy will cause problems.
***

Set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = dbConnection
rs.open ssql

But when I do the following, it stores 1/8/08 in the database:
ssql = "select * from myTBL where ID = 1"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = dbConnection
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Source = sSql
rs.Open
rs("colDate") = cdate(#8/1/08#)
'--> got the same result when i do rs("colDate") = cdate("8/1/08")

***Reply***
When specifying a literal date (#8/1/08#) VB always expects mm/dd/yyyy
format. So the above will be interpreted as Aug 1st, 2008. The CDate(##)
statement above is useless since you are in effect converting a date into
a date.

On the other hand, cdate("8/1/08") is converting the string "8/1/08"into a
date which is a step in the right direction, but it is still not 100% "safe":

"CDate recognizes date formats according to the locale setting of your
system." - MSDN Library

I did the following in the immediate window:

? cdate("8/1/08")
08/01/2008 (2nd) 8th of Jan
8/1/2008 (1st) Aug 1st

I set my regional settings to English US for the 1st try. I then set the
regional settings to English UK for the 2nd test. Note how the "same" date
was displayed, but is intepreted differently depending on locale. Best to stay
away from having dates in strings and using these to update tables or do
further date calculations where the month and day could be ambiguous.

I did another test (in the immediate wndow):

? #8/1/2008#
8/1/2008 US setting Aug 1st 2008
01/08/2008 UK setting 1st of Aug 2008 Same date!!!

Note how the above literal is interpreted as the same date but when displayed
it is done using the correct locale format. As mentioned before, when the #
char is used to specify a date literal it is always interpreted as mm/dd/yyyy.
***

rs.Update
rs.close
set rs = nothing

I could use the "update" command on the 1st method, but I would like
to use the 2nd method if possible. Is it possible to make the 2nd
method above work ?
Thank you.


***PS: I have no idea why the OP's text was not indented with the ">"
char. It seems to have been only for this message.



On Jan 8, 5:41 pm, "MikeD" wrote:
> "fniles" wrote in message
>
> news:%23oWviTkUIHA.5360@TK2MSFTNGP03.phx.gbl...
>
> > On my machine in the office I change the computer setting to English (UK)
> > so the date format is dd/mm/yyyy instead of mm/dd/yyyy for US.
> > This problem happens in either Access or SQL Server.
> > In the database I have a table with Date/time column. The database is
> > located on a machine that is set to dd/mm/yyyy also.
> > When I enter date 7/1/08 (as in January 7, 2008), it stores it in the
> > database as 1/7/08 instead of 7/1/08. Why is it like that and how can I
> > make the database stores it as 7/1/08 ?
>
> If the data type of the column is truly one of the various "date" data
> types, the format of the date is irrelevant. Don't worry about it.
>
> However, if the data type is actually text or characters, then you've got a
> huge problem.
>
> --
> Mike
> Microsoft MVP Visual Basic

Re: Date in database

fniles wrote:
> On my machine in the office I change the computer setting to English (UK) so
> the date format is dd/mm/yyyy instead of mm/dd/yyyy for US.
> This problem happens in either Access or SQL Server.
> In the database I have a table with Date/time column. The database is
> located on a machine that is set to dd/mm/yyyy also.
> When I enter date 7/1/08 (as in January 7, 2008), it stores it in the
> database as 1/7/08 instead of 7/1/08. Why is it like that and how can I make
> the database stores it as 7/1/08 ?
> Thank you.

Would setting the locale help?
Not sure if your using VBScript, but here is something about it:
http://www.w3schools.com/vbscript/func_setlocale.asp

Steve

Re: Date in database

Although setting the locale via VB6 code -could- be a potential "fix", I humbly
advice against it as it might make more enemies than friends. One's app
should adapt the locale setting, NOT the locale setting to the app.

Saga
--



"Dooza" wrote in message news:O8VMJZuUIHA.748@TK2MSFTNGP04.phx.gbl...
> fniles wrote:
>> On my machine in the office I change the computer setting to English (UK) so the date format is
>> dd/mm/yyyy instead of mm/dd/yyyy for US.
>> This problem happens in either Access or SQL Server.
>> In the database I have a table with Date/time column. The database is located on a machine that
>> is set to dd/mm/yyyy also.
>> When I enter date 7/1/08 (as in January 7, 2008), it stores it in the database as 1/7/08 instead
>> of 7/1/08. Why is it like that and how can I make the database stores it as 7/1/08 ?
>> Thank you.
>
> Would setting the locale help?
> Not sure if your using VBScript, but here is something about it:
> http://www.w3schools.com/vbscript/func_setlocale.asp
>
> Steve

Re: Date in database

Thank you everyone.

So, both the client machine and the server where the ASP is running
and where the database is located are set to UK setting. I inserted
Jan 8, 08 (8/1/08) to the database, and after retrieving the value
back from the database, I use the functions Day and Month, and they
return wrong day and month.

ssql =3D "select * from myTBL where ID =3D 1"
Set rs =3D Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection =3D dbConnection
rs.CursorType =3D adOpenKeyset
rs.LockType =3D adLockOptimistic
rs.Source =3D sSql
rs.Open
rs("colDate") =3D cdate("8/1/08")
rs.update
:
ssql =3D "select * from myTBL where ID =3D 1"
Set rs2 =3D dbConnection.Execute(sSQL)
if not (rs2.EOF) then
date3 =3D rs2("DATA_DATE")
sDay =3D day(date3) -->>>> RETURNS 1, where it suppose to
return 8
sMonth =3D month(date3) ->>>> RETURNS 8 (August), where it
suppose to return 1 (January)
end if



On Jan 9, 10:18=A0am, "Saga" wrote:
> Inline
>
> Saga
>
> --
>
> wrote in message
>
> news:45488f81-bd97-48bf-941e-81df707f28cc@v4g2000hsf.googleg roups.com...
> Thank you everybody.
> It turns out that in VB it works fine, but it does not work in ASP.
> The data type of the column is truly a "date/time" column in Access
> and "Datetime" column in SQL Server.
>
> I do need the date to be stored in the correct format in the database,
> because in my ASP program I do the following:
> sDay =3D day(d)
> sMonth =3D month(d)
> sYear =3D year(d)
> If it is not stored correctly in the database, the above functions do
> not return the correct values.
>
> It seems to work when I do the following (it stores 8/1/08 in the
> database)
> ssql =3D "update myTBL set colDate =3D format('8/1/08','dd/mm/yy') =A0wher=
e
> ID =3D 1"
>
> ***Reply***
> That format statement just does not look right. Use 4 digits for year
> and yyyy-mm-dd format. Using dd/mm/yy will cause problems.
> ***
>
> Set rs =3D Server.CreateObject("ADODB.Recordset")
> rs.ActiveConnection =3D dbConnection
> rs.open ssql
>
> But when I do the following, it stores 1/8/08 in the database:
> ssql =3D "select * from myTBL where ID =3D 1"
> Set rs =3D Server.CreateObject("ADODB.Recordset")
> rs.ActiveConnection =3D dbConnection
> rs.CursorType =3D adOpenKeyset
> rs.LockType =3D adLockOptimistic
> rs.Source =3D sSql
> rs.Open
> rs("colDate") =3D cdate(#8/1/08#)
> '--> got the same result when i do rs("colDate") =3D cdate("8/1/08")
>
> ***Reply***
> When specifying a literal date (#8/1/08#) VB always expects mm/dd/yyyy
> format. So the above will be interpreted as Aug 1st, 2008. The CDate(##)
> statement above is useless since you are in effect converting a date into
> a date.
>
> On the other hand, cdate("8/1/08") is converting the string "8/1/08"into a=

> date which is a step in the right direction, but it is still not 100% "saf=
e":
>
> "CDate recognizes date formats according to the locale setting of your
> system." - MSDN Library
>
> I did the following in the immediate window:
>
> ? cdate("8/1/08")
> 08/01/2008 =A0(2nd) =A08th of Jan
> 8/1/2008 =A0 =A0 (1st) =A0 Aug 1st
>
> I set my regional settings to English US for the 1st try. I then set the
> regional settings to English UK for the 2nd test. Note how the "same" date=

> was displayed, but is intepreted differently depending on locale. Best to =
stay
> away from having dates in strings and using these to update tables or do
> further date calculations where the month and day could be ambiguous.
>
> I did another test (in the immediate wndow):
>
> ? #8/1/2008#
> 8/1/2008 =A0 =A0US setting =A0Aug 1st 2008
> 01/08/2008 =A0 UK setting =A01st of Aug 2008 =A0 Same date!!!
>
> Note how the above literal is interpreted as the same date but when displa=
yed
> it is done using the correct locale format. As mentioned before, when the =
#
> char is used to specify a date literal it is always interpreted as mm/dd/y=
yyy.
> ***
>
> rs.Update
> rs.close
> set rs =3D nothing
>
> I could use the "update" command on the 1st method, but I would like
> to use the 2nd method if possible. Is it possible to make the 2nd
> method above work ?
> Thank you.
>
> ***PS: I have no idea why the OP's text was not indented with the ">"
> char. It seems to have been only for this message.
>
> On Jan 8, 5:41 pm, "MikeD" wrote:
>
>
>
> > "fniles" wrote in message
>
> >news:%23oWviTkUIHA.5360@TK2MSFTNGP03.phx.gbl...
>
> > > On my machine in the office I change the computer setting to English (=
UK)
> > > so the date format is dd/mm/yyyy instead of mm/dd/yyyy for US.
> > > This problem happens in either Access or SQL Server.
> > > In the database I have a table with Date/time column. The database is
> > > located on a machine that is set to dd/mm/yyyy also.
> > > When I enter date 7/1/08 (as in January 7, 2008), it stores it in the
> > > database as 1/7/08 instead of 7/1/08. Why is it like that and how can =
I
> > > make the database stores it as 7/1/08 ?
>
> > If the data type of the column is truly one of the various "date" data
> > types, the format of the date is irrelevant. Don't worry about it.
>
> > However, if the data type is actually text or characters, then you've go=
t a
> > huge problem.
>
> > --
> > Mike
> > Microsoft MVP Visual Basic- Hide quoted text -
>
> - Show quoted text -

Re: Date in database

wrote in message
news:f680541c-07d8-4d2d-aec7-53225778f14a@z17g2000hsg.google groups.com...
>>
Thank you everyone.

So, both the client machine and the server where the ASP is running
and where the database is located are set to UK setting. I inserted
Jan 8, 08 (8/1/08) to the database, and after retrieving the value
back from the database, I use the functions Day and Month, and they
return wrong day and month.

ssql = "select * from myTBL where ID = 1"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = dbConnection
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Source = sSql
rs.Open
rs("colDate") = cdate("8/1/08")
rs.update
:
ssql = "select * from myTBL where ID = 1"
Set rs2 = dbConnection.Execute(sSQL)
if not (rs2.EOF) then
date3 = rs2("DATA_DATE")
sDay = day(date3) -->>>> RETURNS 1, where it suppose to
return 8
sMonth = month(date3) ->>>> RETURNS 8 (August), where it
suppose to return 1 (January)
end if
<<


Did you try this:-

Dim dat : dat = CDate("8/1/08")

Response.Write Day(dat) & "
"
Response.Write Month(dat)


What happens? As has been pointed out at least twice in this thread already
the locale does not impact a date value, it only impacts the conversion to
and from a string.

However Locales in ASP can be tricky. Locale settings are configured at the
user level. Assuming you're only accessing the website as an anonymous
user, the user's locale settings you would be interested in would be the
IUSR_. Since this user is unlikely to have a profile on the
machine the DEFAULT user profile will be used to find the locale settings.
Once the locale settings are loaded they are cached by IIS.

Hence it is possible to make 'changes' to the locale but not affect the
settings in the DEFAULT profile which would have initially been set when the
OS was installed. Its a common problem in the UK where Windows has been
installed with the default US settings. What OS is the web site installed
on?

Personally I would avoid the xx/xx/[xx]xx format altogether. I tend to use
01 Jan 2008 style. This is acceptable to a human and is parsable by
VB(Script), Javascript and SQL Server (I would guess Access would have no
problem with it either).


--
Anthony Jones - MVP ASP/ASP.NET

Re: Date in database

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.

You must remember that ASP (IIS) is not running under your user account, so
just because _you_ have your regional settings set to UK, that does not mean
the account that IIS is using has that setting, which defaults to US.

Instead of cdate("8/1/08"), I suggest you use dateserial(2008,1,8).

Explicitly format the dates you retrieve from the database instead of
depending on the operating system to do it for you. If you want to make sure
dates are displayed in UK format, use the Year(), Day() and Month()
functions to build a string containing the correct format which you display
to your users.

Also, instead of using a recordset to maintain data, I suggest using a
parameterized sql statement so you do not need to worry about delimiters. In
vbscript, this would look like:

ssql = "update table set coldate=? where ID = 1"
dim cmd, arParms
arParms = array(dateserial(2008,1,8))
set cmd=createobject("adodb.command")
set cmd.activeconnection = dbConnection
cmd.commandetype = 1 'adCmdText
cmd.commandtext = ssql
cmd.execute ,arParms,128 'adExecuteNoRecords

This should work regardless of the backend database.

fiefie.niles@gmail.com wrote:
> Thank you everyone.
>
> So, both the client machine and the server where the ASP is running
> and where the database is located are set to UK setting. I inserted
> Jan 8, 08 (8/1/08) to the database, and after retrieving the value
> back from the database, I use the functions Day and Month, and they
> return wrong day and month.
>
> ssql = "select * from myTBL where ID = 1"
> Set rs = Server.CreateObject("ADODB.Recordset")
> rs.ActiveConnection = dbConnection
> rs.CursorType = adOpenKeyset
> rs.LockType = adLockOptimistic
> rs.Source = sSql
> rs.Open
> rs("colDate") = cdate("8/1/08")
> rs.update
>>
> ssql = "select * from myTBL where ID = 1"
> Set rs2 = dbConnection.Execute(sSQL)
> if not (rs2.EOF) then
> date3 = rs2("DATA_DATE")
> sDay = day(date3) -->>>> RETURNS 1, where it suppose to
> return 8
> sMonth = month(date3) ->>>> RETURNS 8 (August), where it
> suppose to return 1 (January)
> end if
>
>
>
> On Jan 9, 10:18 am, "Saga" wrote:
>> Inline
>>
>> Saga
>>
>> --
>>
>> wrote in message
>>
>> news:45488f81-bd97-48bf-941e-81df707f28cc@v4g2000hsf.googleg roups.com...
>> Thank you everybody.
>> It turns out that in VB it works fine, but it does not work in ASP.
>> The data type of the column is truly a "date/time" column in Access
>> and "Datetime" column in SQL Server.
>>
>> I do need the date to be stored in the correct format in the
>> database, because in my ASP program I do the following:
>> sDay = day(d)
>> sMonth = month(d)
>> sYear = year(d)
>> If it is not stored correctly in the database, the above functions do
>> not return the correct values.
>>
>> It seems to work when I do the following (it stores 8/1/08 in the
>> database)
>> ssql = "update myTBL set colDate = format('8/1/08','dd/mm/yy') where
>> ID = 1"
>>
>> ***Reply***
>> That format statement just does not look right. Use 4 digits for year
>> and yyyy-mm-dd format. Using dd/mm/yy will cause problems.
>> ***
>>
>> Set rs = Server.CreateObject("ADODB.Recordset")
>> rs.ActiveConnection = dbConnection
>> rs.open ssql
>>
>> But when I do the following, it stores 1/8/08 in the database:
>> ssql = "select * from myTBL where ID = 1"
>> Set rs = Server.CreateObject("ADODB.Recordset")
>> rs.ActiveConnection = dbConnection
>> rs.CursorType = adOpenKeyset
>> rs.LockType = adLockOptimistic
>> rs.Source = sSql
>> rs.Open
>> rs("colDate") = cdate(#8/1/08#)
>> '--> got the same result when i do rs("colDate") = cdate("8/1/08")
>>
>> ***Reply***
>> When specifying a literal date (#8/1/08#) VB always expects
>> mm/dd/yyyy format. So the above will be interpreted as Aug 1st,
>> 2008. The CDate(##) statement above is useless since you are in
>> effect converting a date into
>> a date.
>>
>> On the other hand, cdate("8/1/08") is converting the string
>> "8/1/08"into a date which is a step in the right direction, but it
>> is still not 100% "safe":
>>
>> "CDate recognizes date formats according to the locale setting of
>> your system." - MSDN Library
>>
>> I did the following in the immediate window:
>>
>> ? cdate("8/1/08")
>> 08/01/2008 (2nd) 8th of Jan
>> 8/1/2008 (1st) Aug 1st
>>
>> I set my regional settings to English US for the 1st try. I then set
>> the regional settings to English UK for the 2nd test. Note how the
>> "same" date was displayed, but is intepreted differently depending
>> on locale. Best to stay away from having dates in strings and using
>> these to update tables or do further date calculations where the
>> month and day could be ambiguous.
>>
>> I did another test (in the immediate wndow):
>>
>> ? #8/1/2008#
>> 8/1/2008 US setting Aug 1st 2008
>> 01/08/2008 UK setting 1st of Aug 2008 Same date!!!
>>
>> Note how the above literal is interpreted as the same date but when
>> displayed it is done using the correct locale format. As mentioned
>> before, when the # char is used to specify a date literal it is
>> always interpreted as mm/dd/yyyy. ***
>>
>> rs.Update
>> rs.close
>> set rs = nothing
>>
>> I could use the "update" command on the 1st method, but I would like
>> to use the 2nd method if possible. Is it possible to make the 2nd
>> method above work ?
>> Thank you.
>>
>> ***PS: I have no idea why the OP's text was not indented with the ">"
>> char. It seems to have been only for this message.
>>
>> On Jan 8, 5:41 pm, "MikeD" wrote:
>>
>>
>>
>>> "fniles" wrote in message
>>
>>> news:%23oWviTkUIHA.5360@TK2MSFTNGP03.phx.gbl...
>>
>>>> On my machine in the office I change the computer setting to
>>>> English (UK) so the date format is dd/mm/yyyy instead of
>>>> mm/dd/yyyy for US.
>>>> This problem happens in either Access or SQL Server.
>>>> In the database I have a table with Date/time column. The database
>>>> is located on a machine that is set to dd/mm/yyyy also.
>>>> When I enter date 7/1/08 (as in January 7, 2008), it stores it in
>>>> the database as 1/7/08 instead of 7/1/08. Why is it like that and
>>>> how can I make the database stores it as 7/1/08 ?
>>
>>> If the data type of the column is truly one of the various "date"
>>> data types, the format of the date is irrelevant. Don't worry about
>>> it.
>>
>>> However, if the data type is actually text or characters, then
>>> you've got a huge problem.
>>
>>> --
>>> Mike
>>> Microsoft MVP Visual Basic- Hide quoted text -
>>
>> - Show quoted text -

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Date in database

Anthony Jones wrote:
> Personally I would avoid the xx/xx/[xx]xx format altogether. I tend
> to use 01 Jan 2008 style. This is acceptable to a human and is
> parsable by VB(Script), Javascript and SQL Server (I would guess
> Access would have no problem with it either).

Actually, it would. Date literals in JetSQL must be passed in US format, or
ISO. Also, a SQL Server instance with French settings would also have a
problem with it.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Date in database

wrote in message
news:f680541c-07d8-4d2d-aec7-53225778f14a@z17g2000hsg.google groups.com...
> So, both the client machine and the server where the ASP is running
> and where the database is located are set to UK setting. I inserted
> Jan 8, 08 (8/1/08) to the database, and after retrieving the value
> back from the database, I use the functions Day and Month, and they
> return wrong day and month.
> rs("colDate") = cdate("8/1/08")

Even if you are sure that the date formats are all UK you should avod
string-to-date conversions. Use #1/8/2008# or DateSerial(2008,1,8)

I also always recommend not using the default names so:
rs.fields("colDate").Value=#1/8/2008#

> if not (rs2.EOF) then
> date3 = rs2("DATA_DATE")

Why are you setting "colDate" and retrieving "DATA_DATE"?

Re: Date in database

"Bob Barrows [MVP]" wrote in message
news:eqCbja4UIHA.1184@TK2MSFTNGP04.phx.gbl...
> Anthony Jones wrote:
> > Personally I would avoid the xx/xx/[xx]xx format altogether. I tend
> > to use 01 Jan 2008 style. This is acceptable to a human and is
> > parsable by VB(Script), Javascript and SQL Server (I would guess
> > Access would have no problem with it either).
>
> Actually, it would. Date literals in JetSQL must be passed in US format,
or
> ISO. Also, a SQL Server instance with French settings would also have a
> problem with it.
>


I haven't worked with Jet in such a long time I forget how limited it is.
And yes my preference for dd mmm yyyy doesn't work internationally. :(

Unfortunately Javascript doesn't parse an ISO date and the current
implementations of XSL don't know what a date is. For me then dd mmm yyyy
is a very compelling compromise.

--
Anthony Jones - MVP ASP/ASP.NET

Re: Date in database

Bob Butler wrote on 10 jan 2008 in microsoft.public.inetserver.asp.general:

> Even if you are sure that the date formats are all UK you should avod
> string-to-date conversions. Use #1/8/2008#

The first of august?

Please no, use:

#2008/1/8#

> or DateSerial(2008,1,8)

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

Re: Date in database

"Evertjan." wrote in message
news:Xns9A219E2E16350eejj99@194.109.133.242...
> Bob Butler wrote on 10 jan 2008 in
> microsoft.public.inetserver.asp.general:
>
>> Even if you are sure that the date formats are all UK you should avod
>> string-to-date conversions. Use #1/8/2008#
>
> The first of august?

No, the # format is always #mm/dd/yyyy# so it is consistent regardless of
your locale

> Please no, use:
>
> #2008/1/8#

Enter this in VB and press enter:
d=#2008/1/8#

Re: Date in database

Bob Butler wrote on 10 jan 2008 in
microsoft.public.inetserver.asp.general:

> "Evertjan." wrote in message
> news:Xns9A219E2E16350eejj99@194.109.133.242...
>> Bob Butler wrote on 10 jan 2008 in
>> microsoft.public.inetserver.asp.general:
>>
>>> Even if you are sure that the date formats are all UK you should
>>> avod string-to-date conversions. Use #1/8/2008#
>>
>> The first of august?
>
> No, the # format is always #mm/dd/yyyy# so it is consistent regardless
> of your locale
>
>> Please no, use:
>>
>> #2008/1/8#
>
> Enter this in VB and press enter:
> d=#2008/1/8#

Why?

This NG is not about VB.


--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

Re: Date in database

Bob Butler wrote:
> "Evertjan." wrote in message
> news:Xns9A219E2E16350eejj99@194.109.133.242...
>> Bob Butler wrote on 10 jan 2008 in
>> microsoft.public.inetserver.asp.general:
>>
>>> Even if you are sure that the date formats are all UK you should
>>> avod string-to-date conversions. Use #1/8/2008#
>>
>> The first of august?
>
> No, the # format is always #mm/dd/yyyy# so it is consistent
> regardless of your locale
>
>> Please no, use:
>>
>> #2008/1/8#
>
> Enter this in VB and press enter:
> d=#2008/1/8#

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

?#2000/8/1#

into the Immediate window in VB, pressed enter and it displayed:

8/1/2000

Were you trying to say that VB would reject that format?

PS. newsgroups added back in to the crosspost - not sure why they were
removed
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Date in database

"Bob Barrows [MVP]" wrote in message
news:OK3CXF6UIHA.4696@TK2MSFTNGP05.phx.gbl...

>> Enter this in VB and press enter:
>> d=#2008/1/8#
>
> 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#

Re: Date in database

wow! that's weird!!!
:-)

"Bob Butler" wrote in message
news:%23zsPJT6UIHA.2000@TK2MSFTNGP05.phx.gbl...
> "Bob Barrows [MVP]" wrote in message
> news:OK3CXF6UIHA.4696@TK2MSFTNGP05.phx.gbl...
>
>>> Enter this in VB and press enter:
>>> d=#2008/1/8#
>>
>> 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#
>
>

Re: Date in database

Bob Butler wrote:
> "Bob Barrows [MVP]" wrote in message
> news:OK3CXF6UIHA.4696@TK2MSFTNGP05.phx.gbl...
>
>>> Enter this in VB and press enter:
>>> d=#2008/1/8#
>>
>> 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#

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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Date in database

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

Saga
--



"MP" wrote in message news:%234E4Xb6UIHA.4752@TK2MSFTNGP05.phx.gbl...
> wow! that's weird!!!
> :-)
>
> "Bob Butler" wrote in message news:%23zsPJT6UIHA.2000@TK2MSFTNGP05.phx.gbl...
>> "Bob Barrows [MVP]" wrote in message
>> news:OK3CXF6UIHA.4696@TK2MSFTNGP05.phx.gbl...
>>
>>>> Enter this in VB and press enter:
>>>> d=#2008/1/8#
>>>
>>> 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#
>>
>>
>
>

Re: Date in database

"Bob Barrows [MVP]" wrote in message
news:uFVjfh6UIHA.5288@TK2MSFTNGP04.phx.gbl...

> 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
> ) go to the trouble of finding it out.

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

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.

Re: Date in database

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.

"Bob Barrows [MVP]" wrote in message
news:OpM5eV4UIHA.4440@TK2MSFTNGP06.phx.gbl...
> 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.
>
> You must remember that ASP (IIS) is not running under your user account,
> so just because _you_ have your regional settings set to UK, that does not
> mean the account that IIS is using has that setting, which defaults to US.
>
> Instead of cdate("8/1/08"), I suggest you use dateserial(2008,1,8).
>
> Explicitly format the dates you retrieve from the database instead of
> depending on the operating system to do it for you. If you want to make
> sure dates are displayed in UK format, use the Year(), Day() and Month()
> functions to build a string containing the correct format which you
> display to your users.
>
> Also, instead of using a recordset to maintain data, I suggest using a
> parameterized sql statement so you do not need to worry about delimiters.
> In vbscript, this would look like:
>
> ssql = "update table set coldate=? where ID = 1"
> dim cmd, arParms
> arParms = array(dateserial(2008,1,8))
> set cmd=createobject("adodb.command")
> set cmd.activeconnection = dbConnection
> cmd.commandetype = 1 'adCmdText
> cmd.commandtext = ssql
> cmd.execute ,arParms,128 'adExecuteNoRecords
>
> This should work regardless of the backend database.
>
> fiefie.niles@gmail.com wrote:
>> Thank you everyone.
>>
>> So, both the client machine and the server where the ASP is running
>> and where the database is located are set to UK setting. I inserted
>> Jan 8, 08 (8/1/08) to the database, and after retrieving the value
>> back from the database, I use the functions Day and Month, and they
>> return wrong day and month.
>>
>> ssql = "select * from myTBL where ID = 1"
>> Set rs = Server.CreateObject("ADODB.Recordset")
>> rs.ActiveConnection = dbConnection
>> rs.CursorType = adOpenKeyset
>> rs.LockType = adLockOptimistic
>> rs.Source = sSql
>> rs.Open
>> rs("colDate") = cdate("8/1/08")
>> rs.update
>>>
>> ssql = "select * from myTBL where ID = 1"
>> Set rs2 = dbConnection.Execute(sSQL)
>> if not (rs2.EOF) then
>> date3 = rs2("DATA_DATE")
>> sDay = day(date3) -->>>> RETURNS 1, where it suppose to
>> return 8
>> sMonth = month(date3) ->>>> RETURNS 8 (August), where it
>> suppose to return 1 (January)
>> end if
>>
>>
>>
>> On Jan 9, 10:18 am, "Saga" wrote:
>>> Inline
>>>
>>> Saga
>>>
>>> --
>>>
>>> wrote in message
>>>
>>> news:45488f81-bd97-48bf-941e-81df707f28cc@v4g2000hsf.googleg roups.com...
>>> Thank you everybody.
>>> It turns out that in VB it works fine, but it does not work in ASP.
>>> The data type of the column is truly a "date/time" column in Access
>>> and "Datetime" column in SQL Server.
>>>
>>> I do need the date to be stored in the correct format in the
>>> database, because in my ASP program I do the following:
>>> sDay = day(d)
>>> sMonth = month(d)
>>> sYear = year(d)
>>> If it is not stored correctly in the database, the above functions do
>>> not return the correct values.
>>>
>>> It seems to work when I do the following (it stores 8/1/08 in the
>>> database)
>>> ssql = "update myTBL set colDate = format('8/1/08','dd/mm/yy') where
>>> ID = 1"
>>>
>>> ***Reply***
>>> That format statement just does not look right. Use 4 digits for year
>>> and yyyy-mm-dd format. Using dd/mm/yy will cause problems.
>>> ***
>>>
>>> Set rs = Server.CreateObject("ADODB.Recordset")
>>> rs.ActiveConnection = dbConnection
>>> rs.open ssql
>>>
>>> But when I do the following, it stores 1/8/08 in the database:
>>> ssql = "select * from myTBL where ID = 1"
>>> Set rs = Server.CreateObject("ADODB.Recordset")
>>> rs.ActiveConnection = dbConnection
>>> rs.CursorType = adOpenKeyset
>>> rs.LockType = adLockOptimistic
>>> rs.Source = sSql
>>> rs.Open
>>> rs("colDate") = cdate(#8/1/08#)
>>> '--> got the same result when i do rs("colDate") = cdate("8/1/08")
>>>
>>> ***Reply***
>>> When specifying a literal date (#8/1/08#) VB always expects
>>> mm/dd/yyyy format. So the above will be interpreted as Aug 1st,
>>> 2008. The CDate(##) statement above is useless since you are in
>>> effect converting a date into
>>> a date.
>>>
>>> On the other hand, cdate("8/1/08") is converting the string
>>> "8/1/08"into a date which is a step in the right direction, but it
>>> is still not 100% "safe":
>>>
>>> "CDate recognizes date formats according to the locale setting of
>>> your system." - MSDN Library
>>>
>>> I did the following in the immediate window:
>>>
>>> ? cdate("8/1/08")
>>> 08/01/2008 (2nd) 8th of Jan
>>> 8/1/2008 (1st) Aug 1st
>>>
>>> I set my regional settings to English US for the 1st try. I then set
>>> the regional settings to English UK for the 2nd test. Note how the
>>> "same" date was displayed, but is intepreted differently depending
>>> on locale. Best to stay away from having dates in strings and using
>>> these to update tables or do further date calculations where the
>>> month and day could be ambiguous.
>>>
>>> I did another test (in the immediate wndow):
>>>
>>> ? #8/1/2008#
>>> 8/1/2008 US setting Aug 1st 2008
>>> 01/08/2008 UK setting 1st of Aug 2008 Same date!!!
>>>
>>> Note how the above literal is interpreted as the same date but when
>>> displayed it is done using the correct locale format. As mentioned
>>> before, when the # char is used to specify a date literal it is
>>> always interpreted as mm/dd/yyyy. ***
>>>
>>> rs.Update
>>> rs.close
>>> set rs = nothing
>>>
>>> I could use the "update" command on the 1st method, but I would like
>>> to use the 2nd method if possible. Is it possible to make the 2nd
>>> method above work ?
>>> Thank you.
>>>
>>> ***PS: I have no idea why the OP's text was not indented with the ">"
>>> char. It seems to have been only for this message.
>>>
>>> On Jan 8, 5:41 pm, "MikeD" wrote:
>>>
>>>
>>>
>>>> "fniles" wrote in message
>>>
>>>> news:%23oWviTkUIHA.5360@TK2MSFTNGP03.phx.gbl...
>>>
>>>>> On my machine in the office I change the computer setting to
>>>>> English (UK) so the date format is dd/mm/yyyy instead of
>>>>> mm/dd/yyyy for US.
>>>>> This problem happens in either Access or SQL Server.
>>>>> In the database I have a table with Date/time column. The database
>>>>> is located on a machine that is set to dd/mm/yyyy also.
>>>>> When I enter date 7/1/08 (as in January 7, 2008), it stores it in
>>>>> the database as 1/7/08 instead of 7/1/08. Why is it like that and
>>>>> how can I make the database stores it as 7/1/08 ?
>>>
>>>> If the data type of the column is truly one of the various "date"
>>>> data types, the format of the date is irrelevant. Don't worry about
>>>> it.
>>>
>>>> However, if the data type is actually text or characters, then
>>>> you've got a huge problem.
>>>
>>>> --
>>>> Mike
>>>> Microsoft MVP Visual Basic- Hide quoted text -
>>>
>>> - Show quoted text -
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>

Re: Date in database

"fniles" wrote in message
news:ue68ZA7UIHA.5404@TK2MSFTNGP06.phx.gbl...
> 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.

Re: Date in database

> No, they won't
Did you mean there is function to get the user's date setting ?

"Bob Butler" wrote in message
news:uqtxfH7UIHA.3676@TK2MSFTNGP06.phx.gbl...
> "fniles" wrote in message
> news:ue68ZA7UIHA.5404@TK2MSFTNGP06.phx.gbl...
>> 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.
>

Re: Date in database

"fniles" wrote in message
news:OUpOHt8UIHA.6060@TK2MSFTNGP05.phx.gbl...
>> No, they won't
> 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.

Re: Date in database

fniles wrote:
>> No, they won't
> 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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Date in database

"fniles" wrote in message
news:OUpOHt8UIHA.6060@TK2MSFTNGP05.phx.gbl...

> 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

Re: Date in database

"Bob Barrows [MVP]" wrote in message
news:%239XEM28UIHA.4440@TK2MSFTNGP06.phx.gbl...
> 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.

Re: Date in database

"Bob Barrows [MVP]" wrote in message
news:OpM5eV4UIHA.4440@TK2MSFTNGP06.phx.gbl...
> 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



> You must remember that ASP (IIS) is not running under your user account,
> so just because _you_ have your regional settings set to UK, that does not
> mean the account that IIS is using has that setting, which defaults to US.
>
> Instead of cdate("8/1/08"), I suggest you use dateserial(2008,1,8).
>
> Explicitly format the dates you retrieve from the database instead of
> depending on the operating system to do it for you. If you want to make
> sure dates are displayed in UK format, use the Year(), Day() and Month()
> functions to build a string containing the correct format which you
> display to your users.
>
> Also, instead of using a recordset to maintain data, I suggest using a
> parameterized sql statement so you do not need to worry about delimiters.
> In vbscript, this would look like:
>
> ssql = "update table set coldate=? where ID = 1"
> dim cmd, arParms
> arParms = array(dateserial(2008,1,8))
> set cmd=createobject("adodb.command")
> set cmd.activeconnection = dbConnection
> cmd.commandetype = 1 'adCmdText
> cmd.commandtext = ssql
> cmd.execute ,arParms,128 'adExecuteNoRecords
>
> This should work regardless of the backend database.
>
> fiefie.niles@gmail.com wrote:
>> Thank you everyone.
>>
>> So, both the client machine and the server where the ASP is running
>> and where the database is located are set to UK setting. I inserted
>> Jan 8, 08 (8/1/08) to the database, and after retrieving the value
>> back from the database, I use the functions Day and Month, and they
>> return wrong day and month.
>>
>> ssql = "select * from myTBL where ID = 1"
>> Set rs = Server.CreateObject("ADODB.Recordset")
>> rs.ActiveConnection = dbConnection
>> rs.CursorType = adOpenKeyset
>> rs.LockType = adLockOptimistic
>> rs.Source = sSql
>> rs.Open
>> rs("colDate") = cdate("8/1/08")
>> rs.update
>>>
>> ssql = "select * from myTBL where ID = 1"
>> Set rs2 = dbConnection.Execute(sSQL)
>> if not (rs2.EOF) then
>> date3 = rs2("DATA_DATE")
>> sDay = day(date3) -->>>> RETURNS 1, where it suppose to
>> return 8
>> sMonth = month(date3) ->>>> RETURNS 8 (August), where it
>> suppose to return 1 (January)
>> end if
>>
>>
>>
>> On Jan 9, 10:18 am, "Saga" wrote:
>>> Inline
>>>
>>> Saga
>>>
>>> --
>>>
>>> wrote in message
>>>
>>> news:45488f81-bd97-48bf-941e-81df707f28cc@v4g2000hsf.googleg roups.com...
>>> Thank you everybody.
>>> It turns out that in VB it works fine, but it does not work in ASP.
>>> The data type of the column is truly a "date/time" column in Access
>>> and "Datetime" column in SQL Server.
>>>
>>> I do need the date to be stored in the correct format in the
>>> database, because in my ASP program I do the following:
>>> sDay = day(d)
>>> sMonth = month(d)
>>> sYear = year(d)
>>> If it is not stored correctly in the database, the above functions do
>>> not return the correct values.
>>>
>>> It seems to work when I do the following (it stores 8/1/08 in the
>>> database)
>>> ssql = "update myTBL set colDate = format('8/1/08','dd/mm/yy') where
>>> ID = 1"
>>>
>>> ***Reply***
>>> That format statement just does not look right. Use 4 digits for year
>>> and yyyy-mm-dd format. Using dd/mm/yy will cause problems.
>>> ***
>>>
>>> Set rs = Server.CreateObject("ADODB.Recordset")
>>> rs.ActiveConnection = dbConnection
>>> rs.open ssql
>>>
>>> But when I do the following, it stores 1/8/08 in the database:
>>> ssql = "select * from myTBL where ID = 1"
>>> Set rs = Server.CreateObject("ADODB.Recordset")
>>> rs.ActiveConnection = dbConnection
>>> rs.CursorType = adOpenKeyset
>>> rs.LockType = adLockOptimistic
>>> rs.Source = sSql
>>> rs.Open
>>> rs("colDate") = cdate(#8/1/08#)
>>> '--> got the same result when i do rs("colDate") = cdate("8/1/08")
>>>
>>> ***Reply***
>>> When specifying a literal date (#8/1/08#) VB always expects
>>> mm/dd/yyyy format. So the above will be interpreted as Aug 1st,
>>> 2008. The CDate(##) statement above is useless since you are in
>>> effect converting a date into
>>> a date.
>>>
>>> On the other hand, cdate("8/1/08") is converting the string
>>> "8/1/08"into a date which is a step in the right direction, but it
>>> is still not 100% "safe":
>>>
>>> "CDate recognizes date formats according to the locale setting of
>>> your system." - MSDN Library
>>>
>>> I did the following in the immediate window:
>>>
>>> ? cdate("8/1/08")
>>> 08/01/2008 (2nd) 8th of Jan
>>> 8/1/2008 (1st) Aug 1st
>>>
>>> I set my regional settings to English US for the 1st try. I then set
>>> the regional settings to English UK for the 2nd test. Note how the
>>> "same" date was displayed, but is intepreted differently depending
>>> on locale. Best to stay away from having dates in strings and using
>>> these to update tables or do further date calculations where the
>>> month and day could be ambiguous.
>>>
>>> I did another test (in the immediate wndow):
>>>
>>> ? #8/1/2008#
>>> 8/1/2008 US setting Aug 1st 2008
>>> 01/08/2008 UK setting 1st of Aug 2008 Same date!!!
>>>
>>> Note how the above literal is interpreted as the same date but when
>>> displayed it is done using the correct locale format. As mentioned
>>> before, when the # char is used to specify a date literal it is
>>> always interpreted as mm/dd/yyyy. ***
>>>
>>> rs.Update
>>> rs.close
>>> set rs = nothing
>>>
>>> I could use the "update" command on the 1st method, but I would like
>>> to use the 2nd method if possible. Is it possible to make the 2nd
>>> method above work ?
>>> Thank you.
>>>
>>> ***PS: I have no idea why the OP's text was not indented with the ">"
>>> char. It seems to have been only for this message.
>>>
>>> On Jan 8, 5:41 pm, "MikeD" wrote:
>>>
>>>
>>>
>>>> "fniles" wrote in message
>>>
>>>> news:%23oWviTkUIHA.5360@TK2MSFTNGP03.phx.gbl...
>>>
>>>>> On my machine in the office I change the computer setting to
>>>>> English (UK) so the date format is dd/mm/yyyy instead of
>>>>> mm/dd/yyyy for US.
>>>>> This problem happens in either Access or SQL Server.
>>>>> In the database I have a table with Date/time column. The database
>>>>> is located on a machine that is set to dd/mm/yyyy also.
>>>>> When I enter date 7/1/08 (as in January 7, 2008), it stores it in
>>>>> the database as 1/7/08 instead of 7/1/08. Why is it like that and
>>>>> how can I make the database stores it as 7/1/08 ?
>>>
>>>> If the data type of the column is truly one of the various "date"
>>>> data types, the format of the date is irrelevant. Don't worry about
>>>> it.
>>>
>>>> However, if the data type is actually text or characters, then
>>>> you've got a huge problem.
>>>
>>>> --
>>>> Mike
>>>> Microsoft MVP Visual Basic- Hide quoted text -
>>>
>>> - Show quoted text -
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>