Date Problems

[rn5a]

In my local computer, date has been set in this format - dd/MM/yyyy.
When I insert records in a MS-Access DB table using ASP.NET, then the
records get inserted in the Access DB table exactly in the same format
as what has been set in my local machine. For e.g. if today is 21st
February 2007 & the time is 10:45:32 AM, then this record gets
inserted in the Access DB table as

21/02/2007 10:45:32 AM

But when I try to insert the same record in the same Access DB table
using ASP, the record gets inserted as

02/21/2007 10:45:32 AM

i.e. the month (02) is displayed & then the day (21) is displayed &
this is causing problems for me since later in the application, I am
using the DataDiff function to find the number of days that have
elapsed since the record was inserted & todays date.

Can someone please suggest me a solution to rersolve this? It's
driving me crazy.

The column in the Access DB table where the date records get inserted
has the Date/Time data type. So Access isn't even allowing me insert
the date records in that column after I break-up the entire date into
day, month & year using DatePart. FOr e.g. the following code breaks
up todays date into day, month & year:

<%
only the date part is shown; the time part has been omitted
Dim strDay, strMonth, strYear, dtToday

strDay=DatePart("d",Now)
strMonth=DatePart("m",Now)
strYear=DatePart("yyyy",Now)

dtToday=strDay & "/" & strMonth & "/" & strYear

Set objRS=objConn.Execute("INSERT INTO ODate VALUES ('" & dtToday
& "')"
%>

The above produces the data type mismatch error.

 

[Anthony Jones]

In my local computer, date has been set in this format - dd/MM/yyyy.
When I insert records in a MS-Access DB table using ASP.NET, then the
records get inserted in the Access DB table exactly in the same format
as what has been set in my local machine. For e.g. if today is 21st
February 2007 & the time is 10:45:32 AM, then this record gets
inserted in the Access DB table as

21/02/2007 10:45:32 AM

But when I try to insert the same record in the same Access DB table
using ASP, the record gets inserted as

02/21/2007 10:45:32 AM

i.e. the month (02) is displayed & then the day (21) is displayed &
this is causing problems for me since later in the application, I am
using the DataDiff function to find the number of days that have
elapsed since the record was inserted & todays date.

Can someone please suggest me a solution to rersolve this? It's
driving me crazy.

The column in the Access DB table where the date records get inserted
has the Date/Time data type. So Access isn't even allowing me insert
the date records in that column after I break-up the entire date into
day, month & year using DatePart. FOr e.g. the following code breaks
up todays date into day, month & year:

<%
only the date part is shown; the time part has been omitted
Dim strDay, strMonth, strYear, dtToday

strDay=DatePart("d",Now)
strMonth=DatePart("m",Now)
strYear=DatePart("yyyy",Now)

dtToday=strDay & "/" & strMonth & "/" & strYear

Set objRS=objConn.Execute("INSERT INTO ODate VALUES ('" & dtToday
& "')"
%>

The above produces the data type mismatch error.

First off, lets be clear that date/time data has no text format when stored
in the DB.

When using SQL code such as above the date format is MM/DD/YYYY regardless
of your locale settings.

 

[Bob Barrows [MVP]]

In my local computer, date has been set in this format - dd/MM/yyyy.

Stop. That you consider this to be relevant tells me that you need to read
all three of these articles:
http://www.aspfaq.com/show.asp?id=2313 vbscript
http://www.aspfaq.com/show.asp?id=2040 help with dates
http://www.aspfaq.com/show.asp?id=2260 dd/mm/yyy confusion

 

[Bob Barrows [MVP]]

Set objRS=objConn.Execute("INSERT INTO ODate VALUES ('" & dtToday
& "')"
%>

Also:
Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]