Access to SQL Date Problem

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&"','"&Phone&"','"&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,status ID,cuID)values("
sql = sql & " "&mastercusID&","&deaIDins&",'"&FirstName&"','"&LastName&"','"&ZipCode&"','"&Phone&"','"&Email&"',"
sql = sql & " '"&todaydt&"',1,1,1,1,1,"&cuID&")"
set inscustomers = conn.execute(sql)
gettingdeaID.movenext
wend

I had the same problem as you using UK dates and ended up writing an Access Function that converts a standard date to something SQL server Understands.


Function MakeSqlDate(Indate As Date) As String

On Error GoTo Err_MakeSqlDate

Dim myday As String

Dim mymonth As String

Dim Myyear As String

Dim Inday As Integer

Dim Inmonth As Integer

Dim InYear As Integer



Inday = Day(Indate)

If Inday < 10 Then

myday = "0" & CStr(Inday)

Else

myday = CStr(Inday)

End If

Inmonth = (Month(Indate))

If Inmonth < 10 Then

mymonth = "0" & CStr(Inmonth)

Else

mymonth = CStr(Inmonth)

End If



Myyear = CStr(Year(Indate))



MakeSqlDate = (mymonth & "/" & myday & "/" & Myyear)



Exit_MakeSqlDate:

Exit Function


I then use this to create a string from a date which SQL server then can understand.

Regards

Ted