• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 574
  • Last Modified:

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?
5 Solutions
Jorge PaulinoIT Pro/DeveloperCommented:
try to add the date in this format instead mm/dd/yyyy
Imran Javed ZiaConsultant 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'
Jorge PaulinoIT Pro/DeveloperCommented:
opps, correction MM/dd/yyyy
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Ryan McCauleyData 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.

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

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

Open in new window

- 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')
- you might also need to use CONVERT(yourdatevalue) function. look into the following article by on sql server date formatting hope it helps:

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:

In .NET you can convert the date by calling DateTime.ToString("s")
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") & "')"
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.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now