inserting datetime

hi, i'm using sql server db and i try to insert datetime into db.Can some body check for me what is the syntax error?Thanks

the FORMAT function is not valid for sql server

you can use the dd/mm/yy format, but you may need to set sql server's dateformat option first:

SQL server requires you to insert dates in US date format. What ever your date is run it thru a function that does this conversion. The below function converts a dd/mm/yyy (non american format) mm/dd/yyyy (american format) into a mm/dd/yyyy (american format)

FUNCTION amDate(varDate)
amDate = "'" & Month(DateValue(varDate)) & "/" & Day(DateValue(varDate)) & "/" & Year(DateValue(varDate)) & " " & TimeValue(varDate) & "'"
END FUNCTION

when you do you insert call the function
sql = "INSERT INTO tableName (yourDateFieldname, fieldname1 , fieldname2) VALUES (amdate(yourDateValue), someValue1, someValue2);

Ensure your data type for your date field in design view is set to date. You should always set fields that hold dates to date format so you may 'search on date ranges'

If you have trouble inserting. Response.write your date before the function / run the function and write the value again. if this looks ok check your data type for the field, lastly look at your single and double quotes inside your ststements (the function places single quotes around the returned value!!!)

mat41, i've been using ISO standard yyyy-mm-dd dates in sql server for years, nobody ever told me i had to use US format

the date example given was in a format that was not identifyable to me 05/03/2003 (or something like that - no day or month number over 12). Have you ever inserted a dd/mm/yyyy format in to sql server with success?

If so which version, if not give it a go? I will re try it to satisfy my curiosity however I think you will find it errors out

What format do you use in Canada? I guess I asumed it was US.

i have inserted mm/dd/yyyy, dd/mm/yyyy, yyyy-mm-dd, and even yyyy-dd-mm

for two of these you have to set dateformat dmy