Hello everyvery body....I am in the process of converting a database driven site from Access to MS SQL Server 2000.

Everything works fine but I am having trouble with dates. As we know Access has a way to store a Date and a seperate way to store the time. SQL does not. It handles both items as a Datetime data type.

In the code below I have the variable todaydt assigned to now(). which makes it a string and SQL will not accept it into a datetime field as a string. So I need to convert it to datetime format.

I have found several websites that talk about stored procedures and using it to convert the string to a date format sql will except, but I have know idea how to apply that to my sql statement below.

For more information after it inserts this information into the database it contunues to use the information to insert into other tables after an inner join or two...these new tables will also require the same date. It then pulls information based on the date and time submitted to pass back to an autoresponse page.

I know big explanation but I wanted to be thorough....

So how do I convert the string to a datetime format using either asp or a Stored procedure that I can call durring??? my sql statement??? or if someone has a better idea lay it on me. I have never used stored procedures before so treat me like I am five and hold my hand here.

Thanks in advance

todaydt = now()

'new sql code for sql server
sql = "insert into mastercustomers("
sql = sql & "fname,lname,zipcode,phone,email,"
sql = sql & "datetimesubmitted)values("
sql = sql & " '"&FirstName&"','"&LastName&"','"&ZipCode&"','"&Ph one&"','"&Email&"',"
sql = sql & " '"&todaydt&"')"
set rs = conn.execute(sql)
sql = "select mastercusID from mastercustomers where (fname='"&FirstName&"' and lname='"&LastName&"' and email='"&email&"' and and datetimesubmitted='"&todaydt&"')"
set getmastercusID = conn.execute(sql)
mastercusID = getmastercusID("mastercusID")

sql = "SELECT DISTINCT dealerships.deaID "
sql = sql & "FROM makers INNER JOIN (dealercodes INNER JOIN dealerships ON dealercodes.deaID = dealerships.deaID)ON makers.makerID = dealerships.makerID "
sql = sql & "where dealerships.dealzipcode='"&ZipCode&"' AND dealerships.dactive=1 AND makers.maker='"&SubDivisionName&"' "
set gettingdeaID = conn.execute(sql)
while not gettingdeaID.eof
deaIDins = gettingdeaID("deaID")

sql = "insert into customers("
sql = sql & "mastercusID,deaID,fname,lname,zipcode,phone,email , "
sql = sql & "datetimesubmitted,activeS,activeM,activeABS,statu s ID,cuID)values("
sql = sql & " "&mastercusID&","&deaIDins&",'"&FirstName&"','"&La stName&"','"&ZipCode&"','"&Phone&"','"&Email&"',"
sql = sql & " '"&todaydt&"',1,1,1,1,1,"&cuID&")"
set inscustomers = conn.execute(sql)
gettingdeaID.movenext
wend

Please check the following:

http://p2p.wrox.com/topic.asp?TOPIC_ID=25167

Om Prakash

;;;So how do I convert the string to a datetime format using either asp or a Stored procedure

Neither. Use VBScripts cDate function:
Returns an expression that has been converted to a Variant of subtype Date
cDate(expression)

BTW: "Use the IsDate function to determine if date can be converted to a date or time"
IsDate(expression)
Result: Returns a Boolean value indicating whether an expression can be converted to a date

Wind is your friend
Matt