|
 |
asp_web_howto thread: Date format
Message #1 by william.sze@s... on Mon, 8 Apr 2002 11:18:00 -0400
|
|
Hi,
I have saved one record in a table1 as follows:
record_date begin_date end_date
2002-04-08 10:47:10.000 2002-04-08 10:47:10.000 2002-04-08
10:47:10.000
using page1.asp with query:
datNow = Now( )
oRS.addnew
oRS.Field("record_date") = datNow
oRS.Field("begin_date") = datNow
oRS.Field("end_date") = datNow
oRS.Update
Under other condition the above end_date is retrieved from database and put
in hidden field in page2.asp. Upon user submit, the end_date value is send
to page3.asp:
datNow = Now( )
datLastEndDate = request("End_Date")
response.write "dateLastDate=" & datLastEndDate
oRS.addnew
oRS.Field("record_date") = datNow
oRS.Field("begin_date") = datLastEndDate
oRS.Field("end_date") = datNow
oRS.Update
although the response.write statment gives me datLastEndDate =2002-04-08
10:47:10.000, the new record I have in my table1 is:
record_date begin_date end_date
2002-04-08 10:47:26.000 2002-08-04 10:47:10.000 2002-04-08
10:47:26.000
The begin_date field of second record has changed to Aug 04, instead of Apr
8.
The problem come after we restored the database from sql7.0 to sql2000. I
wonder sthg wrong with the set up of the restored db or the server.
Please help and advise how to troubleshoot and solve the problem.
Thanks
William
Message #2 by william.sze@s... on Mon, 8 Apr 2002 15:33:51 -0400
|
|
Problem solved by adding
datLastEndDate = CDate(request("End_Date"))
so for SQL2000, you need to convert the string to Date format but not
necessary for SQL 7.0
Thanks
William
William Sze
To: "ASP Web HowTo" <asp_web_howto@p...>
04/08/02 11:18 cc:
AM Subject: Date format(Document link: William Sze)
Hi,
I have saved one record in a table1 as follows:
record_date begin_date end_date
2002-04-08 10:47:10.000 2002-04-08 10:47:10.000 2002-04-08
10:47:10.000
using page1.asp with query:
datNow = Now( )
oRS.addnew
oRS.Field("record_date") = datNow
oRS.Field("begin_date") = datNow
oRS.Field("end_date") = datNow
oRS.Update
Under other condition the above end_date is retrieved from database and put
in hidden field in page2.asp. Upon user submit, the end_date value is send
to page3.asp:
datNow = Now( )
datLastEndDate = request("End_Date")
response.write "dateLastDate=" & datLastEndDate
oRS.addnew
oRS.Field("record_date") = datNow
oRS.Field("begin_date") = datLastEndDate
oRS.Field("end_date") = datNow
oRS.Update
although the response.write statment gives me datLastEndDate =2002-04-08
10:47:10.000, the new record I have in my table1 is:
record_date begin_date end_date
2002-04-08 10:47:26.000 2002-08-04 10:47:10.000 2002-04-08
10:47:26.000
The begin_date field of second record has changed to Aug 04, instead of Apr
8.
The problem come after we restored the database from sql7.0 to sql2000. I
wonder sthg wrong with the set up of the restored db or the server.
Please help and advise how to troubleshoot and solve the problem.
Thanks
William
Message #3 by "Ken Schaefer" <ken@a...> on Tue, 9 Apr 2002 13:56:50 +1000
|
|
Always format your dates in ISO format (yyyy/mm/dd), and use an SQL
statement (eg in a sproc), and then you wont have these problems any more
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <william.sze@s...>
Subject: [asp_web_howto] Re: Date format
:
: Problem solved by adding
:
: datLastEndDate = CDate(request("End_Date"))
:
: so for SQL2000, you need to convert the string to Date format but not
: necessary for SQL 7.0
:
: Thanks
: William
:
:
:
: William Sze
: To: "ASP Web HowTo"
<asp_web_howto@p...>
: 04/08/02 11:18 cc:
: AM Subject: Date format(Document
link: William Sze)
:
:
:
:
:
: Hi,
:
: I have saved one record in a table1 as follows:
:
: record_date begin_date end_date
: 2002-04-08 10:47:10.000 2002-04-08 10:47:10.000 2002-04-08
: 10:47:10.000
:
: using page1.asp with query:
:
: datNow = Now( )
: oRS.addnew
: oRS.Field("record_date") = datNow
: oRS.Field("begin_date") = datNow
: oRS.Field("end_date") = datNow
: oRS.Update
:
: Under other condition the above end_date is retrieved from database and
put
: in hidden field in page2.asp. Upon user submit, the end_date value is send
: to page3.asp:
:
: datNow = Now( )
: datLastEndDate = request("End_Date")
: response.write "dateLastDate=" & datLastEndDate
: oRS.addnew
: oRS.Field("record_date") = datNow
: oRS.Field("begin_date") = datLastEndDate
: oRS.Field("end_date") = datNow
: oRS.Update
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #4 by william.sze@s... on Tue, 9 Apr 2002 09:05:09 -0400
|
|
Ken,
Thanks for your reply. Do you mean always use the (yyyy/mm/dd) fomat on the
database or format the date into (yyyy/mm/dd) before sending to the sql
server? The reason I ask because my DBA have the following message to me
that confused me:
The safest way to handle this is to use standard ANSI date format (like '
yyyy-mm-dd') on the database side. If ANSI format is used, the database
server will get the right date regardless of server or client system
regional settings. You should use statements like:
UPDATE myTable set myDateField = '2002-04-15'
You can decide how to display the date in the application form.
My interpretation to is statment is you can have whatever date format in
your front end but must have (yyyy-mm-dd) format in SQL query.
I have one more question. Other developer's application also have date
problem with the new SQL2000 server. I wonder it is possible to set the
date format the same as the original SQL7.0 date format. How to check and
set the server format?
Thanks
William
"Ken Schaefer"
<ken@a... To: "ASP Web HowTo" <asp_web_howto@p...>
tic.com> cc:
Subject: [asp_web_howto] Re: Date format
04/08/02 11:56
PM
Please respond
to "ASP Web
HowTo"
Always format your dates in ISO format (yyyy/mm/dd), and use an SQL
statement (eg in a sproc), and then you wont have these problems any more
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <william.sze@s...>
Subject: [asp_web_howto] Re: Date format
:
: Problem solved by adding
:
: datLastEndDate = CDate(request("End_Date"))
:
: so for SQL2000, you need to convert the string to Date format but not
: necessary for SQL 7.0
:
: Thanks
: William
:
:
:
: William Sze
: To: "ASP Web HowTo"
<asp_web_howto@p...>
: 04/08/02 11:18 cc:
: AM Subject: Date
format(Document
link: William Sze)
:
:
:
:
:
: Hi,
:
: I have saved one record in a table1 as follows:
:
: record_date begin_date end_date
: 2002-04-08 10:47:10.000 2002-04-08 10:47:10.000 2002-04-08
: 10:47:10.000
:
: using page1.asp with query:
:
: datNow = Now( )
: oRS.addnew
: oRS.Field("record_date") = datNow
: oRS.Field("begin_date") = datNow
: oRS.Field("end_date") = datNow
: oRS.Update
:
: Under other condition the above end_date is retrieved from database and
put
: in hidden field in page2.asp. Upon user submit, the end_date value is
send
: to page3.asp:
:
: datNow = Now( )
: datLastEndDate = request("End_Date")
: response.write "dateLastDate=" & datLastEndDate
: oRS.addnew
: oRS.Field("record_date") = datNow
: oRS.Field("begin_date") = datLastEndDate
: oRS.Field("end_date") = datNow
: oRS.Update
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
---
Improve your web design skills with these new books from Glasshaus.
Usable Web Menus
http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
r-20
Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
r-20
Message #5 by "Ken Schaefer" <ken@a...> on Wed, 10 Apr 2002 17:02:25 +1000
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <william.sze@s...>
Subject: [asp_web_howto] Re: Date format
: Thanks for your reply. Do you mean always use the (yyyy/mm/dd) fomat on
the
: database or format the date into (yyyy/mm/dd) before sending to the sql
: server?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
A database *stores* a date as a number, usually the number of days after, or
before, some arbitrary starting date.
Suppose the starting date was 1/1/1900
Then the database would store 1/1/1900 12:00:00 AM as 1.0000000
It would store 2/1/1900 6:00:00 AM as 2.25000000
(the decimal part indicates the portion of the day that has passed). How the
database *displays* this information when you run a query depends on your
database or Windows settings.
Now, when you send date data to the database, you should send it in ISO
format: yyyy/mm/dd hh:mm:ss AM/PM
This is because there is no other possible format that is similar, so the
database can't get confused trying to work out exactly what date you are
sending it.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: The reason I ask because my DBA have the following message to me
: that confused me:
:
: The safest way to handle this is to use standard ANSI date format (like '
: yyyy-mm-dd') on the database side. If ANSI format is used, the database
: server will get the right date regardless of server or client system
: regional settings. You should use statements like:
: UPDATE myTable set myDateField = '2002-04-15'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I agree
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: You can decide how to display the date in the application form.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
That's correct - once you have extracted the date from the database you use
logic in your presentation layer to determine how to display it, eg:
www.adopenstatic.com/resources/code/formatdate.asp
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: My interpretation to is statment is you can have whatever date format in
: your front end but must have (yyyy-mm-dd) format in SQL query.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
That's correct
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: I have one more question. Other developer's application also have date
: problem with the new SQL2000 server. I wonder it is possible to set the
: date format the same as the original SQL7.0 date format. How to check and
: set the server format?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Check your Windows Regional Options settings:
www.adopenstatic.com/faq/systemdateformat.asp
Cheers
Ken
Message #6 by william.sze@s... on Wed, 10 Apr 2002 09:15:48 -0400
|
|
Ken,
Many thanks.
William
"Ken Schaefer"
<ken@a... To: "ASP Web HowTo" <asp_web_howto@p...>
tic.com> cc:
Subject: [asp_web_howto] Re: Date format
04/10/02 03:02
AM
Please respond
to "ASP Web
HowTo"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <william.sze@s...>
Subject: [asp_web_howto] Re: Date format
: Thanks for your reply. Do you mean always use the (yyyy/mm/dd) fomat on
the
: database or format the date into (yyyy/mm/dd) before sending to the sql
: server?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
A database *stores* a date as a number, usually the number of days after,
or
before, some arbitrary starting date.
Suppose the starting date was 1/1/1900
Then the database would store 1/1/1900 12:00:00 AM as 1.0000000
It would store 2/1/1900 6:00:00 AM as 2.25000000
(the decimal part indicates the portion of the day that has passed). How
the
database *displays* this information when you run a query depends on your
database or Windows settings.
Now, when you send date data to the database, you should send it in ISO
format: yyyy/mm/dd hh:mm:ss AM/PM
This is because there is no other possible format that is similar, so the
database can't get confused trying to work out exactly what date you are
sending it.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: The reason I ask because my DBA have the following message to me
: that confused me:
:
: The safest way to handle this is to use standard ANSI date format (like '
: yyyy-mm-dd') on the database side. If ANSI format is used, the database
: server will get the right date regardless of server or client system
: regional settings. You should use statements like:
: UPDATE myTable set myDateField = '2002-04-15'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I agree
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: You can decide how to display the date in the application form.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
That's correct - once you have extracted the date from the database you use
logic in your presentation layer to determine how to display it, eg:
www.adopenstatic.com/resources/code/formatdate.asp
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: My interpretation to is statment is you can have whatever date format in
: your front end but must have (yyyy-mm-dd) format in SQL query.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
That's correct
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: I have one more question. Other developer's application also have date
: problem with the new SQL2000 server. I wonder it is possible to set the
: date format the same as the original SQL7.0 date format. How to check and
: set the server format?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Check your Windows Regional Options settings:
www.adopenstatic.com/faq/systemdateformat.asp
Cheers
Ken
---
Improve your web design skills with these new books from Glasshaus.
Usable Web Menus
http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
r-20
Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
r-20
|
|
|
View Cart
