Solved

date conversion problem

Posted on 2011-03-22
10
565 Views
Last Modified: 2012-05-11

"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?
0
Comment
Question by:IT-Factory
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 48

Expert Comment

by:jpaulino
ID: 35194446
try to add the date in this format instead mm/dd/yyyy
0
 
LVL 16

Accepted Solution

by:
Imran Javed Zia earned 100 total points
ID: 35194466
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
 
LVL 48

Assisted Solution

by:jpaulino
jpaulino earned 100 total points
ID: 35194473
opps, correction MM/dd/yyyy
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 100 total points
ID: 35194475
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
 
LVL 1

Expert Comment

by:smano
ID: 35194721
Try

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

Expert Comment

by:PagodNaUtak
ID: 35195933

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

Open in new window

0
 
LVL 23

Assisted Solution

by:OP_Zaharin
OP_Zaharin earned 100 total points
ID: 35195966
- 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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35195977
- 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
 
LVL 5

Assisted Solution

by:andr_gin
andr_gin earned 100 total points
ID: 35198344
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
 
LVL 2

Author Closing Comment

by:IT-Factory
ID: 35199739
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question