Hello everybody....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

Why same problem is posted in so many forums...

Om Prakash

'DateTime is your date that you pass to it.

FormatDateTime(DateTime,0)

do you mean make my sql statement

sql = sql & " '"FormatDateTime(todaydt,0)'"

In your SQL statememts, for example

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&")"

instead of todaydt, you can Use SQL getDate function as :

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())"



Om Prakash

I posted in several different forums because different people look at different forums I wanted to maximize the possibility that I got an answer or a variety of answers that would work.

This date format problem gave me enough headache and finally I could fix it by following this strategy.

When date is stored into database, I will construct a string as below.

monthpart of date(MM) & "/" & datepart of date(DD) & "/" & year part of date(YYYY)

Then I use ' as the delimiter for sql server and # as the delimiter for ms access.

i.e. "'" & monthpart of date(MM) & "/" & datepart of date(DD) & "/" & year part of date(YYYY) & "'"

or
"#" & monthpart of date(MM) & "/" & datepart of date(DD) & "/" & year part of date(YYYY) & "#"

as the case may be.

While retrieving from database, I will extract date, month and year parts seperately and show it in whatever format required by the client.

This method works well with all servers of all local settings.