|
 |
asp_databases thread: Dates & formats
Message #1 by "ctxultra" <andy@b...> on Thu, 25 May 2000 12:32:44
|
|
I've got an app that runs on an NT server in the UK, using UK regional
settings, ie date format dd/mm/yy. My app writes a date into a table of an
Access db, based on a period (drawn as an integer from the DB), and today's
date eg:
Set cmdDLP = Server.CreateObject("ADODB.Command")
Set rsDLP = Server.CreateObject("ADODB.Recordset")
cmdDLP.CommandText = SQLQueryDLP
cmdDLP.CommandType = 1
Set cmdDLP.ActiveConnection = dbDLP
rsDLP.Open cmdDLP, , 1, 3
DLP = rsDLP("DLP")
Set dbADD = Server.CreateObject("ADODB.Connection")
dbADD.ConnectionTimeout = Session("spromania_ConnectionTimeout")
dbADD.CommandTimeout = Session("spromania_CommandTimeout")
dbADD.Open Session("spromania_ConnectionString"),
Session("spromania_RuntimeUserName"), Session("spromania_RuntimePassword")
SQLQuery = "Select * From tbDownload Where D_ID = 1"
Set cmdADD = Server.CreateObject("ADODB.Command")
Set rsADD = Server.CreateObject("ADODB.Recordset")
cmdADD.CommandText = SQLQuery
cmdADD.CommandType = 1
Set cmdADD.ActiveConnection = dbADD
rsADD.Open cmdADD, , 1, 3
rsADD.addnew
rsADD.Fields("PP_ID") = request("PP_ID")
rsADD.Fields("U_ID") = request("U_ID")
rsADD.Fields("D_DateAdd") = Date()
rsADD.Fields("D_Expiry") = Date()+DLP
rsADD.update
rsADD.close
dbADD.close
set rsADD=nothing
Set dbADD=nothing
I then use this D_Expiry on another page to decide whether a link should
appear:
ElseIf rsDown("D_Expiry") >= Date() then
blah blah blah.
I found that this clause was always coming back as false, so I wrote the
values of D_expiry and Date() to the page. Both were in the format
mm/dd/yy. Both server & client are using dd/mm/yy. Andy ideas?
Andy
Message #2 by "Myle Pham" <myle.pham@n...> on Thu, 25 May 2000 09:02:32 -0600
|
|
Hi Andy,
To my experience, SQL in ASP behave differently when it is dealing with
date. It need to be converted to date before insert into database.
Try using converting function: to_date()
This is a Sample:
SQL = "INSERT INTO reports (DateTime) values (to_date('"&
Session("DateEnter")& "', 'DD-MM-YYYY'));"
Cheers
Myle
-----Original Message-----
From: ctxultra
Sent: Thursday, May 25, 2000 6:33 AM
To: ASP Databases
Subject: [asp_databases] Dates & formats
I've got an app that runs on an NT server in the UK, using UK regional
settings, ie date format dd/mm/yy. My app writes a date into a table of an
Access db, based on a period (drawn as an integer from the DB), and today's
date eg:
Set cmdDLP = Server.CreateObject("ADODB.Command")
Set rsDLP = Server.CreateObject("ADODB.Recordset")
cmdDLP.CommandText = SQLQueryDLP
cmdDLP.CommandType = 1
Set cmdDLP.ActiveConnection = dbDLP
rsDLP.Open cmdDLP, , 1, 3
DLP = rsDLP("DLP")
Set dbADD = Server.CreateObject("ADODB.Connection")
dbADD.ConnectionTimeout = Session("spromania_ConnectionTimeout")
dbADD.CommandTimeout = Session("spromania_CommandTimeout")
dbADD.Open Session("spromania_ConnectionString"),
Session("spromania_RuntimeUserName"), Session("spromania_RuntimePassword")
SQLQuery = "Select * From tbDownload Where D_ID = 1"
Set cmdADD = Server.CreateObject("ADODB.Command")
Set rsADD = Server.CreateObject("ADODB.Recordset")
cmdADD.CommandText = SQLQuery
cmdADD.CommandType = 1
Set cmdADD.ActiveConnection = dbADD
rsADD.Open cmdADD, , 1, 3
rsADD.addnew
rsADD.Fields("PP_ID") = request("PP_ID")
rsADD.Fields("U_ID") = request("U_ID")
rsADD.Fields("D_DateAdd") = Date()
rsADD.Fields("D_Expiry") = Date()+DLP
rsADD.update
rsADD.close
dbADD.close
set rsADD=nothing
Set dbADD=nothing
I then use this D_Expiry on another page to decide whether a link should
appear:
ElseIf rsDown("D_Expiry") >= Date() then
blah blah blah.
I found that this clause was always coming back as false, so I wrote the
values of D_expiry and Date() to the page. Both were in the format
mm/dd/yy. Both server & client are using dd/mm/yy. Andy ideas?
Andy
Message #3 by "Ken Schaefer" <ken.s@a...> on Fri, 26 May 2000 10:00:43 +1000
|
|
This is a common Access problem:
When you insert a date into an Access database, if the date can be construed
as a valid date in mm/dd/yyyy format, then Access will interprete the date
in that format.
If the date can't be construed as a valid date in mm/dd/yyyy format, but it
can be construed in dd/mm/yyyy format, then it will be stored as dd/mm/yyyy
NOTE: your regional settings have *nothing* to do with this. They only
affect the format which the date is displayed in when you open the database.
The actual date that is to be stored though it determined by the rules
above.
How do you avoid this? My preferred solution is to insert all dates in
yyyy/mm/dd format. Every database that I know will accept dates in this
format, and not even the Americans are perverse enough to come up with a
yyyy/dd/mm format to break it.
However this will involved recoding some of your pages... :-) Have fun.
Cheers
Ken
----- Original Message -----
From: "ctxultra"
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, May 25, 2000 12:32 PM
Subject: [asp_databases] Dates & formats
> I've got an app that runs on an NT server in the UK, using UK regional
> settings, ie date format dd/mm/yy. My app writes a date into a table of
an
> Access db, based on a period (drawn as an integer from the DB), and
today's
> date eg:
>
> Set cmdDLP = Server.CreateObject("ADODB.Command")
> Set rsDLP = Server.CreateObject("ADODB.Recordset")
> cmdDLP.CommandText = SQLQueryDLP
> cmdDLP.CommandType = 1
> Set cmdDLP.ActiveConnection = dbDLP
> rsDLP.Open cmdDLP, , 1, 3
>
> DLP = rsDLP("DLP")
>
>
> Set dbADD = Server.CreateObject("ADODB.Connection")
> dbADD.ConnectionTimeout = Session("spromania_ConnectionTimeout")
> dbADD.CommandTimeout = Session("spromania_CommandTimeout")
> dbADD.Open Session("spromania_ConnectionString"),
> Session("spromania_RuntimeUserName"), Session("spromania_RuntimePassword")
>
> SQLQuery = "Select * From tbDownload Where D_ID = 1"
>
> Set cmdADD = Server.CreateObject("ADODB.Command")
> Set rsADD = Server.CreateObject("ADODB.Recordset")
> cmdADD.CommandText = SQLQuery
> cmdADD.CommandType = 1
> Set cmdADD.ActiveConnection = dbADD
> rsADD.Open cmdADD, , 1, 3
>
> rsADD.addnew
>
> rsADD.Fields("PP_ID") = request("PP_ID")
> rsADD.Fields("U_ID") = request("U_ID")
> rsADD.Fields("D_DateAdd") = Date()
> rsADD.Fields("D_Expiry") = Date()+DLP
> rsADD.update
>
> rsADD.close
> dbADD.close
> set rsADD=nothing
> Set dbADD=nothing
>
> I then use this D_Expiry on another page to decide whether a link should
> appear:
>
> ElseIf rsDown("D_Expiry") >= Date() then
>
> blah blah blah.
>
> I found that this clause was always coming back as false, so I wrote the
> values of D_expiry and Date() to the page. Both were in the format
> mm/dd/yy. Both server & client are using dd/mm/yy. Andy ideas?
>
> Andy
>
>
Message #4 by Mark Everest <Mark.Everest@t...> on Fri, 26 May 2000 09:27:20 +0100
|
|
I think the issue may be here the local on the server.
Remember, IIS is a service so will use the local of the system account -
this is normally US by default unless it is changed.
If this is an issue, then a resolution is to do the following in the
session_onstart
session.LCID = 2057 ' i.e. GB_UK, or whatever locale is required.
-----Original Message-----
From: Myle Pham []
Sent: 25 May 2000 16:03
To: ASP Databases
Subject: [asp_databases] RE: Dates & formats
Hi Andy,
To my experience, SQL in ASP behave differently when it is dealing with
date. It need to be converted to date before insert into database.
Try using converting function: to_date()
This is a Sample:
SQL = "INSERT INTO reports (DateTime) values (to_date('"&
Session("DateEnter")& "', 'DD-MM-YYYY'));"
Cheers
Myle
-----Original Message-----
From: ctxultra
Sent: Thursday, May 25, 2000 6:33 AM
To: ASP Databases
Subject: [asp_databases] Dates & formats
I've got an app that runs on an NT server in the UK, using UK regional
settings, ie date format dd/mm/yy. My app writes a date into a table of an
Access db, based on a period (drawn as an integer from the DB), and today's
date eg:
Set cmdDLP = Server.CreateObject("ADODB.Command")
Set rsDLP = Server.CreateObject("ADODB.Recordset")
cmdDLP.CommandText = SQLQueryDLP
cmdDLP.CommandType = 1
Set cmdDLP.ActiveConnection = dbDLP
rsDLP.Open cmdDLP, , 1, 3
DLP = rsDLP("DLP")
Set dbADD = Server.CreateObject("ADODB.Connection")
dbADD.ConnectionTimeout
Session("spromania_ConnectionTimeout")
dbADD.CommandTimeout
Session("spromania_CommandTimeout")
dbADD.Open Session("spromania_ConnectionString"),
Session("spromania_RuntimeUserName"), Session("spromania_RuntimePassword")
SQLQuery = "Select * From tbDownload Where D_ID = 1"
Set cmdADD = Server.CreateObject("ADODB.Command")
Set rsADD = Server.CreateObject("ADODB.Recordset")
cmdADD.CommandText = SQLQuery
cmdADD.CommandType = 1
Set cmdADD.ActiveConnection = dbADD
rsADD.Open cmdADD, , 1, 3
rsADD.addnew
rsADD.Fields("PP_ID") = request("PP_ID")
rsADD.Fields("U_ID") = request("U_ID")
rsADD.Fields("D_DateAdd") = Date()
rsADD.Fields("D_Expiry") = Date()+DLP
rsADD.update
rsADD.close
dbADD.close
set rsADD=nothing
Set dbADD=nothing
I then use this D_Expiry on another page to decide whether a link should
appear:
ElseIf rsDown("D_Expiry") >= Date() then
blah blah blah.
I found that this clause was always coming back as false, so I wrote the
values of D_expiry and Date() to the page. Both were in the format
mm/dd/yy. Both server & client are using dd/mm/yy. Andy ideas?
Andy
Message #5 by Mark Everest <Mark.Everest@t...> on Fri, 26 May 2000 13:27:42 +0100
|
|
That't good to know - another date format that works in all databases (I
think) is dd-MMM-yyyy (i.e. 28-MAY-2000).
-----Original Message-----
From: Ken Schaefer
Sent: 26 May 2000 01:01
To: ASP Databases
Subject: [asp_databases] Re: Dates & formats
This is a common Access problem:
When you insert a date into an Access database, if the date can be construed
as a valid date in mm/dd/yyyy format, then Access will interprete the date
in that format.
If the date can't be construed as a valid date in mm/dd/yyyy format, but it
can be construed in dd/mm/yyyy format, then it will be stored as dd/mm/yyyy
NOTE: your regional settings have *nothing* to do with this. They only
affect the format which the date is displayed in when you open the database.
The actual date that is to be stored though it determined by the rules
above.
How do you avoid this? My preferred solution is to insert all dates in
yyyy/mm/dd format. Every database that I know will accept dates in this
format, and not even the Americans are perverse enough to come up with a
yyyy/dd/mm format to break it.
However this will involved recoding some of your pages... :-) Have fun.
Cheers
Ken
----- Original Message -----
From: "ctxultra"
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, May 25, 2000 12:32 PM
Subject: [asp_databases] Dates & formats
> I've got an app that runs on an NT server in the UK, using UK regional
> settings, ie date format dd/mm/yy. My app writes a date into a table of
an
> Access db, based on a period (drawn as an integer from the DB), and
today's
> date eg:
>
> Set cmdDLP = Server.CreateObject("ADODB.Command")
> Set rsDLP = Server.CreateObject("ADODB.Recordset")
> cmdDLP.CommandText = SQLQueryDLP
> cmdDLP.CommandType = 1
> Set cmdDLP.ActiveConnection = dbDLP
> rsDLP.Open cmdDLP, , 1, 3
>
> DLP = rsDLP("DLP")
>
>
> Set dbADD = Server.CreateObject("ADODB.Connection")
> dbADD.ConnectionTimeout = Session("spromania_ConnectionTimeout")
> dbADD.CommandTimeout = Session("spromania_CommandTimeout")
> dbADD.Open Session("spromania_ConnectionString"),
> Session("spromania_RuntimeUserName"), Session("spromania_RuntimePassword")
>
> SQLQuery = "Select * From tbDownload Where D_ID = 1"
>
> Set cmdADD = Server.CreateObject("ADODB.Command")
> Set rsADD = Server.CreateObject("ADODB.Recordset")
> cmdADD.CommandText = SQLQuery
> cmdADD.CommandType = 1
> Set cmdADD.ActiveConnection = dbADD
> rsADD.Open cmdADD, , 1, 3
>
> rsADD.addnew
>
> rsADD.Fields("PP_ID") = request("PP_ID")
> rsADD.Fields("U_ID") = request("U_ID")
> rsADD.Fields("D_DateAdd") = Date()
> rsADD.Fields("D_Expiry") = Date()+DLP
> rsADD.update
>
> rsADD.close
> dbADD.close
> set rsADD=nothing
> Set dbADD=nothing
>
> I then use this D_Expiry on another page to decide whether a link should
> appear:
>
> ElseIf rsDown("D_Expiry") >= Date() then
>
> blah blah blah.
>
> I found that this clause was always coming back as false, so I wrote the
> values of D_expiry and Date() to the page. Both were in the format
> mm/dd/yy. Both server & client are using dd/mm/yy. Andy ideas?
>
> Andy
>
>
|
|
|
View Cart
