Inline
Saga
--
<fi************ *@SPAMMEgmail.c omwrote in message
news:45******** *************** ***********@v4g 2000hsf.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.CreateOb ject("ADODB.Rec ordset")
rs.ActiveConnec tion = 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.CreateOb ject("ADODB.Rec ordset")
rs.ActiveConnec tion = dbConnection
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimisti c
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" <nob...@nowhere .eduwrote:
"fniles" <fni...@pfmail. comwrote in message
news:%2******** ********@TK2MSF TNGP03.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