inserting american & UK dates into a SQL2000 Server?? 1

[joebickley]

Hi all
I am inserting a simple record into a SQL server table i can get this to work ok with the exception of the date field. The users type a date into a box on the form in uk format eg 28/02/02 (dd/mm/yy) when this inserts in to my SQL server database it is trying to put it in as an american date ie 28/02/02 (mm/dd/yy). This falls over as the format is incorerect. Both client and server use the UK date format. Below is the query it parses.

MM_tableValues - ' this is the string with the database table field names
MM_dbValues - ' this is the string with the valus picked up off the form
[tt]
SQLTEXT = "insert into manufdata (" & MM_tableValues & ", resultdate" & &quot values (" & MM_dbValues & ",'" & request.form("resdate&quot & "')"
[/tt]
I think i need to do something with the date but i cant figure out what

thanks

joe

 

[iowtech]

It is my understanding (and I'm happy to be corrected in this) that SQL, as in the language itself, assumes a US format date. Personally I use a functions as below to do conversions between the date formats before posting to the DB:

function britdate(thedate)
if isdate(thedate) = false then
britdate = ""
end if
p1 = instr(1,thedate,"/&quot
p2 = instr(p1+1,thedate,"/&quot
d = left(thedate,p1 - 1)
m = mid(thedate, p1 + 1,p2 - p1 - 1)
y = mid(thedate,p2+1)
britdate = d & "/" & m & "/" & y
end function

function usdate(thedate)
if isdate(thedate) = false then
usdate = ""
end if
p1 = instr(1,thedate,"/&quot
p2 = instr(p1+1,thedate,"/&quot
d = left(thedate,p1 - 1)
m = mid(thedate, p1 + 1,p2 - p1 - 1)
y = mid(thedate,p2+1)
usdate = m & "/" & d & "/" & y
end function

 

[fhlee]

Hi,
Alternatively, you can use the following short cut:

::Using your current code::
SQLTEXT = "insert into manufdata (" & MM_tableValues & ", resultdate" & &quot values (" & MM_dbValues & ",'" & formatDateTime( request.form("resdate&quot, vbLongDate) & "')"


Note that it forces your Request.Form("ResDate&quot into a long date/time string. When SQL receives this, it will automatically convert the long date/time into the appropriate internal format. This code is both space and computation efficient. It is also machine independant (ie, in future, if you port your code from a UK setting server to a US setting server, it does not require a code re-write).

regards,
- Joseph
=================== ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Visit -->

http://www.replicaSi.com

for (Replica) Watches, Pen, Handbags, Hats, Jerseys and more ... at prices that makes your code spin ...
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

 

[joebickley]

Hi i tried puting in your usdate function but it wont work. It i have it as a vbscript function it says invalid datatype and if i include it im the ASP tags it comes up invalid call or procedure and points at where u used the left (xxxx) function

any ideas

 

[fhlee]

Hi,
Appologies, it should read:
formatDateTime( CDate( request.form("resdate&quot ), vbLongDate)

*** Note the CDate (to convert your text field in request.form("resdate&quot first to a date var before being converted back to long date/time.

cheers.

regards,
- Joseph
============ ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Visit -->

http://www.replicaSi.com

for (Replica) Watches, Pen, Handbags, Hats, Jerseys and more ... at prices that makes your code spin ...
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

 

[joebickley]

fhlee


YOU ARE MY HERO !!!!!! IT WORKS THANKS A LOT MATE I THOUGHT IT WOULD BE SOMETHING SIMPLE



Joe

 

[chiph]

Joe -
You might want to do a web search on the ISO-8601 date format. If you require your users to enter according to this format, you'll never have a conversion problem.

Chip H.