• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 569
  • 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?
0
IT-Factory
Asked:
IT-Factory
5 Solutions
 
jpaulinoCommented:
try to add the date in this format instead mm/dd/yyyy
0
 
Imran Javed ZiaCommented:
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
 
jpaulinoCommented:
opps, correction MM/dd/yyyy
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Ryan McCauleyCommented:
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_ZaharinCommented:
- 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_ginCommented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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