Inserting date issue

[microjack]

I’ve finally made the jump from Access to SQL Express but I’m having some difficulty inserting a date using an asp/vbscript page.

I’ve trawled the web and persisted with trial and error and basically I’m down to 2 outcomes.

The first…

Formatting the date before insert, all combination of day month and year using / or - as a separator. The insert works but shows in the table as either 01/01/1900 or some other crazy combination.

The second…

Formatting the date as yyyymmdd as recommended up and down the web produces this error

Microsoft OLE DB Provider for ODBC Drivers (0x80040E57)
[Microsoft][SQL Native Client][SQL Server]Arithmetic overflow error converting expression to data type datetime.

I’m very new to SQL so I could be wrong but I’m starting to think this is a SQL configuration issue.

The SQL column is set to datetime
The user language (user defined in the connection string) is set to British English although I’ve tried plain English too.

Any ideas welcome.

[bwestover]

Have you tried this?

Convert(datetime,'YYYY-MM-DD')

[microjack]

Can that be used in an asp/vbscript page?

Sorry I should have made it clear I'm doing this via an asp page

[bwestover]

Sure, just put that into your SQL string with the rest of the SQL statement.

So if I had an ASP variable called strVar1 containing a date string of '2007-08-16' to insert that into sql I would do the following.

SQL = "Insert into DateField Select convert(datetime," & strVar1 & ")"