|
vqitinc -> Moving from Access to SQL datetime issue. (7/4/2008 12:51:25)
|
I am migrating an application from MS Accss to MS SQL 2005. Most things moved OK, however, datetime is, of course an issue.
I need to insert a record into the database including a datetime field. On the data entry form, I have a simple JAVA Calendar date picker that places a string in a text box formatted like 02/02/2008. This worked fine for Access but not for SQL.
How can I use the current method to insert the date in SQL? Can I convert the 02/02/2008 to 20080202 00:00:00?
I currently use an if statement to ensure there is no null values for the date fields.
Here is my insert code:
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
If IsDate(Request.Form("PickupDate")) Then
PickupDate="#"&Request.Form("PickupDate")&"#"
Else PickupDate="Null"
End If
If IsDate(Request.Form("DeliveryDate")) Then
DeliveryDate="#"&Request.Form("DeliveryDate")&"#"
Else DeliveryDate="Null"
End If
If IsDate(Request.Form("CardExpireDate")) Then
CardExpireDate="#"&Request.Form("CardExpireDate")&"#"
Else CardExpireDate="Null"
End If
%>
<%
fp_sQry="INSERT INTO Shpmt (Accessorial01, Accessorial02, Accessorial03, Accessorial04, Accessorial05, AccessorialPrice01, AccessorialPrice02, AccessorialPrice03, AccessorialPrice04, AccessorialPrice05, FuelSurchargePrice, BillTo, DAddress1, DAddress2, DCellPhone, DCity, DCounty, DeliveryFlightsPrice, DeliveryFlightsQty, DeliveryPrice, DEmail, DestInstructions, DExt1, DExt2, DFax1, DFirstName, DLastName, DPrefix, DState, DTelephone1, DTelephone2, DZip, GeneralInstructions, OAddress1, OAddress2, OCellPhone, OCity, OCounty, OEmail, OExt1, OExt2, OFax1, OFirstName, OLastName, OPrefix, OriginFlightsPrice, OriginFlightsQty, OriginInstructions, OState, OTelephone1, OTelephone2, OZip, PickUpPrice, SalesRep, ModifiedDate, Contractor, BillToParty, PickupDate, DeliveryDate, Item, ItemMake, ItemModel, ItemSerialNo, ItemYear, ItemEstWeight, HeardAbout, CreditCardType, NameOnCard, CardNumber, CardExpireDate, CardSecurityCode, HeardAboutOther, ReferredByOther, Bonus, BFirstName, BLastName, BAddress1, BAddress2, BCity, BState, BZip, ReferredBy, PaymentApproveCode, HoldStatus, HoldReason) VALUES ('::Accessorial01::', '::Accessorial02::', '::Accessorial03::', '::Accessorial04::', '::Accessorial05::', ::AccessorialPrice01::, ::AccessorialPrice02::, ::AccessorialPrice03::, ::AccessorialPrice04::, ::AccessorialPrice05::, ::FuelSurchargePrice::, '::BillTo::', '::DAddress1::', '::DAddress2::', '::DCellPhone::', '::DCity::', '::DCounty::', ::DeliveryFlightsPrice::, '::DeliveryFlightsQty::', ::DeliveryPrice::, '::DEmail::', '::DestInstructions::', '::DExt1::', '::DExt2::', '::DFax1::', '::DFirstName::', '::DLastName::', '::DPrefix::', '::DState::', '::DTelephone1::', '::DTelephone2::', '::DZip::', '::GeneralInstructions::', '::OAddress1::', '::OAddress2::', '::OCellPhone::', '::OCity::', '::OCounty::', '::OEmail::', '::OExt1::', '::OExt2::', '::OFax1::', '::OFirstName::', '::OLastName::', '::OPrefix::', ::OriginFlightsPrice::, '::OriginFlightsQty::', '::OriginInstructions::', '::OState::', '::OTelephone1::', '::OTelephone2::', '::OZip::', ::PickUpPrice::, '::SalesRep::', '::ModifiedDate::', '::Contractor::', '::BillToParty::', "&PickupDate&", "&DeliveryDate&", '::Item::', '::ItemMake::', '::ItemModel::', '::ItemSerialNo::', '::ItemYear::', '::ItemEstWeight::', '::HeardAbout::', '::CreditCardType::', '::NameOnCard::', '::CardNumber::', "&CardExpireDate&", '::CardSecurityCode::', '::HeardAboutOther::', '::ReferredByOther::', ::Bonus::, '::BFirstName::', '::BLastName::', '::BAddress1::', '::BAddress2::', '::BCity::', '::BState::', '::BZip::', '::ReferredBy::', '::PaymentApproveCode::', '::HoldStatus::', '::HoldReason::')"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=16 align=""LEFT"" width=""100%""></td></tr>"
fp_sDataConn="smssql"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
Thanks
Allan Browning
|
|
|
|
