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?
LVL 2
IT-FactoryAsked:
Who is Participating?
 
Imran Javed ZiaConnect With a Mentor Consultant Software Engineer - .NET ArchitectCommented:
Please use 4 digit year and dd/MM/yyyy or yyyy-MM-dd format in date value field '22/03/11 10:34:07'
so try to use '2011-03-2210:34:07'
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
try to add the date in this format instead mm/dd/yyyy
0
 
Jorge PaulinoConnect With a Mentor IT Pro/DeveloperCommented:
opps, correction MM/dd/yyyy
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Ryan McCauleyConnect With a Mentor Data and Analytics ManagerCommented:
SQL Server isn't sure how to convert your DATETIME, since it's in DD/MM/YY, which is a little ambiguous (take 03/05/11 - March 5th or May 3rd?). In this case, it sees 22 as the month (since it assumes MM/DD/YY when it sees XX/XX/XX), and that's not a valid month.

I prefer to convert dates using the YYYY-MM-DD format, since it's clear what date you're referring to, so that would change your statement to:

INSERT INTO Tracking(OfferteID, LevArtDetBarcode, Datum) VALUES (9332, '544900016932', '2011-03-22 10:34:07')

Open in new window


As jpaulino mentioned, MM/DD/YYYY is also an acceptable format.
0
 
smanoCommented:
Try

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

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

Open in new window

0
 
OP_ZaharinConnect With a Mentor Commented:
- by default, the date format for SQL server is in U.S. date format MM/DD/YY, unless a localized version of SQL Server has been installed.

- you might want to run the following statement to check your default date format and formating your date data based on that format. make sure you are on "master" database to run this:

select name ,alias, dateformat
   from syslanguages
      where langid =
      (select value from master..sysconfigures
         where comment = 'default language')
0
 
OP_ZaharinCommented:
- 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/
0
 
andr_ginConnect With a Mentor Commented:
The valid date format depends on the language of the software you are using, regional settings, the software you are using and so on. There are not only the difference, if day or month stands first, but also delimiters, digits of the year and so on.

If you want to save your nerves encode every date string in the ISO standard:
2011-03-23T14:16:30

In .NET you can convert the date by calling DateTime.ToString("s")
0
 
IT-FactoryAuthor Commented:
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") & "')"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.