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

Not sure what exactly you are trying to achieve, you gave lot of inormation and I am confused..

IN SQL Server 2000 you can use GETDATE() to insert current datetime

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 & " '"& GETDATE() &"')"


Also, use CONVERT() function if you want to explicitly convert into datetime from a string.

basically this website is designed to give a quote for new cars. The customer information and vehicle information is placed into a table "mastercustomers" we then grab the "dealerships" from a another table, that deal with that make and model of car. Last we input the vehicle info, customer info, and dealer info into a new table "customers" so tyhat we can generate an email to each distinct dealership that has that type of vehicle and email the quote request to each one. We are kind of a middle man between customer and dealer.

After that explaination....I purposely left out some of the code to make my post smaller. I am really only interested in making my datefield work. I would also like to have a time field as well so that we can tell when the quote request was posted.

Based on your post would the last part of the SQL Statement look like this

sql = sql & " '"& (CONVERT(CHAR(10),GETDATE(),110)) &"')"

sorry I had a typo

like this?

sql = sql & " '"& (CONVERT(datetime(8),GETDATE(),110)) &"')"

or this? Using my "todaydt" string

sql = sql & " '"& (CONVERT(datetime(8),todaydt,110)) &"')"

What is the datatype you are using for your column? If you using datetime then GETDATE() should work without explicitly coverting it. If you are using varchar or char as your column datatype then use convert() to explicitly convert it.

I really don't think I am getting my point across.

I don't want to rewrite my earlier posts. If I was just using the date in one table this would be no problem as a matter of fact I would just have SQL Server set the column's default value to getdate()

I need the exact date and time to use in other tables and they have to match. Getdate returns the month day and year + minutes second etc. So that I can send data based upon this exact date and time to people.

Let me try to ask my question in a different way. I posted three SQL statements above...the first and last both require the "todaydt" or getdate() inorder for these two dates/times to be exact I can't use getdate can I? Because the seconds ...or miliseconds... would change and there fore the two would not match. I would not be able to recover the data?

Am I correct in this?

Secondly...I have obviosly never used the part of SQL Before so can someone please write out a SQL Staement that would work.