asked on

date conversion problem

"INSERT INTO Tracking(OfferteID, LevArtDetBarcode, Datum) VALUES (9332, '544900016932', '22/03/11 10:34:07')"

Open in new window

gives this error:
sqlerr.Message = "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated."

This sql statement runs on Mobile 6.5 with sql compact installed, date sourcefield is of type Datetime.
Target database is on a server in SQL server express 2008, target field is Datum of tyoe DateTime.

What am I doing wrong?

Jorge Paulino

try to add the date in this format instead mm/dd/yyyy

ASKER CERTIFIED SOLUTION

Imran Javed Zia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Jorge Paulino

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Ryan McCauley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

smano

Try

VALUES (.... '22/03/2011 10:34:07')

PagodNaUtak

"INSERT INTO Tracking(OfferteID, LevArtDetBarcode, Datum) VALUES (9332, '544900016932', '20110322 10:34:07')"

Open in new window

SOLUTION

OP_Zaharin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

OP_Zaharin

- you might also need to use CONVERT(yourdatevalue) function. look into the following article by on sql server date formatting hope it helps:

http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

SOLUTION

andr_gin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

IT-Factory

ASKER

You all gave me a hint to the right solution, thank.

Changed SQL to:
strSQL = "INSERT INTO Tracking(OfferteID, LevArtDetBarcode, Datum) VALUES (" & rdr("Bonnr") & ", '" & rdr("Barcode") & "', '" & Format(rdr("Datum"), "yyyy-M-dd HH:mm:ss") & "')"