How to code DateTime-Literal in SQL Server

Hi,

I'm a newbee to SQL Server. I have a very simple question to you experts: How should I code a literal of the "datetime"-Datatype? For Example in the VALUES-clause of an SQL-statement. I have tested several "formats" ('20.04.2006 11:15:00' with an 4-digit year enclosed in single apostrophes) but all i earned is an exception!

Any help very appreciated!

Thanks in advance and best regards

Reiner

PS.: I'm using a german-localized database (thus the date-format dd.MM.yyyy).

I am not familiar with the german localized database, but I have some guesses...

You are doing the right thing, dates in SQL are inclosed by single '. Are your clients localized with the same culture as the database? Is so you should be able to

strsql = "INSERT INTO TABLE VALUES('" & datetime.tostring() & "')"
and the tostring method with format it properly.

Also try it with - instead of . in the date.

What I would do is open SQL Query Analyzer and write out the SQL insert query with a date and keep modifying the date format until it accepts it.

If none of this is a help then post the exception message.
|||

Look up date and time formats in books online. There are some ISO date time formates that are the best practice. Generally speaking, the formats are:

YYYYMMDD or YYYY-MM-DD

then time:

HH:MM:SS.SSS

There are variations, but the most important bits have to do with the year month date arrangement that is not ambiguous in any country

|||

There are only two unambiguous formats (one for date and another for datetime). Both are ISO formats.

YYYYMMDD - ISO unseparated date format

YYYY-MM-DDThh:mm:ss.nnn[ Z ] - ISO 8601 timestamp format (note there is no space after and before Z. I have to do it to prevent a stupid icon from showing up and there seems to be no way to prevent formatting.)

The ISO 8601 format is supported only from SQL Server 2000 onwards. Any other format interpretation depends on the language / session settings.