Which date-format to use with SQL-server?

  • Thread starter Thread starter Christoph Hilmes
  • Start date Start date
C

Christoph Hilmes

Hi NG,

i am using vb.Net and ado.Net to save data in a database. Now i´m trying to
update a datetime-field in a sql-server-database. I use an sql-string like
this

UPDATE Table SET gebdat='15.10.1972'

If i do this by using a command-object (ado.net) i get no error message, but
if i try to send this command directly to the database using the query
analyser i get an error message like this

The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value

Then i tried to use a different format:

UPDATE Table SET gebdat='10-15-1972'

This one works fine with the query analyser but when i use it from within my
program using the command-obejct i get the same error message that i got
from the query analyser using the first statement.

??????

I have also tried '10/15/1972' and '1972-10-15' and '1972/10/15' but i
always get the same result.

What dateformat do i have to use, so both my database and ado.net can deal
with it?

Any help is much appreaciated ... otherwise i have to change my
database-field to a string-field :-(

Christoph
 
Christoph Hilmes said:
Hi NG,

i am using vb.Net and ado.Net to save data in a database. Now i´m trying
to
update a datetime-field in a sql-server-database. I use an sql-string like
this

UPDATE Table SET gebdat='15.10.1972'
Click to expand...

The correct answer is none. Don't send dates as strings. Use a parameter.

UPDATE Table SET gebdat=@gebdat

Bind a SqlParameter with the appropriate type, and the driver will convert
from a .NET Date to a SQL DateTime.

The other answer is 'YYYY-MM-DD HH24:MM:SS' (ODBC Cannonical format) is
widely understood, location independant, and sorts correctly.

David
 
Chrisoph,

In addition to the others.

Use as possible forever the
new date(year,month,day)
that does not give you globalizations problems.

Cor
 
Hi,

To eliminate most of the issues, use the SqlDateTime object.

HTH,
Rakesh Rajan
 
Back
Top