Insert Date into SQL

[vlitim]

I am trying to insert a date into an SQl dbase and I can't get it to work.

if I use:

SQLupdate="UPDATE news SET ondate='"& news_ondate &"' WHERE id="&nid&""

I get the error:

[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

if I remove the quotes around the date it inserts into the databse but always as 01/01/1900

the date is being passed from a from so it is just a text field.

please someone help me!

 

[Guest_imported]

What's the varable news_ondate look like? I used to have problems with date entry, but I set the field format to smalldatatime and usually declare the variuable in the SQL format i.e.

yyyymmdd

So for example todays date 11th Ocober 2001 would look like this:

20011011

I've never had problems since.

Hope this helps

 

[vlitim]

how do you convert a date format from 15/12/01 to 20011215??

 

[stakadush]

here's a function i use to convert dates to sql format:

Dim dtDateTime
dtDateTime = FormatTS(Now)

Function FormatTS(dteDate)
	Dim dte
	Dim Res,Temp
	on error resume next

	If IsDate(dteDate) Then
		dte = CDate(dteDate)
	Else
		dte = Now()
	End If

	Res = Right("0000"& Year(dte),4) &"-"& _
	Right("00"& Month(dte),2) &"-"& _
	Right("00"& Day(dte),2) &" "& _
	Right("00"& Hour(dte),2) &":"& _
	Right("00"& Minute(dte),2) &":"& _
	Right("00"& Second(dte),2)

	FormatTS = "{ts '"& Res &"'}"
End Function