?
Solved

date conversion problem

Posted on 2011-03-22
10
Medium Priority
?
567 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 400 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 400 total points
ID: 35194473
opps, correction MM/dd/yyyy
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 400 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 400 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 400 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…

650 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